基于关联表实现截至指定日期用户数量统计的SQL问询

你的原始SQL只是统计了每个月的记录数量（同一个用户可能在rat_proj_duration_map中有多条记录，所以count(r.uid)会重复计数）。要实现截至每个月的累计唯一用户数，我们需要先处理用户的唯一性，再基于用户首次出现的月份做累计统计。下面分两种常见需求给出解决方案：

需求1：统计截至当月，所有曾在2018年出现过的唯一用户数（累计） #

这个需求不区分用户当前状态，只要用户在2018年的某个月出现在rat_proj_duration_map中，就从首次出现的月份开始计入累计数。

WITH user_first_appearance AS (
    -- 先获取每个用户在2018年首次出现在记录表中的月份
    SELECT 
        r.uid,
        MIN(r.month_val) AS first_month
    FROM rat_proj_duration_map r
    INNER JOIN users u ON r.uid = u.uid
    WHERE r.year_val = 2018
    GROUP BY r.uid
),
-- 生成2018年的所有月份列表
all_months AS (
    SELECT 1 AS month_val UNION ALL
    SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
    SELECT 11 UNION ALL SELECT 12
)
-- 统计每个月的累计用户数
SELECT 
    am.month_val,
    COUNT(u.uid) AS cumulative_user_count
FROM all_months am
LEFT JOIN user_first_appearance u ON u.first_month <= am.month_val
GROUP BY am.month_val
ORDER BY am.month_val;

代码解释： #

1. user_first_appearance CTE：通过MIN(r.month_val)找出每个用户在2018年第一次出现在记录表中的月份，同时关联users表确保用户存在。
2. all_months CTE：生成2018年1-12月的完整月份列表，避免因为某些月份没有用户数据而缺失记录。
3. 最后通过左关联，统计所有首次出现月份<=当前月份的用户数量，得到截至当月的累计用户数。

需求2：统计截至当月，仍处于活跃状态的累计用户数 #

如果需要只统计截至当月仍活跃的用户（即status='Act'，且未离职或离职日期晚于当月），可以调整过滤条件：

WITH active_user_first_month AS (
    SELECT 
        r.uid,
        MIN(r.month_val) AS first_active_month
    FROM rat_proj_duration_map r
    INNER JOIN users u ON r.uid = u.uid
    WHERE r.year_val = 2018
        AND u.status = 'Act'
        -- 确保用户在该月仍未离职：离职日期为空 或 离职日期晚于当月最后一天
        AND (u.left_on IS NULL OR u.left_on >= LAST_DAY(CONCAT(r.year_val, '-', r.month_val, '-01')))
    GROUP BY r.uid
),
all_months AS (
    SELECT 1 AS month_val UNION ALL
    SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
    SELECT 11 UNION ALL SELECT 12
)
SELECT 
    am.month_val,
    COUNT(u.uid) AS cumulative_active_users
FROM all_months am
LEFT JOIN active_user_first_month u ON u.first_active_month <= am.month_val
GROUP BY am.month_val
ORDER BY am.month_val;

关键调整点： #

在active_user_first_month中加入了用户状态和离职日期的判断，确保统计的是符合活跃条件的用户，再基于他们首次活跃的月份计算累计数。

另一种更简洁的窗口函数实现 #

如果你更习惯用窗口函数，可以先统计每个月的新增用户数，再通过累计求和得到结果：

WITH monthly_new_users AS (
    -- 统计每个月的新增用户（首次出现的用户）
    SELECT 
        MIN(r.month_val) AS month_val,
        COUNT(DISTINCT r.uid) AS new_users
    FROM rat_proj_duration_map r
    INNER JOIN users u ON r.uid = u.uid
    WHERE r.year_val = 2018
    -- 可选：加入活跃用户过滤条件
    -- AND u.status = 'Act' AND (u.left_on IS NULL OR u.left_on >= LAST_DAY(CONCAT(r.year_val, '-', MIN(r.month_val), '-01')))
    GROUP BY r.uid
),
all_months AS (
    SELECT 1 AS month_val UNION ALL
    SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
    SELECT 11 UNION ALL SELECT 12
)
SELECT 
    am.month_val,
    -- 用窗口函数累计求和
    SUM(COALESCE(mnu.new_users, 0)) OVER (ORDER BY am.month_val) AS cumulative_users
FROM all_months am
LEFT JOIN monthly_new_users mnu ON am.month_val = mnu.month_val
ORDER BY am.month_val;

这里用SUM() OVER (ORDER BY am.month_val)实现了累计求和，COALESCE用来处理没有新增用户的月份（补0）。

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