SQL统计用户转化路径序列频次的实现方案咨询

问题分析 #

我们有一张记录用户广告行为的表，包含用户ID、时间、行为类型、广告ID，其中ad_id=-1代表转化事件。需要统计每个转化路径（转化前的广告序列，省略末尾的-1）的出现次数，用户的多次转化要分别计算对应的路径。

实现思路 #

核心是把每个转化事件和其之前的广告行为绑定为一组，再对每组的广告ID按时间排序后拼接成路径，最后统计路径的出现次数：

• 标记转化组：对每个用户的行为按时间倒序遍历，每遇到一次转化事件，就为后续（更早的）行为分配一个新的组ID，确保每个转化事件和它之前的广告行为属于同一组。
• 拼接路径：对每个组内的广告ID（排除转化的-1）按时间正序排序，拼接成规范的路径字符串。
• 统计次数：按路径分组，统计每组的出现数量。

示例SQL（PostgreSQL版本） #

假设表名为user_journey，字段为user_id, time, activity, ad_id：

WITH grouped_activities AS (
    -- 标记每个行为所属的转化组
    SELECT
        user_id,
        time,
        ad_id,
        -- 从后往前数，每遇到转化事件就递增组号
        COUNT(CASE WHEN ad_id = -1 THEN 1 END) OVER (
            PARTITION BY user_id
            ORDER BY time DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS conversion_group
    FROM user_journey
),
path_strings AS (
    -- 按组拼接广告ID序列，排除转化事件
    SELECT
        user_id,
        conversion_group,
        '(' || STRING_AGG(CAST(ad_id AS TEXT), ',' ORDER BY time) || ')' AS path
    FROM grouped_activities
    WHERE ad_id != -1
    GROUP BY user_id, conversion_group
)
-- 统计各路径的出现次数
SELECT
    path,
    COUNT(*) AS time
FROM path_strings
GROUP BY path
ORDER BY time DESC;

适配MySQL的版本 #

MySQL使用GROUP_CONCAT替代STRING_AGG，窗口函数逻辑用变量实现：

WITH grouped_activities AS (
    SELECT
        user_id,
        time,
        ad_id,
        -- 按用户倒序遍历，遇到转化则递增组号
        @group_id := CASE
            WHEN user_id != @prev_user THEN 1
            WHEN ad_id = -1 THEN @group_id + 1
            ELSE @group_id
        END AS conversion_group,
        @prev_user := user_id
    FROM user_journey
    CROSS JOIN (SELECT @group_id := 0, @prev_user := NULL) vars
    ORDER BY user_id, time DESC
),
path_strings AS (
    SELECT
        user_id,
        conversion_group,
        CONCAT('(', GROUP_CONCAT(ad_id ORDER BY time SEPARATOR ','), ')') AS path
    FROM grouped_activities
    WHERE ad_id != -1
    GROUP BY user_id, conversion_group
)
SELECT
    path,
    COUNT(*) AS time
FROM path_strings
GROUP BY path
ORDER BY time DESC;

结果验证 #

执行上述SQL后，会得到与期望一致的输出：

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