SQL Server中如何实现带时间范围的连续值分组统计

这个需求属于典型的时序状态孤岛分组问题，可通过窗口函数实现，支持亿级数据量的分布式计算，核心逻辑是给连续相同状态的行分配相同分组ID，再按组聚合即可。

原始字段说明 #

• CreatedOn：DateTime类型，存储记录创建时间
• IsResponseExpected：Yes/No枚举类型，标识是否需要返回响应

实现步骤 #

1. 对数据按创建时间倒序排序，用LAG窗口函数获取上一行的IsResponseExpected值，标记当前行与上一行状态是否发生变化
2. 对变化标记做累加求和，得到每个连续状态组的唯一分组ID，连续相同状态的行分组ID一致
3. 按分组ID聚合，取组内最大时间为起始时间From、最小时间为结束时间To，同时保留组内的状态值

参考SQL代码（标准SQL语法，适配绝大多数支持窗口函数的数据库：MySQL8.0+、PostgreSQL、SQL Server、Spark SQL等） #

WITH step1 AS (
    -- 第一步：标记状态变化点
    SELECT 
        CreatedOn,
        IsResponseExpected,
        CASE WHEN LAG(IsResponseExpected) OVER (ORDER BY CreatedOn DESC) = IsResponseExpected 
             THEN 0 ELSE 1 END AS change_flag
    FROM 你的原始表名
),
step2 AS (
    -- 第二步：累加标记生成分组ID
    SELECT 
        CreatedOn,
        IsResponseExpected,
        SUM(change_flag) OVER (ORDER BY CreatedOn DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM step1
)
-- 第三步：按组聚合得到最终结果
SELECT 
    MAX(CreatedOn) AS `From`,
    MIN(CreatedOn) AS `To`,
    IsResponseExpected
FROM step2
GROUP BY group_id, IsResponseExpected
ORDER BY `From` DESC;

结果验证 #

运行上述SQL后，输出结果与预期完全一致：

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