如何用单条SQL语句计算12个月滚动周期内各月唯一用户数

核心思路 #

生成所有需要统计的报告月序列，关联联系记录表后，通过日期范围筛选每个报告月对应的前11个月第一天至当月最后一天的记录，最终统计该范围内的唯一UserID数量。

不同SQL方言实现示例 #

MySQL

WITH report_months AS (
    -- 自动从联系人表提取所有存在记录的月份作为报告月
    SELECT DISTINCT DATE_FORMAT(ContactDate, '%Y%m') AS report_month
    FROM contacts
    -- 若需手动指定报告月范围，替换上方为枚举：
    -- SELECT '202210' AS report_month UNION ALL
    -- SELECT '202211' UNION ALL ...
)
SELECT
    rm.report_month,
    COUNT(DISTINCT c.UserID) AS unique_user_count
FROM report_months rm
LEFT JOIN contacts c
    -- 匹配报告月对应的12个月滚动周期
    ON c.ContactDate >= STR_TO_DATE(CONCAT(rm.report_month, '01'), '%Y%m%d') - INTERVAL 11 MONTH
    AND c.ContactDate <= LAST_DAY(STR_TO_DATE(CONCAT(rm.report_month, '01'), '%Y%m%d'))
GROUP BY rm.report_month
ORDER BY rm.report_month;

PostgreSQL

WITH report_months AS (
    SELECT DISTINCT TO_CHAR(ContactDate, 'YYYYMM') AS report_month
    FROM contacts
)
SELECT
    rm.report_month,
    COUNT(DISTINCT c.UserID) AS unique_user_count
FROM report_months rm
LEFT JOIN contacts c
    ON c.ContactDate >= (TO_DATE(rm.report_month || '01', 'YYYYMMDD') - INTERVAL '11 months')
    AND c.ContactDate <= (TO_DATE(rm.report_month || '01', 'YYYYMMDD') + INTERVAL '1 month - 1 day')
GROUP BY rm.report_month
ORDER BY rm.report_month;

SQL Server

WITH report_months AS (
    SELECT DISTINCT FORMAT(ContactDate, 'yyyyMM') AS report_month
    FROM contacts
)
SELECT
    rm.report_month,
    COUNT(DISTINCT c.UserID) AS unique_user_count
FROM report_months rm
LEFT JOIN contacts c
    ON c.ContactDate >= DATEADD(MONTH, -11, CAST(rm.report_month + '01' AS DATE))
    AND c.ContactDate <= EOMONTH(CAST(rm.report_month + '01' AS DATE))
GROUP BY rm.report_month
ORDER BY rm.report_month;

关键注意事项 #

• 性能优化：若联系人表数据量较大，建议为ContactDate和UserID创建联合索引，加速范围查询与去重统计。
• 空值处理：LEFT JOIN确保无用户记录的报告月仍返回0；若仅需保留有记录的报告月，替换为INNER JOIN即可。
• 日期格式校验：确保ContactDate为日期类型；若存储为字符串，需先通过转换函数转为日期后再进行范围比较。

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