分组后LAG函数计算上年销售额错误的SQL问题修复求助

我编写了一条SQL查询，使用LAG窗口函数按客户、产品、年份和月份展示当期销售额与上年销售额。由于并非所有产品每年都会有销售，且LAG函数无法处理缺失年份的情况，因此添加了CASE语句来适配。目前明细层级下LAG函数计算的上年数据正确，但按年度分组后结果与预期不符。

表结构 #

DIM_CUSTOMER #

DECLARE @DIM_CUSTOMERS TABLE([BusinessKey] INT,[Customer] NVARCHAR(255))
 
INSERT INTO @DIM_CUSTOMERS
VALUES 
(10000, 'Kevin N.V.'),
(10001, 'V.Z.W. Frederik'),
(10002, 'Klaas N.V.')
SELECT * FROM @DIM_CUSTOMERS

DIM_PRODUCTS #

DECLARE @DIM_PRODUCTS TABLE([BusinessKey] INT, [Product] NVARCHAR(255))

INSERT INTO @DIM_PRODUCTS
VALUES
(9000, 'PH114'),
(9001, 'PH272'),
(9002, 'PH878'),
(9003, 'PH900')
SELECT * FROM @DIM_PRODUCTS

DIM_DATES #

DECLARE @DIM_DATES TABLE([BusinessKey] INT, [Year] INT, [Month] INT, [YearMonth] INT, [YearMonthText] NVARCHAR(20))

INSERT INTO @DIM_DATES
VALUES
(202201, 2022, 1, 202201, '2022.01'),
(202202, 2022, 2, 202202, '2022.02'),
(202203, 2022, 3, 202203, '2022.03'),
(202204, 2022, 4, 202204, '2022.04'),
(202205, 2022, 5, 202205, '2022.05'),
(202206, 2022, 6, 202206, '2022.06'),
(202207, 2022, 7, 202207, '2022.07'),
(202208, 2022, 8, 202208, '2022.08'),
(202209, 2022, 9, 202209, '2022.09'),
(202210, 2022, 10, 202210, '2022.10'),
(202211, 2022, 11, 202211, '2022.11'),
(202212, 2022, 12, 202212, '2022.12'),
(202301, 2023, 1, 202301, '2023.01'),
(202302, 2023, 2, 202302, '2023.02'),
(202303, 2023, 3, 202303, '2023.03'),
(202304, 2023, 4, 202304, '2023.04'),
(202305, 2023, 5, 202305, '2023.05'),
(202306, 2023, 6, 202306, '2023.06'),
(202307, 2023, 7, 202307, '2023.07'),
(202308, 2023, 8, 202308, '2023.08'),
(202309, 2023, 9, 202309, '2023.09'),
(202310, 2023, 10, 202310, '2023.10'),
(202311, 2023, 11, 202311, '2023.11'),
(202312, 2023, 12, 202312, '2023.12'),
(202401, 2024, 1, 202401, '2024.01'),
(202402, 2024, 2, 202402, '2024.02'),
(202403, 2024, 3, 202403, '2024.03'),
(202404, 2024, 4, 202404, '2024.04'),
(202405, 2024, 5, 202405, '2024.05'),
(202406, 2024, 6, 202406, '2024.06'),
(202407, 2024, 7, 202407, '2024.07'),
(202408, 2024, 8, 202408, '2024.08'),
(202409, 2024, 9, 202409, '2024.09'),
(202410, 2024, 10, 202410, '2024.10'),
(202411, 2024, 11, 202411, '2024.11'),
(202412, 2024, 12, 202412, '2024.12')
SELECT * FROM @DIM_DATES

FACT_SALES #

DECLARE @FACT_SALES TABLE([ID] INT, [FK_Product] INT, [FK_Customer] INT, [FK_Date] INT, [Sales] FLOAT)

INSERT INTO @FACT_SALES
VALUES
(1, 9000, 10000, 202303, 90.48),
(2, 9000, 10000, 202304, 20.40),
(3, 9002, 10000, 202305, 250.85),
(4, 9002, 10000, 202303, 100.50),
(5, 9000, 10000, 202403, 38.40),
(6, 9000, 10000, 202406, 474.50),
(7, 9001, 10000, 202403, 128.60),
(8, 9001, 10000, 202404, 144.97),
(9, 9000, 10002, 202303, 199.60),
(10, 9001, 10002, 202302, 58.97),
(11, 9001, 10002, 202402, 40.88),
(12, 9001, 10000, 202203, 14.5)
SELECT * FROM @FACT_SALES

尝试的SQL代码 #

