SQL Server如何按id分组按日期排序后统计同色连续出现次数

我使用SQL创建了按相同id分组、按日期排序的rank列，想要统计蓝色、红色、黄色值按顺序的连续重复次数。

原始数据示例 #

这是SQL中典型的连续序列（岛屿）统计问题，直接使用窗口函数即可实现需求：

WITH color_group AS (
    SELECT 
        *,
        -- 计算连续同色块的分组标识
        [rank] - ROW_NUMBER() OVER(PARTITION BY id, color ORDER BY [rank]) AS grp
    FROM your_table -- 替换为你自己的实际表名
)
SELECT 
    date,
    id,
    color,
    [rank],
    -- 同连续块内的行号即为连续重复次数
    ROW_NUMBER() OVER(PARTITION BY id, color, grp ORDER BY [rank]) AS repetitions
FROM color_group
ORDER BY id, [rank]

逻辑说明 #

• 第一层CTE中，按id和color分组计算每条记录的内部行号，用原有全局rank减去这个行号，得到的值对于连续出现的同颜色记录是相同的，以此作为连续同色块的唯一标识
• 第二层查询中，按id、color和上面得到的分组标识grp再次分组，组内按rank排序生成的行号，就是对应颜色的连续重复次数

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