如何计算上年累计QTD销售额？SQL查询修正求助

需求说明 #

• 计算上年累计季度至今（Prior Year Cumulative QTD）销售额
• 具体规则：
  1. 对于SalesID=1的2022年Q1，上年累计QTD销售额为2021年Q1的45；Q2为2021年Q1+Q2销售额之和；Q3为2021年Q1+Q2+Q3销售额之和。
  2. 对于SalesID=2，因缺失2021年Q1、Q2及2022年Q3数据，2022年Q4的上年累计QTD销售额为2021年Q3+Q4销售额之和。

当前问题 #

现有SQL查询用LAG(SalesAmt,4)的方式计算PriorCumulativeQTDSales，既无法覆盖缺失季度的场景，也没法实现累计求和，结果不符合需求。

测试数据SQL #

;with Data AS (
    select 2021 as [year], 1 as [Quarter] , 1 as SalesID,'1/29/2021' AS Date, 45 AS SalesAmt 
    UNION 
    select 2021 as [year], 2 as [Quarter] , 1 as SalesID,'4/26/2022' AS Date, 100 AS SalesAmt 
    UNION 
    select 2021 as [year], 3 as [Quarter] , 1 as SalesID,'8/29/2021' AS Date, 100 AS SalesAmt 
    UNION 
    select 2021 as [year], 4 as [Quarter] , 1 as SalesID,'11/26/2022' AS Date,50 AS SalesAmt 
    UNION
    select 2022 as [year], 1 as [Quarter] , 1 as SalesID,'1/25/2022' AS Date,100 AS SalesAmt 
    UNION 
    select 2022 as [year], 2 as [Quarter] , 1 as SalesID,'5/22/2022' AS Date,200 AS SalesAmt 
    UNION 
    select 2022 as [year], 3 as [Quarter] , 1 as SalesID,'7/16/2022' AS Date,300 AS SalesAmt 
    UNION 
    select 2022 as [year], 4 as [Quarter] , 1 as SalesID,'12/16/2022' AS Date,400 AS SalesAmt 
    UNION--
    select 2021 as [year], 3 as [Quarter] , 2 as SalesID,'1/26/2021' AS Date, 10 AS SalesAmt 
    UNION 
    select 2021 as [year], 4 as [Quarter] , 2 as SalesID,'12/15/2021' AS Date, 15 AS SalesAmt 
    UNION 
    select 2022 as [year], 1 as [Quarter] , 2 as SalesID,'1/10/2022' AS Date, 20 AS SalesAmt 
    UNION 
    select 2022 as [year], 2 as [Quarter] , 2 as SalesID,'4/10/2022' AS Date, 20 AS SalesAmt 
    UNION 
    select 2022 as [year], 4 as [Quarter] , 2 as SalesID,'10/24/2022' AS Date, 20 AS salesAmt 
) 
 select [Year],[quarter],[Date],SalesID     
,SUM(SalesAmt) OVER (Partition by [Year],SalesId order by [quarter]) As YTDSales
,LAG(SalesAmt,4) OVER (  Partition by SalesId order by [Year]) As PriorCumulativeQTDSales 
 from  data a

修正方案 #

要正确计算上年累计QTD，得先算出每个SalesID、年份的季度累计销售额，再关联上一年对应季度及之前的所有销售额求和。修正后的SQL如下：

;with Data AS (
    select 2021 as [year], 1 as [Quarter] , 1 as SalesID,'1/29/2021' AS Date, 45 AS SalesAmt 
    UNION 
    select 2021 as [year], 2 as [Quarter] , 1 as SalesID,'4/26/2022' AS Date, 100 AS SalesAmt 
    UNION 
    select 2021 as [year], 3 as [Quarter] , 1 as SalesID,'8/29/2021' AS Date, 100 AS SalesAmt 
    UNION 
    select 2021 as [year], 4 as [Quarter] , 1 as SalesID,'11/26/2022' AS Date,50 AS SalesAmt 
    UNION
    select 2022 as [year], 1 as [Quarter] , 1 as SalesID,'1/25/2022' AS Date,100 AS SalesAmt 
    UNION 
    select 2022 as [year], 2 as [Quarter] , 1 as SalesID,'5/22/2022' AS Date,200 AS SalesAmt 
    UNION 
    select 2022 as [year], 3 as [Quarter] , 1 as SalesID,'7/16/2022' AS Date,300 AS SalesAmt 
    UNION 
    select 2022 as [year], 4 as [Quarter] , 1 as SalesID,'12/16/2022' AS Date,400 AS SalesAmt 
    UNION--
    select 2021 as [year], 3 as [Quarter] , 2 as SalesID,'1/26/2021' AS Date, 10 AS SalesAmt 
    UNION 
    select 2021 as [year], 4 as [Quarter] , 2 as SalesID,'12/15/2021' AS Date, 15 AS SalesAmt 
    UNION 
    select 2022 as [year], 1 as [Quarter] , 2 as SalesID,'1/10/2022' AS Date, 20 AS SalesAmt 
    UNION 
    select 2022 as [year], 2 as [Quarter] , 2 as SalesID,'4/10/2022' AS Date, 20 AS SalesAmt 
    UNION 
    select 2022 as [year], 4 as [Quarter] , 2 as SalesID,'10/24/2022' AS Date, 20 AS salesAmt 
),
YearlyQuarterlyCumulative AS (
    -- 计算每个SalesID、年份、季度的累计销售额，同时关联上一年数据计算累计QTD
    SELECT 
        curr.[Year],
        curr.[Quarter],
        curr.SalesID,
        curr.Date,
        curr.SalesAmt,
        SUM(curr.SalesAmt) OVER (PARTITION BY curr.SalesID, curr.[Year] ORDER BY curr.[Quarter]) AS YTDSales,
        -- 筛选上一年中季度≤当前季度的销售额，求和得到上年累计QTD
        SUM(CASE WHEN prev.[Year] = curr.[Year] - 1 AND prev.[Quarter] <= curr.[Quarter] THEN prev.SalesAmt ELSE 0 END) 
            OVER (PARTITION BY curr.SalesID, curr.[Year], curr.[Quarter]) AS PriorCumulativeQTDSales
    FROM Data curr
    LEFT JOIN Data prev ON curr.SalesID = prev.SalesID AND prev.[Year] = curr.[Year] - 1
)
SELECT 
    [Year],
    [Quarter],
    Date,
    SalesID,
    YTDSales,
    PriorCumulativeQTDSales
FROM YearlyQuarterlyCumulative
ORDER BY SalesID, [Year], [Quarter];

修正说明 #

• 用自关联把当前年份的记录和上一年的记录绑定，通过条件筛选出上一年度里季度不大于当前季度的所有销售额，求和后就是符合需求的上年累计QTD数值。
• 不管有没有缺失季度，这个逻辑都能自动处理，比如SalesID=2的2022年Q4，会自动把2021年Q3、Q4的销售额加起来。
• 原有的当年累计销售额YTDSales计算逻辑保留，结果不受影响。

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