SQL计算滚动12/4个月销售数据时无销售月份结果为空如何解决

计算异常的核心原因是维度值缺失：原表仅保留存在销售记录的(Month, Organization, Trade)组合，无销售的月份不会出现在GROUP BY结果集中，窗口函数无对应行参与计算，遇到断档月份就会返回空值。此外原SQL未显式指定窗口范围，默认计算的是从首月到当前月的累计值，并非固定窗口的滚动统计结果。

• 构造连续月份序列，覆盖完整统计周期
• 用月份、组织、交易类型三个维度做笛卡尔积，生成所有可能的维度组合，确保无销售的月份也存在对应行
• 左关联原销售数据，将无销售月份的NS值统一置为0
• 显式指定窗口函数的行范围，分别计算滚动4个月、滚动12个月的统计值

-- 生成统计周期内的连续月份，可按需修改起止时间
WITH AllMonths AS (
    SELECT CAST('2023-01-01' AS DATE) AS Month
    UNION ALL
    SELECT DATEADD(MONTH, 1, Month)
    FROM AllMonths
    WHERE Month < CAST('2024-12-01' AS DATE)
),
-- 生成 月份+组织+交易类型 的全量维度组合
FullDimension AS (
    SELECT
        am.Month,
        org.Organization,
        trd.Trade
    FROM AllMonths am
    CROSS JOIN (SELECT DISTINCT Organization FROM Table_Name) org
    CROSS JOIN (SELECT DISTINCT Trade FROM Table_Name) trd
),
-- 补全缺失月份的月度销售额，无销售则记为0
MonthlyAgg AS (
    SELECT
        fd.Month,
        fd.Organization,
        fd.Trade,
        ISNULL(SUM(raw.NS), 0) AS MonthlyNS
    FROM FullDimension fd
    LEFT JOIN Table_Name raw
        ON fd.Month = raw.Month
        AND fd.Organization = raw.Organization
        AND fd.Trade = raw.Trade
    GROUP BY fd.Month, fd.Organization, fd.Trade
)
-- 计算指定窗口的滚动销售额
SELECT
    Month,
    Organization,
    Trade,
    MonthlyNS,
    -- 滚动4个月：当前月+前3个月
    SUM(MonthlyNS) OVER (
        PARTITION BY Organization, Trade
        ORDER BY Month
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS R4month,
    -- 滚动12个月：当前月+前11个月
    SUM(MonthlyNS) OVER (
        PARTITION BY Organization, Trade
        ORDER BY Month
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS Rolling12Month
FROM MonthlyAgg
ORDER BY Organization, Trade, Month

• 原表和维度表关联时，组织、交易类型、月份的匹配条件必须写在LEFT JOIN的ON子句中，不能写在WHERE子句里，否则会过滤掉无销售的空行
• 如果Month字段是字符串格式（如2024-01），生成连续月份时要保证格式、排序规则和原字段完全一致，避免窗口排序错误
• 若使用MySQL、PostgreSQL等其他数据库，连续月份生成逻辑可替换为系统自带的日期序列函数（如MySQL的WITH RECURSIVE、PostgreSQL的generate_series），核心逻辑不变

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