问题背景与核心思路
在实际业务场景中，我们经常会需要按日期维度统计指标，比如每日用户活跃数、每日销售额等。但原始业务数据可能存在日期缺失的情况，比如某天没有订单产生，查询时就不会出现该日期的记录，导致统计结果不连续，影响后续的趋势分析。要解决这个问题，核心思路是先生成一段连续的日期序列作为基准，再将业务数据表与这个日期序列做左连接，没有匹配到业务数据的日期就会自动填充为NULL，后续再根据需求处理NULL值即可。
核心步骤拆解
- 第一步：生成需要覆盖的完整日期序列，序列的起始和结束日期根据业务需求确定，比如统计最近30天的数据就生成最近30天的连续日期
- 第二步：将生成的日期序列作为左表，业务数据表作为右表，通过日期字段做左连接
- 第三步：对连接后的结果进行处理，将NULL的业务指标替换为0或者其他默认值
不同数据库的日期序列生成方法
MySQL实现方案
MySQL8.0及以上版本支持递归公用表表达式（CTE），可以用来生成连续的日期序列，下面是一个生成最近7天日期序列并填充缺失订单记录的示例：
-- 生成最近7天的日期序列
WITH RECURSIVE date_series AS (
SELECT CURDATE() - INTERVAL 6 DAY AS stat_date
UNION ALL
SELECT stat_date + INTERVAL 1 DAY
FROM date_series
WHERE stat_date < CURDATE()
)
-- 左连接订单表填充缺失日期
SELECT
ds.stat_date,
COALESCE(o.order_count, 0) AS order_count
FROM date_series ds
LEFT JOIN (
-- 按日期统计订单数
SELECT
DATE(create_time) AS order_date,
COUNT(*) AS order_count
FROM order_table
WHERE create_time >= CURDATE() - INTERVAL 6 DAY
GROUP BY DATE(create_time)
) o ON ds.stat_date = o.order_date
ORDER BY ds.stat_date;
PostgreSQL实现方案
PostgreSQL有内置的generate_series函数，可以更方便地生成日期序列，示例如下：
-- 生成最近7天的日期序列，左连接订单表
SELECT
ds.stat_date,
COALESCE(o.order_count, 0) AS order_count
FROM generate_series(
CURRENT_DATE - INTERVAL '6 days',
CURRENT_DATE,
INTERVAL '1 day'
) AS ds(stat_date)
LEFT JOIN (
SELECT
DATE(create_time) AS order_date,
COUNT(*) AS order_count
FROM order_table
WHERE create_time >= CURRENT_DATE - INTERVAL '6 days'
GROUP BY DATE(create_time)
) o ON ds.stat_date = o.order_date
ORDER BY ds.stat_date;
SQL Server实现方案
SQL Server可以通过递归CTE或者数字表生成日期序列，示例如下：
-- 生成最近7天的日期序列
WITH date_series AS (
SELECT CAST(GETDATE() - 6 AS DATE) AS stat_date
UNION ALL
SELECT DATEADD(DAY, 1, stat_date)
FROM date_series
WHERE stat_date < CAST(GETDATE() AS DATE)
)
-- 左连接订单表填充缺失日期
SELECT
ds.stat_date,
COALESCE(o.order_count, 0) AS order_count
FROM date_series ds
LEFT JOIN (
SELECT
CAST(create_time AS DATE) AS order_date,
COUNT(*) AS order_count
FROM order_table
WHERE create_time >= CAST(GETDATE() - 6 AS DATE)
GROUP BY CAST(create_time AS DATE)
) o ON ds.stat_date = o.order_date
ORDER BY ds.stat_date;
注意事项与业务适配
在实际使用中需要注意几个问题：
- 日期序列的范围要和统计需求匹配，避免生成过多无用的日期增加查询开销
- 左连接时的日期字段类型要保持一致，比如都是DATE类型，避免出现隐式转换导致连接失败
- 如果业务需要填充更细的时间粒度，比如按小时、按分钟，只需要调整日期序列的生成间隔即可，比如MySQL中把INTERVAL 1 DAY改成INTERVAL 1 HOUR就能生成小时序列
- 如果原始业务表数据量很大，建议先对业务表做日期过滤，减少连接的数据量，提升查询效率
这种利用左连接和日期序列生成的方法，不仅可以填充日期缺失，也可以适配其他连续维度缺失的场景，比如按连续的编号维度统计时，同样可以先生成编号序列再做左连接填充。