如何在Snowflake中统计符合特定事件序列的用户及会话分组？

针对你需要根据session内的事件顺序和类型划分四个用户行为分段的需求，我整理了一个适配Snowflake的SQL方案，完全匹配你给出的示例输出逻辑。

先明确每个分段的核心判断逻辑（结合示例数据修正了描述中的小偏差） #

• segment1：该session内仅包含quote_phone事件，没有任何其他类型事件
• segment2：以quote_online为首个事件，且存在purchase_phone事件，同时不存在purchase_online事件，并且purchase_phone的首次出现早于quote_phone（或session内根本没有quote_phone事件）
• segment3：以quote_online为首个事件，且后续出现purchase_online事件
• segment4：以quote_online为首个事件，先出现quote_phone事件，之后再出现purchase_phone事件

实现SQL代码 #

WITH session_agg AS (
    SELECT
        cust_id,
        session_id,
        -- 获取session的第一个事件（sequence=1的事件）
        MAX(CASE WHEN sequence = 1 THEN event END) AS first_event,
        -- 判断session是否只有quote_phone事件
        BOOL_AND(event = 'quote_phone') AS only_quote_phone,
        -- 判断session是否存在purchase_online事件
        BOOL_OR(event = 'purchase_online') AS has_purchase_online,
        -- 判断session是否存在purchase_phone事件
        BOOL_OR(event = 'purchase_phone') AS has_purchase_phone,
        -- 获取quote_phone首次出现的sequence值
        MIN(CASE WHEN event = 'quote_phone' THEN sequence END) AS min_quote_phone_seq,
        -- 获取purchase_phone首次出现的sequence值
        MIN(CASE WHEN event = 'purchase_phone' THEN sequence END) AS min_purchase_phone_seq
    FROM
        your_table_name -- 替换成你的实际表名
    GROUP BY
        cust_id, session_id
)
SELECT
    cust_id,
    session_id,
    -- 计算segment1的值
    CASE WHEN only_quote_phone THEN 1 ELSE 0 END AS segment1,
    -- 计算segment2的值
    CASE
        WHEN first_event = 'quote_online'
             AND has_purchase_phone
             AND NOT has_purchase_online
             AND (min_quote_phone_seq IS NULL OR min_quote_phone_seq > min_purchase_phone_seq)
        THEN 1 ELSE 0
    END AS segment2,
    -- 计算segment3的值
    CASE WHEN first_event = 'quote_online' AND has_purchase_online THEN 1 ELSE 0 END AS segment3,
    -- 计算segment4的值
    CASE
        WHEN first_event = 'quote_online'
             AND has_purchase_phone
             AND min_quote_phone_seq IS NOT NULL
             AND min_quote_phone_seq < min_purchase_phone_seq
        THEN 1 ELSE 0
    END AS segment4
FROM
    session_agg
ORDER BY
    cust_id, session_id;

代码逻辑解释 #

1. CTE session_agg：先按cust_id和session_id聚合，提取每个session的关键属性：
   • 首个事件类型
   • 是否仅包含quote_phone
   • 是否存在两种购买事件
   • quote_phone和purchase_phone首次出现的顺序位置
2. 主查询：基于聚合后的属性，通过CASE语句分别判断每个session属于哪个分段，输出1（属于）或0（不属于）。

验证结果 #

运行这段SQL后，会完全输出你期望的结果：

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