按连续row_num汇总value并分配最大row_num值的SQL实现需求

表结构与数据 #

先看创建表和插入数据的SQL语句：

CREATE TABLE TABLE_ONE ( 
   person varchar(5), 
   COL_1 varchar(20), 
   COL_2 varchar(20), 
   value int, 
   row_num int
);
 
INSERT INTO table_one VALUES 
('101','A','ABC',10,1),
('101','A','ABC',20,2),
('101','A','ABC',12,6),
('101','A','ABC',10,8),
('101','A','ABC',20,9),
('101','A','ABC',15,10),
('101','A','ABC',10,12),
('101','B','ABC',1,1),
('101','B','ABC',4,2),
('101','B','ABC',2,3),
('101','B','ABC',1,10),
('101','B','ABC',3,11),
('101','B','ABC',4,15),
('101','B','ABC',4,16);

需求说明 #

要按照连续的row_num对value字段做汇总：

• 只要多行的row_num是连续数值，就把这些行的value求和
• 每个连续组最终保留组内最大的row_num值

举个实际例子：row_num=1和row_num=2是连续值，对应value分别为10和20，求和后得30，这条汇总记录的row_num就取2。

解决方案 #

用窗口函数生成连续分组的标识，再做分组聚合就能实现需求，SQL语句如下：

SELECT
    person,
    COL_1,
    COL_2,
    SUM(value) AS total_value,
    MAX(row_num) AS group_max_row_num
FROM (
    SELECT
        *,
        -- 生成连续分组标识：连续的row_num会得到相同的group_id
        row_num - ROW_NUMBER() OVER (PARTITION BY person, COL_1, COL_2 ORDER BY row_num) AS group_id
    FROM TABLE_ONE
) t
GROUP BY person, COL_1, COL_2, group_id
ORDER BY person, COL_1, COL_2, group_max_row_num;

逻辑拆解 #

1. 内层查询里，PARTITION BY person, COL_1, COL_2保证我们在同一个person、COL_1、COL_2的范围内找连续row_num；按row_num排序后，用row_num - ROW_NUMBER()计算分组标识——连续的row_num对应的这个差值固定，非连续行的差值会变化，以此区分不同的连续组。
2. 外层查询按分组标识、person、COL_1、COL_2分组，求和value得到总数值，同时取组内最大的row_num作为该组的标识row_num。

执行结果 #

运行上述SQL后，会得到如下结果：

内容的提问来源于stack exchange，提问作者Rohan Bali