如何用SQL计算截至指定月份的累计用户注册总量

要实现截至每个月份的累计注册量统计，核心是利用窗口函数的累计求和功能，先统计单月注册量，再基于时间顺序累加。以下是具体实现方案：

解决方案代码 #

WITH monthly_reg AS (
    SELECT 
        COUNT(*) AS monthly_count,
        TO_CHAR(registration_at::DATE, 'Month') AS reg_month,
        EXTRACT(MONTH FROM registration_at) AS reg_month_num,
        EXTRACT(YEAR FROM registration_at) AS reg_year
    FROM my_table
    WHERE registration_at > DATEADD(month, -6, GETDATE())
    GROUP BY reg_year, reg_month_num, reg_month
)
SELECT 
    SUM(monthly_count) OVER(ORDER BY reg_year, reg_month_num) AS registrations,
    'until_' || LOWER(TRIM(reg_month)) AS reg_month,
    reg_month_num,
    reg_year
FROM monthly_reg
ORDER BY reg_year, reg_month_num;

代码说明 #

1. CTE子查询monthly_reg：先按年份、月份分组，统计每个月的单独注册量，逻辑和你原来的查询一致；
2. 累计求和窗口函数：SUM(monthly_count) OVER(ORDER BY reg_year, reg_month_num)会按照年份、月份的顺序，从最早的月份开始累加，得到截至当前月份的总注册量；
3. 格式调整：'until_' || LOWER(TRIM(reg_month))将月份名转换为until_november的格式，TRIM用于去除TO_CHAR('Month')返回值末尾的空格（部分数据库会自动补空格对齐）；
4. 排序：最后按年份、月份排序，确保结果的时间顺序正确。

数据库语法适配 #

如果使用的是PostgreSQL，需调整日期函数的写法：

WITH monthly_reg AS (
    SELECT 
        COUNT(*) AS monthly_count,
        TO_CHAR(registration_at::DATE, 'Month') AS reg_month,
        EXTRACT(MONTH FROM registration_at) AS reg_month_num,
        EXTRACT(YEAR FROM registration_at) AS reg_year
    FROM my_table
    WHERE registration_at > DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '6 months'
    GROUP BY reg_year, reg_month_num, reg_month
)
SELECT 
    SUM(monthly_count) OVER(ORDER BY reg_year, reg_month_num) AS registrations,
    'until_' || LOWER(TRIM(reg_month)) AS reg_month,
    reg_month_num,
    reg_year
FROM monthly_reg
ORDER BY reg_year, reg_month_num;

预期输出 #

（注：示例中16000为1000+15000的累计值，和你给出的示例数据对应）

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