SQL Server中PERCENTILE_DISC结合GROUP BY报错求助

错误原因 #

你遇到的报错核心是窗口函数与GROUP BY的语法冲突。PERCENTILE_DISC是窗口函数，通过OVER(PARTITION BY Grade)已经完成了按Grade的分组计算，每个Grade对应的分位数会重复出现在原表的每一行中。而GROUP BY要求SELECT列表中的所有列要么在GROUP BY子句中，要么被聚合函数包裹，但窗口函数不属于这两类，因此触发报错。

解决方案 #

以下两种方法都能实现按Grade唯一展示分位数的需求：

方法一：用DISTINCT直接去重 #

因为同一Grade的所有行对应的分位数结果完全一致，只需在SELECT前添加DISTINCT，就能保留每个Grade的唯一分位数记录，无需GROUP BY：

SELECT DISTINCT Grade,
  PERCENTILE_DISC(0.1) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS '10th_Percentile',
  PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS '25th_Percentile',
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS '50th_Percentile',
  PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS '75th_Percentile',
  PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS '90th_Percentile'
FROM Employees_Rank;

方法二：子查询+聚合函数配合GROUP BY #

先通过子查询计算出每一行对应的分位数，再在外层按Grade分组，用MAX（或MIN，因为同一Grade的分位数相同，聚合结果不影响）包裹分位数列，满足GROUP BY的语法要求：

SELECT Grade,
  MAX(10th_Percentile) AS 10th_Percentile,
  MAX(25th_Percentile) AS 25th_Percentile,
  MAX(50th_Percentile) AS 50th_Percentile,
  MAX(75th_Percentile) AS 75th_Percentile,
  MAX(90th_Percentile) AS 90th_Percentile
FROM (
  SELECT Grade,
    PERCENTILE_DISC(0.1) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS 10th_Percentile,
    PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS 25th_Percentile,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS 50th_Percentile,
    PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS 75th_Percentile,
    PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY Salary) OVER(PARTITION BY Grade) AS 90th_Percentile
  FROM Employees_Rank
) AS SubQuery
GROUP BY Grade;

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