如何在SQL Server中为维度各分类补全全量日期并填充缺失值

问题描述 #

现有数据记录了每种动物的月度数量统计值，默认仅对存在数据的月份做聚合。

希望为每种动物补全截止到当月的全量日期序列，无数据的月份统计值填充为0，期望效果如下：

请问该需求是否可以在SQL Server中实现，而无需通过Excel处理？

实现方案 #

完全可以在SQL Server中直接实现，无需借助Excel处理，核心逻辑是通过全量日期序列+动物维度的笛卡尔积补全所有组合，再左关联原统计数据填充0即可，参考实现代码如下：

示例表结构（可根据实际业务调整字段名） #

假设原统计表命名为animal_monthly_count，包含字段：

• animal_name 动物名称
• stat_month 统计月份，建议统一用每月1号的日期格式存储，也支持yyyy-MM格式的字符串
• count_val 月度统计值

完整实现代码 #

WITH 
-- 递归CTE生成连续月份序列，起始值替换为你数据中最早的统计月份即可
month_series AS (
    SELECT CAST('2023-01-01' AS DATE) AS month_date
    UNION ALL
    SELECT DATEADD(MONTH, 1, month_date)
    FROM month_series
    WHERE month_date < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) -- 截止到当月1号
),
-- 获取所有去重的动物维度列表
animal_list AS (
    SELECT DISTINCT animal_name
    FROM animal_monthly_count
),
-- 生成全量的「动物-月份」组合
full_combination AS (
    SELECT a.animal_name, m.month_date
    FROM animal_list a
    CROSS JOIN month_series m
)
-- 左关联原表，缺失统计值填充为0
SELECT 
    fc.animal_name,
    FORMAT(fc.month_date, 'yyyy-MM') AS stat_month,
    ISNULL(t.count_val, 0) AS count_val
FROM full_combination fc
LEFT JOIN animal_monthly_count t 
    ON fc.animal_name = t.animal_name
    -- 统一按月份维度匹配，若原表stat_month已经是每月1号的日期格式可简化关联条件
    AND fc.month_date = DATEFROMPARTS(YEAR(t.stat_month), MONTH(t.stat_month), 1)
ORDER BY fc.animal_name, fc.month_date
-- 若统计月份跨度超过100个月，加上下面这句取消递归层数限制
-- OPTION (MAXRECURSION 0)

扩展说明 #

如果不需要给动物补全首次出现前的月份，只需要把笛卡尔积逻辑调整为和每个动物的首次统计月份做范围匹配即可。

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