按插入顺序分组行：同SHIFTREF非连续组的SQL分组实现

需求说明 #

现有包含RECORDREF、SHIFTREF、DATECREATED字段的表格数据，需要按SHIFTREF分组，但被其他SHIFTREF值行隔开的同值组需视为不同分组，且不能将DATECREATED用于GROUP BY子句，最终得到指定的汇总结果。

原始数据 #

期望结果 #

解决方案 #

核心思路是通过窗口函数生成连续分组标识，利用RECORDREF的递增特性判断行的先后顺序，将连续相同的SHIFTREF归为同一组，被隔开的同值则生成新组，最后基于分组标识汇总。

完整SQL语句 #

SELECT 
    SHIFTREF,
    COUNT(RECORDREF) AS COUNT,
    MIN(DATECREATED) AS DATE
FROM (
    SELECT 
        RECORDREF,
        SHIFTREF,
        DATECREATED,
        -- 生成连续分组ID：当前行与上一行SHIFTREF不同时，累加1
        SUM(CASE WHEN SHIFTREF = LAG(SHIFTREF) OVER (ORDER BY RECORDREF) THEN 0 ELSE 1 END) 
        OVER (ORDER BY RECORDREF) AS group_id
    FROM your_table
) AS grouped_data
-- 按SHIFTREF和分组ID分组，确保同SHIFTREF但不连续的组被区分
GROUP BY SHIFTREF, group_id
ORDER BY group_id;

语句解释 #

1. 子查询生成分组ID：

   • 使用LAG(SHIFTREF) OVER (ORDER BY RECORDREF)获取当前行的上一行SHIFTREF值；
   • 通过CASE语句判断当前行与上一行SHIFTREF是否相同，不同则标记为1，相同则为0；
   • 用SUM()窗口函数累加标记值，生成唯一的group_id，连续相同的SHIFTREF会得到同一个group_id。

2. 外层汇总查询：

   • 按SHIFTREF和group_id分组，确保被隔开的同SHIFTREF组被视为不同分组；
   • COUNT(RECORDREF)统计每组的记录数；
   • MIN(DATECREATED)取该组的最早日期（符合期望结果的日期展示需求）；
   • 最后按group_id排序，保证结果顺序与原始数据的分组顺序一致。

注意事项 #

• 必须确保RECORDREF是按数据插入顺序递增的，否则ORDER BY RECORDREF无法正确反映行的先后顺序，会导致分组错误。

内容的提问来源于stack exchange，提问作者Fatih Büyükegen