在数据处理的实际场景中，我们经常会遇到原始数据的时间维度不连续的情况，比如某个月的销售数据只记录了有交易的日期，缺失了无交易的日期，这时候就需要先生成连续的日期序列，再完成时间维度的补齐，才能进行完整的统计计算。
一、为什么需要生成连续日期
时间维度是很多统计分析的基础维度，当原始数据存在日期缺失时，直接进行按日统计、环比计算等操作会得到错误的结果。比如统计某月每日的销售额，缺失的日期会被直接跳过，导致统计结果不完整。生成连续日期后再和原始数据左连接，就能把缺失日期的对应指标填充为0或者默认值，完成时间维度的补齐。
二、通用思路：借助数字序列生成连续日期
生成连续日期的核心思路是先生成一组连续的整数序列，再把这些整数作为偏移量，和起始日期相加得到连续的日期。不同数据库的实现方式略有差异，下面分别介绍常见数据库的实现方法。
2.1 MySQL 8.0+ 实现方法
MySQL 8.0支持递归CTE，可以用递归的方式生成连续的数字序列，再结合DATE_ADD函数生成连续日期。
假设需要生成2024-01-01到2024-01-10的连续日期，代码如下：
-- 生成2024-01-01到2024-01-10的连续日期
WITH RECURSIVE date_series AS (
-- 初始行，起始日期
SELECT DATE('2024-01-01') AS continuous_date
UNION ALL
-- 递归部分，每次日期加1天，直到达到结束日期
SELECT DATE_ADD(continuous_date, INTERVAL 1 DAY)
FROM date_series
WHERE continuous_date < DATE('2024-01-10')
)
SELECT continuous_date FROM date_series;
如果需要补齐时间维度，只需要把生成的连续日期序列作为主表，左连接原始业务数据即可：
-- 连续日期左连接销售数据，补齐时间维度
WITH RECURSIVE date_series AS (
SELECT DATE('2024-01-01') AS continuous_date
UNION ALL
SELECT DATE_ADD(continuous_date, INTERVAL 1 DAY)
FROM date_series
WHERE continuous_date < DATE('2024-01-10')
)
SELECT
ds.continuous_date AS sale_date,
COALESCE(s.sale_amount, 0) AS sale_amount -- 缺失日期的销售额填充为0
FROM date_series ds
LEFT JOIN sale_table s ON ds.continuous_date = s.sale_date
ORDER BY ds.continuous_date;
2.2 PostgreSQL 实现方法
PostgreSQL有内置的generate_series函数，可以直接生成连续的日期序列，不需要手动写递归逻辑，使用起来更加简洁。
生成2024-01-01到2024-01-10的连续日期代码如下：
-- 使用generate_series生成连续日期
SELECT generate_series(
DATE('2024-01-01'),
DATE('2024-01-10'),
INTERVAL '1 day'
) AS continuous_date;
时间维度补齐的示例：
-- 连续日期左连接业务数据补齐维度
SELECT
ds.continuous_date AS stat_date,
COALESCE(t.user_count, 0) AS user_count
FROM (
SELECT generate_series(
DATE('2024-01-01'),
DATE('2024-01-10'),
INTERVAL '1 day'
) AS continuous_date
) ds
LEFT JOIN user_stat t ON ds.continuous_date = t.stat_date
ORDER BY ds.continuous_date;
2.3 SQL Server 实现方法
SQL Server可以使用递归CTE或者内置的master..spt_values系统表来生成连续数字，再转换为连续日期。
使用递归CTE生成连续日期的代码如下：
-- 递归CTE生成连续日期
WITH date_series AS (
SELECT CAST('2024-01-01' AS DATE) AS continuous_date
UNION ALL
SELECT DATEADD(DAY, 1, continuous_date)
FROM date_series
WHERE continuous_date < CAST('2024-01-10' AS DATE)
)
SELECT continuous_date FROM date_series
OPTION (MAXRECURSION 0); -- 避免递归次数限制报错
使用系统表生成连续日期的代码如下，这种方式不需要递归，性能更好：
-- 使用系统表生成连续数字再转日期
SELECT
DATEADD(DAY, number, CAST('2024-01-01' AS DATE)) AS continuous_date
FROM master..spt_values
WHERE type = 'P' -- 筛选数字类型的行
AND number <= DATEDIFF(DAY, '2024-01-01', '2024-01-10') -- 控制日期范围
ORDER BY number;
三、注意事项
- 生成连续日期时要注意起始日期和结束日期的格式，不同数据库的日期类型转换函数不同，需要对应使用正确的函数。
- 使用递归CTE生成日期时，要注意数据库对递归次数的限制，比如SQL Server默认递归上限是100次，超过需要设置
MAXRECURSION参数。 - 时间维度补齐时，左连接的关联条件要准确匹配日期字段，避免日期格式不一致导致关联失败。
- 如果原始数据的日期包含时间部分，生成连续日期时需要先对原始日期做日期截断，只保留日期部分再关联。
四、总结
SQL生成连续日期的核心是借助连续数字序列作为偏移量，结合日期加减函数实现，不同数据库有不同的内置函数或者实现方式。生成连续日期后，通过左连接原始数据就能完成时间维度的补齐，解决日期缺失导致的统计问题。大家可以根据自己的数据库类型选择合适的实现方法，提升日期相关统计的准确性。