SQL实现补全缺失月份 填充连续行号、目标值与累计销售额

核心约束与要求 #

• 返回查询周期内1-12月完整记录，补全无交易的缺失月份
• 行号从1到12连续编号，支持直接通过行号关联存储目标值的属性表
• 无交易的缺失月份累计销售额沿用上一个有交易月份的累计值
• 避免使用CTE，支持直接关联string_split()等查询结果

原有逻辑缺陷 #

原有查询仅返回存在交易记录的月份，示例场景下仅返回1-5月、8-12月共10条数据，行号、累计值均按有数据的月份计算，缺失6、7月条目，无法满足连续数据要求。原有代码如下：

SELECT DISTINCT DATEPART(MONTH,rpt_date)
, DENSE_RANK() OVER (ORDER BY DATEPART(MONTH,rpt_date)) as [row_no]
, CAST(SUM(total_sales_amt) OVER (ORDER BY DATEPART(MONTH,rpt_date)) AS DECIMAL(14,2))
FROM summary_receipt
WHERE outlet_id = 174
AND bus_id = 6
AND rpt_date between CAST('2021-06-01' AS DATE) AND CAST('2022-05-31' AS DATE)

可直接使用的实现代码 #

全程使用派生表实现，无CTE，可直接在外层关联其他查询结果：

SELECT
    m.month_num AS [month],
    ROW_NUMBER() OVER (ORDER BY m.month_num) AS [row_no],
    CAST(
        -- 向前取最近非空累计值，自动填充缺失月份
        LAST_VALUE(t.accumulate_sales) IGNORE NULLS
        OVER (ORDER BY m.month_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS DECIMAL(14,2)) AS accumulate_sales
FROM
    -- 生成1-12连续月份序列
    (SELECT number AS month_num FROM master..spt_values WHERE type = 'P' AND number BETWEEN 1 AND 12) m
LEFT JOIN
    -- 原表聚合逻辑，计算有交易月份的累计值
    (
        SELECT
            DATEPART(MONTH, rpt_date) AS month_num,
            SUM(SUM(total_sales_amt)) OVER (ORDER BY DATEPART(MONTH, rpt_date)) AS accumulate_sales
        FROM summary_receipt
        WHERE outlet_id = 174
          AND bus_id = 6
          AND rpt_date BETWEEN CAST('2021-06-01' AS DATE) AND CAST('2022-05-31' AS DATE)
        GROUP BY DATEPART(MONTH, rpt_date)
    ) t ON m.month_num = t.month_num
ORDER BY m.month_num

逻辑说明 #

• 直接通过系统内置数字表spt_values生成1-12的连续月份序列，无需额外建表或定义CTE，外层可直接关联属性表、string_split()拆分结果，适配后续关联需求
• 行号基于连续月份序列通过ROW_NUMBER()生成，天然为1-12连续无跳号的编号，和属性表行号匹配规则完全对齐
• 缺失月份的累计值通过窗口函数自动填充，示例场景下6、7月累计值会自动继承5月的5861237，8月及之后的累计值按实际交易数据顺延计算
• 若使用的SQL Server版本不支持IGNORE NULLS语法，可将累计值计算逻辑替换为以下写法，效果完全一致：

CAST(
    MAX(t.accumulate_sales) 
    OVER (ORDER BY m.month_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS DECIMAL(14,2)) AS accumulate_sales

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