如何在SQL中计算重复用户数？附示例数据与预期结果

要实现你需要的重复用户统计逻辑（12月统计当月唯一用户数，后续月份统计当月曾在前一个月出现过的用户数），可以通过以下步骤实现：

1. 提取唯一用户-月份组合：先去除同一用户同一月的重复会话记录，得到每个用户每个月的唯一记录。
2. 给月份排序：将字符串格式的月份转换为日期，生成月份的序列，方便关联前一个月的数据。
3. 判断用户是否在前一个月存在：对每个用户的每个月份，检查该用户是否在前一个月有记录，标记为重复用户。
4. 分组统计结果：第一个月份直接统计唯一用户数，后续月份统计标记为重复的用户数量。

完整的SQL代码如下：

WITH fact_table (employee_wid, month, conv_id) AS (
    SELECT 100024, 'Dec 2022', 'Conv 1' FROM dual UNION ALL
    SELECT 100234, 'Dec 2022', 'Conv 1' FROM dual UNION ALL
    SELECT 100236, 'Dec 2022', 'Conv 1' FROM dual UNION ALL
    SELECT 100024, 'Dec 2022', 'Conv 2' FROM dual UNION ALL
    SELECT 100234,'Dec 2022', 'Conv 2' FROM dual UNION ALL
    SELECT 100342,'Jan 2023', 'Conv 1' FROM dual UNION ALL
    SELECT 100346, 'Jan 2023', 'Conv 1' FROM dual UNION ALL
    SELECT 100024, 'Jan 2023', 'Conv 1' FROM dual UNION ALL
    SELECT 100024, 'Jan 2023', 'Conv 2' FROM dual UNION ALL
    SELECT 100234, 'Jan 2023', 'Conv 1' FROM dual UNION ALL
    SELECT 100236, 'Jan 2023', 'Conv 1' FROM dual UNION ALL
    SELECT 100236, 'Jan 2023', 'Conv 2' FROM dual UNION ALL
    SELECT 100346,'Feb 2023', 'Conv 1' FROM dual UNION ALL
    SELECT 100346,'Feb 2023', 'Conv 2' FROM dual UNION ALL
    SELECT 100234, 'Feb 2023', 'Conv 1' FROM dual UNION ALL
    SELECT 100113,'Feb 2023', 'Conv 1' FROM dual UNION ALL
    SELECT 100148,'Feb 2023', 'Conv 1' FROM dual
),
-- 获取用户-月份的唯一组合
unique_user_month AS (
    SELECT DISTINCT employee_wid, month
    FROM fact_table
),
-- 给每个月份生成排序序号
month_order AS (
    SELECT 
        month,
        TO_DATE(month, 'Mon YYYY') AS month_date,
        ROW_NUMBER() OVER (ORDER BY TO_DATE(month, 'Mon YYYY')) AS month_seq
    FROM (SELECT DISTINCT month FROM unique_user_month)
),
-- 关联用户-月份和月份序号
user_month_seq AS (
    SELECT u.employee_wid, u.month, m.month_seq
    FROM unique_user_month u
    JOIN month_order m ON u.month = m.month
),
-- 标记用户是否在前一个月存在
user_prev_month_check AS (
    SELECT 
        um.employee_wid, 
        um.month,
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM user_month_seq um_prev
                WHERE um_prev.employee_wid = um.employee_wid
                  AND um_prev.month_seq = um.month_seq - 1
            ) THEN 1 
            ELSE 0 
        END AS is_repeated
    FROM user_month_seq um
)
-- 分组统计结果
SELECT 
    month,
    CASE 
        WHEN m.month_seq = 1 THEN COUNT(DISTINCT umc.employee_wid)
        ELSE SUM(umc.is_repeated)
    END AS repeated_user_count
FROM user_prev_month_check umc
JOIN month_order m ON umc.month = m.month
GROUP BY month, m.month_seq
ORDER BY m.month_seq;

运行结果 #

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

MONTH     REPEATED_USER_COUNT
--------- -------------------
Dec 2022                    3
Jan 2023                    3
Feb 2023                    2

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