如何基于条件在SQL中生成用户旅程列表

核心思路 #

要区分同一用户的独立访问旅程，关键是给每个旅程分配唯一的分组ID。观察数据规律：每个旅程要么以purchase=1结束，要么是未完成转化的连续访问序列。我们可以通过累计转化次数来标记旅程分组：

• 对每个用户（clientid），按访问顺序（visit_order）排序
• 计算从第一条记录到当前记录的purchase累计和，这个累计值就是该记录所属的旅程ID

每次出现转化（purchase=1）时，后续的访问会被分到新的旅程组里，自然实现了独立旅程的划分。

完整SQL代码 #

WITH journey_groups AS (
    SELECT 
        clientid,
        channel,
        purchase,
        visit_order,
        -- 生成旅程分组ID：累计当前及之前的转化次数
        SUM(purchase) OVER (
            PARTITION BY clientid 
            ORDER BY visit_order 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS journey_id
    FROM table_name
)
SELECT 
    clientid,
    -- 按访问顺序拼接渠道，确保旅程顺序正确
    CONCAT_WS(',', COLLECT_LIST(channel) ORDER BY visit_order) AS user_journey,
    -- 每个旅程的转化状态：取该组的最大purchase值（0或1）
    MAX(purchase) AS conversion
FROM journey_groups
GROUP BY clientid, journey_id
ORDER BY clientid, journey_id;

代码说明 #

1. CTE部分（journey_groups）：
   • 使用窗口函数SUM(purchase) OVER (...)为每条记录生成journey_id。例如用户123的三条记录journey_id依次为0、0、1；用户456前三条记录journey_id为0、0、1，后两条为1、1（因前面已有一次转化，累计和保持1）。
2. 主查询部分：
   • 按clientid和journey_id分组，确保每个独立旅程被单独统计。
   • COLLECT_LIST(channel) ORDER BY visit_order保证渠道拼接顺序与实际访问顺序一致，避免乱序。
   • MAX(purchase)直接获取该旅程的转化状态（每个旅程最多仅一次转化，或全为0）。

验证结果 #

执行上述代码后，会得到期望的输出：

内容的提问来源于stack exchange，提问作者Wagner André Yamada Vieira