SQL查询中4 to 1季度销售差值列数值错误修正求助

问题原因 #

原查询中「4 to 1」列的逻辑是直接返回当年Q1的销售额，这不符合该列实际需求——当年Q1销售额与上一年Q4销售额的差值，因此数值完全错误。

修正方案 #

以下是调整后的完整查询，核心是新增跨年度的季度销售额对比逻辑，修复「4 to 1」列的计算：

WITH Sales_By_Quarter AS
(
    SELECT
        DATEPART(YEAR, OrderDate) AS [Year],
        DATEPART(QUARTER, OrderDate) AS [Quarter],
        SUM(TotalDue) AS [Quarterly Sales],
        -- 同年度内季度间变化
        SUM(TotalDue) - LAG(SUM(TotalDue)) OVER (PARTITION BY DATEPART(YEAR, OrderDate) ORDER BY DATEPART(QUARTER, OrderDate)) AS [IntraYear Change],
        -- 跨年度Q4到Q1的变化：去掉年份分区，获取上一个季度（可能是上一年Q4）的销售额
        CASE WHEN DATEPART(QUARTER, OrderDate) = 1 
             THEN SUM(TotalDue) - LAG(SUM(TotalDue)) OVER (ORDER BY DATEPART(YEAR, OrderDate), DATEPART(QUARTER, OrderDate))
             ELSE NULL 
        END AS [Q4_to_Q1_Change]
    FROM Sales.SalesOrderHeader
    GROUP BY DATEPART(YEAR, OrderDate), DATEPART(QUARTER, OrderDate)
),
Annual_Sales AS
(
    SELECT
        [Year],
        SUM([Quarterly Sales]) AS [Total Annual Sales],
        SUM([Quarterly Sales]) - LAG(SUM([Quarterly Sales])) OVER (ORDER BY [Year]) AS [Annual Growth]
    FROM Sales_By_Quarter
    GROUP BY [Year]
)
SELECT
    sbq.[Year],
    SUM(CASE WHEN sbq.[Quarter] = 1 THEN sbq.[Quarterly Sales] END) AS [Q1],
    SUM(CASE WHEN sbq.[Quarter] = 1 THEN (sbq.[Quarterly Sales]/asl.[Total Annual Sales]*100) END) as [Q1 Annual %],
    -- 替换为跨年度变化值
    SUM(CASE WHEN sbq.[Quarter] = 1 THEN sbq.[Q4_to_Q1_Change] END) AS [4 to 1],
    SUM(CASE WHEN sbq.[Quarter] = 2 THEN sbq.[Quarterly Sales] END) AS [Q2],
    SUM(CASE WHEN sbq.[Quarter] = 2 THEN (sbq.[Quarterly Sales]/asl.[Total Annual Sales]*100) END) as [Q2 Annual %],
    SUM(CASE WHEN sbq.[Quarter] = 2 THEN sbq.[IntraYear Change] END) AS [1 to 2],
    SUM(CASE WHEN sbq.[Quarter] = 3 THEN sbq.[Quarterly Sales] END) AS [Q3],
    SUM(CASE WHEN sbq.[Quarter] = 3 THEN (sbq.[Quarterly Sales]/asl.[Total Annual Sales]*100) END) as [Q3 Annual %],
    SUM(CASE WHEN sbq.[Quarter] = 3 THEN sbq.[IntraYear Change] END) AS [2 to 3],
    SUM(CASE WHEN sbq.[Quarter] = 4 THEN sbq.[Quarterly Sales] END) AS [Q4],
    SUM(CASE WHEN sbq.[Quarter] = 4 THEN (sbq.[Quarterly Sales]/asl.[Total Annual Sales]*100) END) as [Q4 Annual %],
    SUM(CASE WHEN sbq.[Quarter] = 4 THEN sbq.[IntraYear Change] END) AS [3 to 4],
    asl.[Total Annual Sales]
FROM Sales_By_Quarter sbq
JOIN Annual_Sales asl ON sbq.[Year] = asl.[Year]
GROUP BY sbq.[Year], asl.[Total Annual Sales], asl.[Annual Growth]
ORDER BY sbq.[Year];

关键调整说明 #

1. 在Sales_By_Quarter中：
   • 将原Change列重命名为IntraYear Change，明确其为同年度内的季度变化
   • 新增Q4_to_Q1_Change列，仅在Q1时计算：通过去掉PARTITION BY YEAR的LAG函数，获取上一个季度（即上一年Q4）的销售额，计算与当前Q1的差值
2. 在主查询中：
   • 将「4 to 1」列替换为跨年度差值计算逻辑
   • 为每个季度的「Annual %」列添加季度标识，避免列名重复导致的潜在问题

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