SQL按时间顺序对同生产线连续同色记录分组并分配组ID咨询

适用场景 #

该需求属于SQL经典的「间隙与岛屿」问题，可通过标准窗口函数实现，兼容MySQL8.0+、PostgreSQL、SQL Server、Hive、SparkSQL等所有支持窗口函数的主流数据库。

实现代码 #

WITH step1 AS (
    -- 标记同生产线内相邻行的颜色变化
    SELECT 
        Time,
        Line,
        Colour,
        CASE 
            WHEN LAG(Colour) OVER (PARTITION BY Line ORDER BY Time) != Colour THEN 1 
            ELSE 0 
        END AS change_flag
    FROM 你的表名
),
step2 AS (
    -- 生成每条生产线的连续组ID
    SELECT 
        *,
        SUM(change_flag) OVER (PARTITION BY Line ORDER BY Time) + 1 AS `Group ID`
    FROM step1
)
-- 计算同组总数量并输出最终结果
SELECT 
    Time,
    Line,
    Colour,
    COUNT(*) OVER (PARTITION BY Line, `Group ID`) AS Qty,
    `Group ID`
FROM step2
ORDER BY Line, Time;

逻辑说明 #

• 第一层CTE用LAG窗口函数取同生产线内按时间排序的上一行颜色，和当前行对比，颜色发生变化则标记为1，否则为0
• 第二层CTE对标记值做滚动累加，每次颜色变化累加值就+1，自然生成每个生产线内从1开始的连续组ID
• 最后用COUNT窗口函数统计每个生产线+组ID下的总记录数，即为同组总数量Qty

注意事项 #

如果存在多条记录时间完全相同的情况，可在窗口函数的ORDER BY子句后追加自增主键等唯一字段，保证排序结果稳定，避免同时间记录顺序错乱导致分组异常。

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