如何在Snowflake中基于符合条件的连续事件创建记录聚类

针对你需求的按用户分组、基于指定事件且时间间隔小于30秒的连续事件聚类，可以通过窗口函数结合CTE实现，具体步骤如下：

步骤说明 #

1. 筛选目标事件并计算时间间隔：先过滤出event_key属于目标数组的记录，同时为每个用户的事件按时间排序，计算当前事件与上一个符合条件事件的时间差。
2. 标记聚类边界：判断当前事件是否为新聚类的起点——如果是用户的第一个目标事件，或者与上一个事件的时间间隔≥30秒，则标记为边界。
3. 生成聚类ID：通过累计求和边界标记值，为每个连续事件组生成唯一的聚类ID。

完整SQL示例 #

WITH filtered_events AS (
    SELECT
        user_id,
        event_time,
        event_key,
        -- 按用户和时间排序，生成事件顺序
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS event_seq,
        -- 获取上一个符合条件事件的时间
        LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time,
        -- 计算与上一个事件的时间间隔（秒）
        DATEDIFF(SECOND, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time), event_time) AS time_diff
    FROM your_table_name
    -- 替换为你的目标事件数组
    WHERE event_key IN ('event_key1', 'event_key2', 'event_key3')
),
cluster_boundaries AS (
    SELECT
        *,
        -- 标记新聚类的起点：第一个事件 或 时间间隔≥30秒
        CASE
            WHEN event_seq = 1 THEN 1
            WHEN time_diff >= 30 THEN 1
            ELSE 0
        END AS is_new_cluster
    FROM filtered_events
)
SELECT
    user_id,
    event_time,
    event_key,
    -- 累计求和生成聚类ID，同一聚类的ID相同
    SUM(is_new_cluster) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cluster_id
FROM cluster_boundaries
ORDER BY user_id, event_time;

关键细节说明 #

• 过滤目标事件：通过WHERE event_key IN (...)筛选出属于指定数组的事件，确保只处理符合要求的事件。
• 时间间隔计算：使用LAG()窗口函数获取上一个事件的时间，结合DATEDIFF()计算时间差（单位为秒）。
• 聚类ID生成：利用SUM() OVER()的累计求和特性，每当遇到新聚类起点（标记为1）时，聚类ID递增，从而将连续符合条件的事件归为同一组。

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