如何在Snowflake中捕获同一状态的两个连续时间周期？

现有包含ID、Status、Date三列的数据集，其中ID为001的记录存在两段连续Status=T的日期区间（中间被Status=R分隔），需要生成包含ID、Status、Start_date、End_date的结果表，提取这两段T状态的起止日期。

直接用窗口函数分组连续相同状态的记录，再聚合提取起止日期即可：

步骤1：标记连续状态分组 #

先通过窗口函数给每一段连续相同状态的记录分配唯一分组ID：

WITH grouped_data AS (
    SELECT 
        ID,
        Status,
        Date,
        -- 若当前行状态与前一行不同，分组ID加1，以此标记连续状态段
        SUM(CASE WHEN LAG(Status) OVER (PARTITION BY ID ORDER BY Date) != Status THEN 1 ELSE 0 END) 
            OVER (PARTITION BY ID ORDER BY Date) AS status_group
    FROM your_table_name -- 替换为你的实际表名
)

步骤2：聚合提取起止日期 #

基于分组结果，过滤出T状态的记录，再按分组聚合得到每段的起止日期：

SELECT 
    ID,
    Status,
    MIN(Date) AS Start_date,
    MAX(Date) AS End_date
FROM grouped_data
WHERE Status = 'T'
GROUP BY ID, Status, status_group
ORDER BY ID, Start_date;

逻辑说明 #

• LAG(Status) OVER (PARTITION BY ID ORDER BY Date)：针对每个ID，按日期排序后获取前一行的Status，用来判断当前行是否和前一行状态连续。
• SUM(...) OVER (...)：累加状态变化的次数，给每一段连续相同状态的记录分配同一个分组ID。
• 最后通过分组聚合，取每组的最小日期作为起始、最大日期作为结束，只保留T状态的结果。

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