如何按连续状态窗口分组SQL数据？现有代码结果不符需求

嘿，这个问题属于经典的连续状态分组（岛屿问题），你的原始查询只是按id和[Status]做全局分区，没有区分同一id下相同Status被其他状态打断后的不同连续段，所以才会把所有同状态的行都合并成一个分组。咱们来修改代码实现需求：

核心思路 #

要实现连续状态的分组，关键是给每个连续的相同状态段生成一个唯一的分组ID，步骤如下：

• 按id和Date排序，用LAG()函数获取当前行的上一行Status
• 判断当前行和上一行的Status是否一致，生成状态变化标记
• 对标记做累加，得到每个连续状态段的分组ID
• 最后按id、[Status]和分组ID聚合，计算每个段的起始、结束日期和总数

修改后的完整SQL代码 #

declare @test table (id int, [Status] int, [Date] date)
insert into @test (Id,[Status],[Date]) VALUES 
(1,1,'2018-01-01'), (2,1,'2018-01-01'), (1,1,'2017-11-01'), 
(1,2,'2017-10-01'), (1,1,'2017-09-01'), (2,2,'2017-01-01'), 
(1,1,'2017-08-01'), (1,1,'2017-07-01'), (1,1,'2017-06-01'), 
(1,2,'2017-05-01'), (1,1,'2017-04-01'), (1,1,'2017-03-01'), 
(1,1,'2017-01-01')

-- 先给每个连续状态段生成分组ID，再聚合
WITH CTE AS (
    SELECT 
        id, 
        [Status], 
        [Date],
        -- 标记状态是否变化：当前行Status和上一行不同则为1，否则为0
        CASE WHEN LAG([Status]) OVER (PARTITION BY id ORDER BY [Date]) != [Status] THEN 1 ELSE 0 END AS StatusChange
    FROM @test
),
CTE_Grouped AS (
    SELECT 
        id, 
        [Status], 
        [Date],
        -- 累加状态变化标记，得到每个连续段的分组ID
        SUM(StatusChange) OVER (PARTITION BY id ORDER BY [Date]) AS GroupId
    FROM CTE
)
SELECT 
    id, 
    [Status], 
    MIN([Date]) AS WindowStart, 
    MAX([Date]) AS WindowEnd, 
    COUNT(*) AS total
FROM CTE_Grouped
GROUP BY id, [Status], GroupId
ORDER BY id, WindowStart;

代码解释 #

1. CTE部分：用LAG([Status]) OVER (PARTITION BY id ORDER BY [Date])获取当前id下按日期排序的上一行状态，通过CASE判断是否发生状态变化，生成StatusChange标记。
2. CTE_Grouped部分：对StatusChange做累加（SUM() OVER()），这样每个连续的相同状态段会得到同一个GroupId，不同的连续段GroupId不同。
3. 最终聚合：按id、[Status]和GroupId分组，计算每个段的最小日期（起始）、最大日期（结束）和行数（总数）。

执行结果 #

执行后会得到你需要的连续状态分组结果：

id Status WindowStart WindowEnd total
1  1      2017-01-01  2017-04-01 3
1  2      2017-05-01  2017-05-01 1
1  1      2017-06-01  2017-09-01 4
1  2      2017-10-01  2017-10-01 1
1  1      2017-11-01  2018-01-01 2
2  1      2018-01-01  2018-01-01 1
2  2      2017-01-01  2017-01-01 1

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