如何用SQL实现连续日期周期分组？基于TEST表的技术问询

假设你的CTE_BASE已经包含了按时间排序后的测试数据（排序是分组逻辑生效的前提），可以通过以下步骤实现符合要求的连续分组：

核心思路 #

借助窗口函数LAG()获取上一行的Period_End值，判断当前行是否满足连续规则，生成分组切换标记，最后通过累计求和生成唯一的分组ID。

完整SQL代码 #

WITH CTE_BASE AS (
    -- 此处替换为你已有的查询逻辑，务必保证结果按Period_start/Period_End排序
    SELECT Period_start, Period_End, other_columns
    FROM TEST
    ORDER BY Period_start, Period_End
),
CTE_GROUP_MARK AS (
    SELECT 
        *,
        -- 标记分组切换点：不满足连续条件时标记为1，否则为0
        CASE 
            WHEN LAG(Period_End) OVER (ORDER BY Period_start, Period_End) IS NULL THEN 1 -- 第一行作为分组起点
            WHEN Period_End = LAG(Period_End) OVER (ORDER BY Period_start, Period_End) THEN 0
            WHEN Period_start = DATEADD(DAY, 1, LAG(Period_End) OVER (ORDER BY Period_start, Period_End)) THEN 0
            ELSE 1
        END AS group_switch
    FROM CTE_BASE
)
SELECT 
    *,
    -- 累计求和生成分组ID，同一连续组的ID保持一致
    SUM(group_switch) OVER (ORDER BY Period_start, Period_End ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
FROM CTE_GROUP_MARK;

代码说明 #

• LAG()函数：获取当前行的上一行Period_End值，用于判断连续关系。
• group_switch列：标记分组的切换位置，第一行默认标记为1（作为第一个分组的起点）；当当前行满足连续规则时标记为0，不满足时标记为1。
• 累计求和生成group_id：对group_switch列做累计求和，每次遇到1时分组ID递增，从而将连续的行归为同一分组。

注意事项 #

• 必须确保CTE_BASE的结果按Period_start和Period_End排序，否则窗口函数的LAG()会获取错误的上一行数据，导致分组逻辑失效。
• 若使用非SQL Server数据库，DATEADD函数需替换为对应数据库的日期函数：比如MySQL用DATE_ADD(..., INTERVAL 1 DAY)，PostgreSQL用LAG(Period_End) + INTERVAL '1 day'。

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