窗口函数求和却报非聚合/分组列错误的SQL问题排查

问题描述 #

执行给定SQL时触发错误：Column '#temp.UsedSlots' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause，尽管UsedSlots已在窗口函数的SUM中使用。若将UsedSlots加入GROUP BY，会生成重复行的错误结果，无法实现**按合同年度（ContractYear）、预约类型（Category）分组的UsedSlots累计求和（运行总计）**的预期目标。

原SQL代码 #

SELECT AppointmentMethod AS Category
      ,MonthStart           AS EventDate
      ,UsedSlots
      ,CASE
           WHEN MonthStart
                BETWEEN '01-APR-2022' AND '31-MAR-2023' THEN '2022-2023'
           WHEN MonthStart
                BETWEEN '01-APR-2023' AND '31-MAR-2024' THEN '2023-2024'
       END                  AS ContractYear
INTO #temp
FROM [Reports].[Service].[Slots]
WHERE SlotType LIKE '%AMR%'
      AND AppointmentMethod IN ('Home Visit', 'Face-to-face', 'Group')
      AND MonthStart >= '01-APR-2022'

SELECT Category
      ,EventDate
      ,sum(UsedSlots) OVER (PARTITION BY ContractYear
                            ORDER BY eventdate
                            rows unbounded preceding
                           ) AS Numerator
      ,'351'                 AS Denominator
FROM #temp
GROUP BY contractyear
        ,Category
        ,EventDate

错误结果（加入UsedSlots到GROUP BY后） #

预期正确结果 #

错误原因 #

1. GROUP BY与窗口函数执行顺序冲突：SQL执行顺序中，GROUP BY先于窗口函数执行。原SQL中GROUP BY未包含UsedSlots，但SELECT直接引用了UsedSlots（窗口函数的SUM(UsedSlots)是对分组后的行计算，但分组阶段无法确定UsedSlots的取值规则，因此触发语法错误）。
2. 加入UsedSlots到GROUP BY的副作用：强行将UsedSlots加入GROUP BY会把每个独立的UsedSlots行作为单独分组，导致同一Category+EventDate出现重复行，窗口函数的累计求和变成逐行累加，而非按月份分组后的累计。

修正方案 #

正确逻辑需分两步：

1. 先按ContractYear+Category+EventDate分组，计算每个月份的UsedSlots总和；
2. 基于聚合后的结果，用窗口函数计算按合同年度、预约类型分区的累计求和。

方案1：临时表+子查询 #

-- 原临时表创建逻辑（建议将日期改为标准格式避免解析错误）
SELECT AppointmentMethod AS Category
      ,MonthStart           AS EventDate
      ,UsedSlots
      ,CASE
           WHEN MonthStart BETWEEN '2022-04-01' AND '2023-03-31' THEN '2022-2023'
           WHEN MonthStart BETWEEN '2023-04-01' AND '2024-03-31' THEN '2023-2024'
       END                  AS ContractYear
INTO #temp
FROM [Reports].[Service].[Slots]
WHERE SlotType LIKE '%AMR%'
      AND AppointmentMethod IN ('Home Visit', 'Face-to-face', 'Group')
      AND MonthStart >= '2022-04-01'

-- 先聚合月份总和，再计算累计求和
SELECT Category
      ,EventDate
      ,SUM(MonthlyTotal) OVER (
          PARTITION BY ContractYear, Category
          ORDER BY EventDate
          ROWS UNBOUNDED PRECEDING
      ) AS Numerator
      ,'351' AS Denominator
FROM (
    -- 第一步：按合同年度、预约类型、月份分组求和
    SELECT ContractYear
          ,Category
          ,EventDate
          ,SUM(UsedSlots) AS MonthlyTotal
    FROM #temp
    GROUP BY ContractYear, Category, EventDate
) AS GroupedData
ORDER BY ContractYear, EventDate, Category

方案2：CTE简化写法（无需临时表） #

WITH CTE_Slots AS (
    SELECT AppointmentMethod AS Category
          ,MonthStart AS EventDate
          ,UsedSlots
          ,CASE
               WHEN MonthStart BETWEEN '2022-04-01' AND '2023-03-31' THEN '2022-2023'
               WHEN MonthStart BETWEEN '2023-04-01' AND '2024-03-31' THEN '2023-2024'
           END AS ContractYear
    FROM [Reports].[Service].[Slots]
    WHERE SlotType LIKE '%AMR%'
          AND AppointmentMethod IN ('Home Visit', 'Face-to-face', 'Group')
          AND MonthStart >= '2022-04-01'
),
CTE_MonthlySum AS (
    SELECT ContractYear, Category, EventDate, SUM(UsedSlots) AS MonthlyTotal
    FROM CTE_Slots
    GROUP BY ContractYear, Category, EventDate
)
SELECT Category
      ,EventDate
      ,SUM(MonthlyTotal) OVER (
          PARTITION BY ContractYear, Category
          ORDER BY EventDate
          ROWS UNBOUNDED PRECEDING
      ) AS Numerator
      ,'351' AS Denominator
FROM CTE_MonthlySum
ORDER BY ContractYear, EventDate, Category

关键说明 #

• 日期格式改为YYYY-MM-DD标准格式，避免不同数据库语言环境下的日期解析错误；
• 窗口函数的PARTITION BY需同时包含ContractYear和Category，确保累计求和是按每个合同年度下的每个预约类型单独计算；
• 先聚合再做窗口函数，从根源消除重复行，保证累计求和的准确性。

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