多表用户行动重叠分析：统计不同行动完成数的用户占比

我们需要分析用户行动的重叠情况，统计两个表中特定行动的完成用户占比，具体规则和需求如下：

• table_1的actions_1列包含行动：action-a、action-b
• table_2的actions_2列包含行动：action-c、action-de、action-ed，其中action-de和action-ed需视为同一行动
• 两张表通过user_id关联
• 统计范围为至少完成一项行动的用户，需要算出完成1、2、3、4项行动的用户占比

目前用连续左连接只能查询单个行动的重叠情况，无法得到整体统计结果，也不知道如何合并action-de和action-ed进行分析。现有查询代码：

select count(distinct a.user_id)
from table_1 as a
     left join
           (select count(distinct user_id)
            from table_1
                  left join (... and so on for each action)
            where actions_1 = 'action-b'
            ) b
            on b.user_id = a.user_id
where actions_1 = 'action-a'

步骤1：统一行动名称并整合数据 #

先把两张表的行动数据合并，用CASE语句将action-de和action-ed统一为同一个行动标识，同时去重每个用户的重复行动（比如同一用户多次完成action-a只算1次）：

WITH user_actions AS (
    -- 提取table_1的行动记录
    SELECT 
        user_id,
        actions_1 AS action
    FROM table_1
    UNION
    -- 提取table_2的行动记录，合并相似行动
    SELECT 
        user_id,
        CASE 
            WHEN actions_2 IN ('action-de', 'action-ed') THEN 'action-de-ed'
            ELSE actions_2 
        END AS action
    FROM table_2
),
unique_user_actions AS (
    -- 去重，确保每个用户每个行动仅记录一次
    SELECT DISTINCT user_id, action
    FROM user_actions
)

步骤2：统计每个用户的行动完成数量 #

基于去重后的行动记录，计算每个用户完成的不同行动总数：

user_action_counts AS (
    SELECT 
        user_id,
        COUNT(action) AS action_count
    FROM unique_user_actions
    GROUP BY user_id
)

步骤3：计算各行动数量的用户占比 #

先统计符合条件的总用户数（至少完成1项行动的用户），再分别计算完成1/2/3/4项行动的用户数及占比：

SELECT 
    action_count,
    COUNT(user_id) AS user_num,
    ROUND(COUNT(user_id) * 100.0 / (SELECT COUNT(*) FROM user_action_counts), 2) AS percentage
FROM user_action_counts
GROUP BY action_count
ORDER BY action_count;

完整SQL代码 #

将以上片段整合为完整查询：

WITH user_actions AS (
    SELECT 
        user_id,
        actions_1 AS action
    FROM table_1
    UNION
    SELECT 
        user_id,
        CASE 
            WHEN actions_2 IN ('action-de', 'action-ed') THEN 'action-de-ed'
            ELSE actions_2 
        END AS action
    FROM table_2
),
unique_user_actions AS (
    SELECT DISTINCT user_id, action
    FROM user_actions
),
user_action_counts AS (
    SELECT 
        user_id,
        COUNT(action) AS action_count
    FROM unique_user_actions
    GROUP BY user_id
)
SELECT 
    action_count,
    COUNT(user_id) AS user_num,
    ROUND(COUNT(user_id) * 100.0 / (SELECT COUNT(*) FROM user_action_counts), 2) AS percentage
FROM user_action_counts
GROUP BY action_count
ORDER BY action_count;

代码说明 #

• 通过CASE语句实现了相似行动的合并，把原本的5种行动简化为4种有效行动（action-a、action-b、action-c、action-de-ed），所以用户的行动完成数量只会是1-4
• UNION+DISTINCT确保了同一用户的同一行动不会被重复统计
• 最后通过子查询获取总用户数，计算出各行动数量区间的用户占比，结果按行动数量排序展示

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