Snowflake中基于Timestamp按30分钟窗口重置初始值并标记数据

现有一张包含id和timestamp字段的表，需按id、timestamp排序后实现以下逻辑：

• 每个id分组的第一行标记为1，作为窗口起点
• 后续行若与当前窗口起点的时间差在30分钟内，标记为0
• 若行的timestamp超出当前窗口起点30分钟，则将该行设为新窗口起点，标记为1

尝试过时间差计算和普通窗口函数，未找到可行方案，寻求解决方法。

表结构示例 #

id    timestamp 
1     01/01/2019 19:51:27
1     01/01/2019 19:58:12
1     01/01/2019 20:51:49
1     01/01/2019 22:58:19
1     01/02/2019 12:36:57
1     01/02/2019 12:47:55
1     01/02/2019 18:37:32
2     01/01/2019 09:55:05
2     01/01/2019 09:58:32
2     01/01/2019 10:08:16
2     01/01/2019 18:42:07
2     01/01/2019 19:01:15
2     01/01/2019 19:59:31
2     01/01/2019 23:51:59

期望输出 #

id    timestamp              value
1     01/01/2019 19:51:27    1        -- 第一个窗口起点
1     01/01/2019 19:58:12    0
1     01/01/2019 20:51:49    1        -- 超出30分钟，新窗口起点
1     01/01/2019 22:58:19    1        -- 超出上一窗口30分钟，新窗口起点
1     01/02/2019 12:36:57    1        -- 超出上一窗口30分钟，新窗口起点
1     01/02/2019 12:47:55    0
1     01/02/2019 18:37:32    1        -- 新窗口起点
2     01/01/2019 09:55:05    1        -- 新id分组的第一个窗口起点
2     01/01/2019 09:58:32    0
2     01/01/2019 10:08:16    0
2     01/01/2019 18:42:07    1        -- 超出上一窗口30分钟，新窗口起点
2     01/01/2019 19:01:15    0
2     01/01/2019 19:59:31    1        -- 超出上一窗口30分钟，新窗口起点
2     01/01/2019 23:51:59    1        -- 超出上一窗口30分钟，新窗口起点

这属于动态时间窗口分组需求，普通窗口函数无法直接实现，需通过累积标记生成动态分组ID，再基于分组标记目标值。以下是通用SQL实现：

简化版（兼容PostgreSQL/BigQuery等） #

WITH grouped_data AS (
    SELECT 
        id,
        timestamp,
        -- 累积生成分组ID：当前行与上一行时差超30分钟则分组+1
        SUM(CASE 
            WHEN LAG(timestamp) OVER (PARTITION BY id ORDER BY timestamp) IS NULL 
                 OR timestamp > LAG(timestamp) OVER (PARTITION BY id ORDER BY timestamp) + INTERVAL '30 minutes'
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY id ORDER BY timestamp) AS group_id
    FROM your_table
)
SELECT 
    id,
    timestamp,
    -- 每组第一行标记为1，其余为0
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY id, group_id ORDER BY timestamp) = 1 THEN 1 ELSE 0 END AS value
FROM grouped_data
ORDER BY id, timestamp;

数据库适配说明 #

• MySQL：将INTERVAL '30 minutes'替换为INTERVAL 30 MINUTE
• SQL Server：将判断条件改为timestamp > DATEADD(MINUTE, 30, LAG(timestamp) OVER (PARTITION BY id ORDER BY timestamp))

逻辑解释 #

1. 按id分组、timestamp排序，用LAG()获取上一行时间，判断当前行是否与上一行时差超30分钟。
2. 通过SUM() OVER()累积求和生成动态分组ID，连续30分钟内的行会被归为同一组。
3. 最后用ROW_NUMBER()标记每组的第一行为1，其余为0，得到目标结果。

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