Snowflake SQL如何实现按时间排序去重的累计字符串聚合

核心实现逻辑 #

要满足同id+session_id分组内、按时间累计、去重、有序拼接事件名的需求，直接使用Snowflake原生窗口版LISTAGG函数即可，无需写复杂的UDF或者多层子查询，核心逻辑点：

• 用PARTITION BY id, session_id限定计算窗口为单个用户的单个会话
• 窗口帧设置为从分区起点到当前行，实现「截至当前行事件时间」的累计范围
• 给LISTAGG加DISTINCT关键字自动剔除重复事件，通过WITHIN GROUP子句指定事件按发生时间升序排列

可直接运行的代码示例 #

-- 先构造测试样例数据（实际使用时替换成自己的业务表即可）
WITH event_data AS (
    SELECT
        5496 AS id,
        4621 AS session_id,
        'start' AS event_custom_name,
        '2024-01-01 10:00:00'::TIMESTAMP AS event_date
    UNION ALL
    SELECT 5496, 4621, 'SelectBank', '2024-01-01 10:00:05'::TIMESTAMP
    UNION ALL
    SELECT 5496, 4621, 'login', '2024-01-01 10:00:10'::TIMESTAMP
    UNION ALL
    SELECT 5496, 4621, 'end', '2024-01-01 10:00:15'::TIMESTAMP
    -- 额外加一条重复事件验证去重逻辑
    UNION ALL
    SELECT 5496, 4621, 'SelectBank', '2024-01-01 10:00:20'::TIMESTAMP
)
-- 核心计算逻辑
SELECT
    id,
    session_id,
    event_custom_name,
    event_date,
    LISTAGG(DISTINCT event_custom_name, ',') 
        WITHIN GROUP (ORDER BY event_date ASC)
        OVER (
            PARTITION BY id, session_id
            ORDER BY event_date ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS event_list
FROM event_data
ORDER BY id, session_id, event_date ASC;

结果验证 #

上述代码执行后输出结果完全匹配需求：

最后一条重复的SelectBank事件不会重复出现在拼接结果中，符合去重要求。

注意事项 #

• 如果同一时间点会产生多个不同事件，Snowflake默认会按事件名字典序排列同时间点的事件，如果需要自定义优先级，可以在WITHIN GROUP的ORDER BY里加第二排序字段（比如事件自增ID、事件类型优先级字段）
• 单会话拼接的event_list总长度不能超过Snowflake VARCHAR类型最大长度（默认16MB），如果存在超长会话场景，可以按需调整字段长度或者加截断逻辑

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