如何在日期类型列中逐日插入连续多月日期以统计每日账户数

首先纠正你原有查询的逻辑问题：你给出的SQL select count(*) from Accounts where created_date > CURRENT_DATE; 条件写反了，这个语句会统计创建时间晚于当前日期的异常数据，正确的当日累计账户数判断逻辑应该是账户创建日期小于等于统计日期。

生成指定起始点的连续日期序列 #

主流数据库都可以通过递归CTE直接生成连续日期，不需要额外维护数字辅助表，不同数据库的适配写法如下：

• MySQL 8.0+ / PostgreSQL 通用写法

-- 生成超过1000天序列时先执行：SET SESSION cte_max_recursion_depth = 10000;
WITH RECURSIVE date_series AS (
    -- 锚点：设置序列起始日期
    SELECT DATE('2022-01-01') AS stat_date
    UNION ALL
    -- 递归：每次日期+1天，截止到当前日期
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE stat_date < CURRENT_DATE
)
SELECT * FROM date_series;

• SQL Server 写法

WITH date_series AS (
    SELECT CAST('2022-01-01' AS DATE) AS stat_date
    UNION ALL
    SELECT DATEADD(day, 1, stat_date)
    FROM date_series
    WHERE stat_date < GETDATE()
)
SELECT * FROM date_series
OPTION (MAXRECURSION 0); -- 取消默认100层的递归限制，支持长周期日期生成

如果你使用的是不支持递归CTE的MySQL 5.x版本，可以自建一张存0-9数字的辅助表，通过多表交叉连接生成数字序列，再和起始日期相加得到连续日期。

一次性统计全日期的累计账户数 #

不需要逐行循环比对日期，直接通过左关联+分组的方式，一条SQL就能算出所有日期的账户总量，执行效率远高于逐行查询：

WITH RECURSIVE date_series AS (
    SELECT DATE('2022-01-01') AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE stat_date < CURRENT_DATE
)
SELECT
    ds.stat_date,
    COUNT(a.created_date) AS total_accounts
FROM date_series ds
LEFT JOIN Accounts a
    ON a.created_date <= ds.stat_date
GROUP BY ds.stat_date
ORDER BY ds.stat_date;

落地为报表专用表 #

如果需要把统计结果持久化供报表直接查询，可以按如下逻辑建表并初始化数据，后续每天定时追加最新日期的统计值即可：

-- 新建报表专用表
CREATE TABLE IF NOT EXISTS daily_account_report (
    stat_date DATE PRIMARY KEY COMMENT '统计日期',
    total_accounts INT NOT NULL COMMENT '当日累计账户总量',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '数据更新时间'
);

-- 初始化2022-01-01至今的全量统计数据
INSERT INTO daily_account_report (stat_date, total_accounts)
WITH RECURSIVE date_series AS (
    SELECT DATE('2022-01-01') AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE stat_date < CURRENT_DATE
)
SELECT
    ds.stat_date,
    COUNT(a.created_date) AS total_accounts
FROM date_series ds
LEFT JOIN Accounts a
    ON a.created_date <= ds.stat_date
GROUP BY ds.stat_date;

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