;WITH CustProdYears 
    AS(
        SELECT DISTINCT 
            d.[Year] as SaleYear, s.FK_Product, s.FK_Customer
            FROM @FACT_SALES   s     
            JOIN @DIM_DATES d on s.FK_Date = d.BusinessKey
    )
, CustomerSales 
    AS (
        SELECT cpy.FK_Customer, cpy.FK_Product, d.YearMonthText, s.[Sales], d.year, d.month
            FROM CustProdYears cpy 
            JOIN @DIM_DATES d on cpy.[SaleYear] = d.[Year]
            LEFT JOIN @FACT_SALES s 
                on s.FK_Customer = cpy.FK_Customer
                and s.FK_Product = cpy.FK_Product
                and s.FK_Date = d.BusinessKey
    )
SELECT b.Year,
        SUM(b.[Sales]) AS [Sales],
        SUM(b.[SalesLastYear]) AS [SalesLastYear]
FROM
(
    SELECT *,
        LAG(a.year, 1, 0) OVER (PARTITION BY a.Customer,  a.Product, a.month ORDER BY a.year) AS [PreviousYear],
        CASE WHEN 1 = a.year - LAG(a.year, 1, 0) OVER (PARTITION BY a.Customer,  a.Product, a.month ORDER BY a.year)
            THEN LAG(a.[Sales], 1, 0) OVER (PARTITION BY a.Customer,  a.Product, a.month ORDER BY a.year)
            ELSE 0 END AS [SalesLastYear]
    FROM
    (
    SELECT 
          Customer, Product, Year, Month
        , [Sales] = ISNULL([Sales], 0)
        FROM @DIM_CUSTOMERS c 
            LEFT JOIN CustomerSales s ON c.BusinessKey = s.FK_Customer
            LEFT JOIN @DIM_PRODUCTS p on s.FK_Product = p.BusinessKey
            WHERE Customer = 'Kevin N.V.'

    ) a
    --ORDER BY a.Customer, a.Product, a.year, a.month
) b
GROUP BY b.Year
ORDER BY b.Year

当前查询结果 #

预期结果 #

问题原因及修复方案 #

问题原因 #

原代码在月维度计算每个客户-产品-月份的上年销售额，再直接求和得到年度结果。这种逻辑仅统计了有对应上年同月记录的销售额，忽略了上年其他月份的销售数据，导致年度汇总的上年销售额远小于实际值。

修复方案 #

正确逻辑是先按年度汇总每个客户-产品的年度总销售额，再对年度汇总表使用LAG窗口函数取上年年度销售额，最后按年度汇总结果。

修复后的SQL代码 #

-- 第一步：按客户、产品、年度汇总销售额，确保全量组合
WITH AnnualSales AS (
    SELECT 
        c.Customer,
        p.Product,
        d.Year,
        ISNULL(SUM(s.Sales), 0) AS AnnualTotalSales
    FROM @DIM_CUSTOMERS c
    CROSS JOIN @DIM_PRODUCTS p
    CROSS JOIN (SELECT DISTINCT Year FROM @DIM_DATES) d
    LEFT JOIN @FACT_SALES s 
        ON c.BusinessKey = s.FK_Customer 
        AND p.BusinessKey = s.FK_Product
        AND EXISTS (SELECT 1 FROM @DIM_DATES dd WHERE dd.BusinessKey = s.FK_Date AND dd.Year = d.Year)
    WHERE c.Customer = 'Kevin N.V.'
    GROUP BY c.Customer, p.Product, d.Year
),
-- 第二步：计算每个客户-产品的上年年度销售额
AnnualSalesWithPrevYear AS (
    SELECT 
        Year,
        AnnualTotalSales,
        LAG(AnnualTotalSales, 1, 0) OVER (PARTITION BY Customer, Product ORDER BY Year) AS PreviousYearSales
    FROM AnnualSales
)
-- 第三步：按年度汇总最终结果
SELECT 
    Year,
    SUM(AnnualTotalSales) AS Sales,
    SUM(PreviousYearSales) AS SalesLastYear
FROM AnnualSalesWithPrevYear
GROUP BY Year
ORDER BY Year;

代码说明 #

1. AnnualSales CTE：生成客户-产品-年度的完整组合，确保每个客户-产品每年都有记录（无销售则为0），并汇总年度销售额。
2. AnnualSalesWithPrevYear CTE：使用LAG窗口函数，按客户和产品分组，取每个客户-产品的上年年度销售额。
3. 最终查询：按年度汇总所有客户-产品的销售额和上年销售额，得到符合预期的结果。

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