【详述】子查询用窗口函数计算时，外层查询group by无法聚合成一条
【StarRocks版本】2.3.10及2.5.3均存在
【集群规模】1fe+4be
建表语句（主键模型表也是一样）：
CREATE TABLE testtable (
history_type tinyint(4) NOT NULL “,
history_id int(11) NOT NULL “,
history_time datetime NOT NULL “,
history_category varchar(120) NOT NULL”,
created_by varchar(10) NOT NULL”,
created_time datetime NOT NULL”
) ENGINE=OLAP
DUPLICATE KEY(history_type, history_id, history_time, history_category)
DISTRIBUTED BY HASH(history_type) BUCKETS 10
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “true”
);
测试数据：
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘7’, ‘162’, ‘2018-12-31 00:00:00’, ‘aaaa’,‘yliu68’, ‘2023-02-07 18:44:14’);
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘7’, ‘162’, ‘2019-12-31 00:00:00’, ‘bbbb’, ‘yliu68’, ‘2023-02-07 18:44:14’);
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘7’, ‘162’, ‘2020-12-31 00:00:00’, ‘cccc’, ‘yliu68’, ‘2023-02-07 18:44:14’);
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘7’, ‘162’, ‘2021-12-31 00:00:00’, ‘dddd’, ‘yliu68’, ‘2023-02-07 18:44:14’);
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘7’, ‘162’, ‘2022-12-31 00:00:00’, ‘eeee’, ‘yliu68’, ‘2023-02-07 18:44:14’);
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘7’, ‘162’, ‘2022-12-31 00:00:00’, ‘ffff’, ‘yliu68’, ‘2023-02-23 15:29:47’);
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘10’, ‘162’, ‘2023-12-31 00:00:00’, ‘gggg’, ‘hwu16’, ‘2023-02-20 09:53:11’);
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘10’, ‘162’, ‘2023-12-31 00:00:00’, ‘hhhh’, ‘hwan2’, ‘2023-02-20 16:26:06’);
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘10’, ‘162’, ‘2023-12-31 00:00:00’, ‘iiii’, ‘yma4’, ‘2023-02-20 18:11:19’);
INSERT INTO testtable (history_type, history_id, history_time, history_category, created_by, created_time) VALUES (‘10’, ‘162’, ‘2023-12-31 00:00:00’, ‘jjjj’, ‘yma4’, ‘2023-02-20 18:16:31’);
复现语句：
SELECT
history_id AS supplier_id
FROM
(
SELECT
history_id,
history_time,
rank () over (
PARTITION BY history_id,
history_time
ORDER BY
created_time DESC
) AS rk
FROM
testtable
WHERE
history_type = 7
AND history_id = 162
) temp
WHERE
temp.rk = 1
GROUP BY
history_id
MySQL [pms]> SELECT history_id AS supplier_id FROM (SELECT history_id,history_time,rank () over (PARTITION BY history_id,history_time ORDER BY created_time DESC) AS rk FROM testtable WHERE history_type = 7 AND history_id = 162) temp WHERE temp.rk = 1 GROUP BY history_id;
±------------+
| supplier_id |
±------------+
| 162 |
| 162 |
| 162 |
| 162 |
±------------+
4 rows in set (0.01 sec)
如果把temp.rk = 1条件去掉，则可以得到一条记录。
MySQL [pms]> SELECT history_id AS supplier_id FROM (SELECT history_id,history_time,rank () over (PARTITION BY history_id,history_time ORDER BY created_time DESC) AS rk FROM testtable WHERE history_type = 7 AND history_id = 162) temp GROUP BY history_id;
±------------+
| supplier_id |
±------------+
| 162 |
±------------+
1 row in set (0.01 sec)