SQL求助：如何用ROWS BETWEEN聚合含空值的12个月数据？

你的问题核心是原数据里没有交易的月份会被跳过，用ROWS BETWEEN会把更早的有数据的月份算进来，而不是严格取过去12个自然月。要解决这个问题，必须先补全所有缺失的年月维度，确保每个供应商-物品-站点的每个年月都有记录（哪怕金额为0），再用基于年月数值的范围窗口来计算。

具体步骤： #

1. 生成连续的年月序列：把年月转成YYYYMM格式的数值（比如202302），方便计算时间范围。
2. 生成全维度组合：把供应商、物品、站点的唯一组合，和连续年月做交叉连接，得到所有可能的维度组合，保证每个年月都有行。
3. 左连接原数据：把聚合后的交易数据和全维度组合左联，没有交易的月份金额设为0。
4. 用范围窗口计算过去12个月：基于YYYYMM数值的范围（当前年月-12 到 当前年月-1）来求和，而不是用ROWS。

完整SQL代码： #

-- 第一步：生成连续的年月序列（根据你的数据时间范围调整起止年月）
WITH date_dim AS (
    SELECT 
        YEAR(dateadd(month, n, (SELECT min(datefromparts(inv_date_year_numerical, inv_date_month_numerical, 1)) FROM invoice_flat))) AS Year_Number,
        MONTH(dateadd(month, n, (SELECT min(datefromparts(inv_date_year_numerical, inv_date_month_numerical, 1)) FROM invoice_flat))) AS Month_Number,
        YEAR(dateadd(month, n, (SELECT min(datefromparts(inv_date_year_numerical, inv_date_month_numerical, 1)) FROM invoice_flat))) * 100 + 
        MONTH(dateadd(month, n, (SELECT min(datefromparts(inv_date_year_numerical, inv_date_month_numerical, 1)) FROM invoice_flat))) AS YearMonth_Num
    FROM (
        SELECT TOP (DATEDIFF(month, (SELECT min(datefromparts(inv_date_year_numerical, inv_date_month_numerical, 1)) FROM invoice_flat), GETDATE()) + 1)
        n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
        FROM sys.all_columns ac1
        CROSS JOIN sys.all_columns ac2
    ) nums
),
-- 第二步：获取供应商-物品-站点的唯一组合
dimensions AS (
    SELECT DISTINCT
        normalized_supplier_name_inv,
        item_number,
        item_description,
        location_inv AS SiteID
    FROM invoice_flat
),
-- 第三步：生成全维度的年月组合，左联原聚合数据
full_data AS (
    SELECT
        d.normalized_supplier_name_inv,
        d.item_number,
        d.item_description,
        d.SiteID,
        dd.Year_Number,
        dd.Month_Number,
        dd.YearMonth_Num,
        ISNULL(SUM(cp.invoice_spend_usd), 0) AS TotalInvoiceSpend
    FROM dimensions d
    CROSS JOIN date_dim dd
    LEFT JOIN invoice_flat cp
        ON d.normalized_supplier_name_inv = cp.normalized_supplier_name_inv
        AND d.item_number = cp.item_number
        AND d.SiteID = cp.location_inv
        AND cp.inv_date_year_numerical = dd.Year_Number
        AND cp.inv_date_month_numerical = dd.Month_Number
    GROUP BY d.normalized_supplier_name_inv, d.item_number, d.item_description, d.SiteID, dd.Year_Number, dd.Month_Number, dd.YearMonth_Num
)
-- 第四步：计算过去12个月的基准金额
SELECT
    normalized_supplier_name_inv,
    item_number,
    item_description,
    SiteID,
    Year_Number,
    Month_Number,
    -- 用RANGE窗口，基于YearMonth_Num的范围：当前年月-12 到 当前年月-1
    SUM(TotalInvoiceSpend) OVER(
        PARTITION BY normalized_supplier_name_inv, item_number, SiteID
        ORDER BY YearMonth_Num
        RANGE BETWEEN 12 PRECEDING AND 1 PRECEDING
    ) AS BaselineSpend,
    TotalInvoiceSpend
FROM full_data
-- 可以过滤掉早于第一个有交易的年月的记录，避免无效数据
WHERE YearMonth_Num >= (SELECT MIN(YearMonth_Num) FROM full_data WHERE TotalInvoiceSpend > 0)
ORDER BY normalized_supplier_name_inv, item_number, SiteID, YearMonth_Num;

关键说明： #

• date_dim CTE：生成从数据最早年月到当前的所有连续年月，确保没有缺失。如果你用的是MySQL，把dateadd换成DATE_ADD，sys.all_columns换成information_schema.tables来生成序列。
• dimensions CTE：提取所有需要分组的维度的唯一值，避免重复。
• full_data CTE：交叉连接维度和年月，左联原数据后把空值金额设为0，保证每个年月都有记录。
• 窗口函数用RANGE：因为YearMonth_Num是连续的数值（比如202302,202303...202401），RANGE BETWEEN 12 PRECEDING AND 1 PRECEDING会严格匹配过去12个自然月的数值范围，不会跳过缺失的月份。

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