如何用SQL统计截至各日期的累计出生人数？

嘿，这个需求我之前帮同事解决过，核心要搞定两个点：一是生成连续的日期序列（因为原表可能有断档的日期，比如你例子里的1987-09-11），二是计算截至每个日期的累计出生人数。下面分步骤给你讲清楚，不同数据库的写法我也都列出来了：

• 第一步：生成从最早到最晚出生日期的连续日期列表，覆盖所有中间的空白日期
• 第二步：统计每天的出生人数，再基于连续日期计算累计总和

假设你的原表名为birth_records，字段是name和date_of_birth。

PostgreSQL 版本 #

WITH date_range AS (
    -- 生成连续日期序列，从最早到最晚的出生日期
    SELECT generate_series(
        (SELECT MIN(date_of_birth) FROM birth_records),
        (SELECT MAX(date_of_birth) FROM birth_records),
        '1 day'::interval
    )::date AS date
),
daily_births AS (
    -- 统计每天的出生人数
    SELECT date_of_birth, COUNT(*) AS daily_count
    FROM birth_records
    GROUP BY date_of_birth
)
SELECT 
    dr.date,
    -- 窗口函数累计求和，自动处理空白日期的累计值
    SUM(db.daily_count) OVER (ORDER BY dr.date) AS count
FROM date_range dr
LEFT JOIN daily_births db ON dr.date = db.date_of_birth
ORDER BY dr.date;

MySQL 8.0+ 版本（支持CTE和窗口函数） #

WITH RECURSIVE date_range AS (
    -- 起始日期：表中最早的出生日期
    SELECT MIN(date_of_birth) AS date FROM birth_records
    UNION ALL
    -- 递归生成后续日期，直到最晚的出生日期
    SELECT date + INTERVAL 1 DAY FROM date_range
    WHERE date < (SELECT MAX(date_of_birth) FROM birth_records)
),
daily_births AS (
    SELECT date_of_birth, COUNT(*) AS daily_count
    FROM birth_records
    GROUP BY date_of_birth
)
SELECT 
    dr.date,
    SUM(COALESCE(db.daily_count, 0)) OVER (ORDER BY dr.date) AS count
FROM date_range dr
LEFT JOIN daily_births db ON dr.date = db.date_of_birth
ORDER BY dr.date;

注：这里用COALESCE把NULL转成0，避免SUM的时候出问题，不过其实SUM本身会忽略NULL，不加也可以，但加上更稳妥。

老版本MySQL（不支持CTE和窗口函数） #

如果你的MySQL版本比较老，用关联子查询也能实现：

SELECT 
    dates.date,
    (SELECT COUNT(*) FROM birth_records br WHERE br.date_of_birth <= dates.date) AS count
FROM (
    -- 这里用变量生成连续日期，适合老版本MySQL
    SELECT 
        (SELECT MIN(date_of_birth) FROM birth_records) + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date
    FROM 
        (SELECT 0 AS a UNION ALL SELECT 1 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) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 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) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 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) AS c
) AS dates
WHERE dates.date <= (SELECT MAX(date_of_birth) FROM birth_records)
ORDER BY dates.date;

注：这个方法用笛卡尔积生成最多1000天的日期，如果你的日期范围超过1000天，需要再加一层CROSS JOIN。

SQL Server 版本 #

WITH date_range AS (
    SELECT MIN(date_of_birth) AS date FROM birth_records
    UNION ALL
    SELECT DATEADD(DAY, 1, date) FROM date_range
    WHERE date < (SELECT MAX(date_of_birth) FROM birth_records)
),
daily_births AS (
    SELECT date_of_birth, COUNT(*) AS daily_count
    FROM birth_records
    GROUP BY date_of_birth
)
SELECT 
    dr.date,
    SUM(ISNULL(db.daily_count, 0)) OVER (ORDER BY dr.date) AS count
FROM date_range dr
LEFT JOIN daily_births db ON dr.date = db.date_of_birth
ORDER BY dr.date
OPTION (MAXRECURSION 0); -- 允许递归超过默认的100次

• 为什么要用左连接？因为要保留连续日期里的所有日期，哪怕当天没有出生记录，这样累计数就会和前一天保持一致，正好符合你要的效果。
• 窗口函数SUM() OVER (ORDER BY dr.date)是累计求和的关键，它会按日期顺序把之前所有的每日出生数加起来。
• 如果你的日期范围需要扩展到当前日期或者其他范围，只需要修改date_range里的起止日期即可。

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