Snowflake/dbt中时间序列表筛选不同连续条目的实现问题

核心思路 #

你要实现的是连续相同action分组取最新记录，属于典型的「间隙与孤岛（Gaps and Islands）」场景，核心逻辑是给连续相同的action打上相同的分组标签，再从每个分组里筛选最新的一条记录即可。

实现代码 #

方案1：GROUP BY分组取最值 #

WITH step1 AS (
    -- 第一步：对比当前行和上一行的action，标记是否发生变化
    SELECT 
        *,
        LAG(action) OVER (PARTITION BY user_id, session_id ORDER BY timestamp) AS prev_action
    FROM your_table_name
),
step2 AS (
    -- 第二步：累加变化标记，生成连续相同action的分组ID
    SELECT 
        *,
        SUM(CASE WHEN action != prev_action OR prev_action IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY user_id, session_id ORDER BY timestamp) AS action_group
    FROM step1
)
-- 第三步：每个连续action分组里，取时间最大的最后一条记录
SELECT user_id, session_id, action, MAX(timestamp) AS timestamp
FROM step2
GROUP BY user_id, session_id, action, action_group
ORDER BY timestamp

方案2：QUALIFY语法简化实现 #

WITH step1 AS (
    SELECT 
        *,
        LAG(action) OVER (PARTITION BY user_id, session_id ORDER BY timestamp) AS prev_action
    FROM your_table_name
),
step2 AS (
    SELECT 
        *,
        SUM(CASE WHEN action != prev_action OR prev_action IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY user_id, session_id ORDER BY timestamp) AS action_group,
        ROW_NUMBER() OVER (PARTITION BY user_id, session_id, action_group ORDER BY timestamp DESC) AS rn
    FROM step1
)
SELECT user_id, session_id, action, timestamp
FROM step2
QUALIFY rn = 1
ORDER BY timestamp

逻辑说明 #

• 分区维度PARTITION BY user_id, session_id默认按用户+会话划分独立的行为序列，可根据业务需求自行调整分区规则
• 同一时间戳下的不同action（比如示例里的scroll和saved都是12:00:10）会被判定为不同分组，各自保留，完全匹配预期输出
• 若时间戳存在重复值，可在排序规则后加次级排序字段（比如自增ID），避免非确定排序问题

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