SQL Server中使用GROUPING SETS计算中位数的最优方法咨询

错误原因 #

PERCENTILE_CONT是窗口函数而非标准聚合函数，直接在GROUP BY GROUPING SETS后的SELECT列表中使用时，未指定与分组匹配的分区规则，SQL Server无法将其与分组关联，因此触发"value is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"错误。

解决方案 #

通过CTE（公共表表达式）先在原始数据层面按GROUPING SETS的分组维度计算中位数，再执行分组聚合，避免UNION拼接的冗余写法。核心思路是用GROUPING_ID或分组列组合作为窗口分区键，确保中位数计算对应每个分组集合，最后在聚合时取分组内的中位数（同一分组中位数一致，用MAX/MIN聚合即可）。

修改后的查询代码 #

WITH grouped_data AS (
    SELECT
        [type],
        [period],
        [area],
        [units],
        [value],
        -- 生成分组唯一标识，用于窗口分区
        GROUPING_ID([type], [period], [area]) AS group_id,
        -- 按分组标识+分组列分区，计算对应分组的中位数
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [value]) OVER (
            PARTITION BY GROUPING_ID([type], [period], [area]), [type], [period], [area]
        ) AS median_value
    FROM tb_source
)
SELECT
    CASE 
        WHEN [type] IS NOT NULL AND [period] IS NOT NULL AND [area] IS NOT NULL THEN 'LA by event type by month'
        WHEN [type] IS NOT NULL AND [period] IS NULL AND [area] IS NOT NULL THEN 'LA by event type any time'
        WHEN [type] IS NOT NULL AND [period] IS NOT NULL AND [area] IS NULL THEN 'England event type by month'
        WHEN [type] IS NOT NULL AND [period] IS NULL AND [area] IS NULL THEN 'England event type any time'
        ELSE 'CHECK GROUPING' END AS [grouping_type]
    ,[type]
    ,[period]
    ,[area]
    ,COUNT(*) AS [number_projects]
    ,SUM([units]) AS [total_units]
    ,AVG([value]) as [mean_value]
    ,MIN([value]) AS [min_value]
    ,MAX([value]) AS [max_value]
    ,MAX(median_value) AS median_value -- 同一分组中位数一致，取任意聚合值即可
FROM grouped_data
GROUP BY 
    GROUPING SETS (
        ([type])        
        ,([type], [period]) 
        ,([type], [area]) 
        ,([type], [period], [area])
        ,())

说明 #

• GROUPING_ID函数会为每个GROUPING SETS分组生成唯一的整数标识，确保窗口分区准确匹配每个分组集合。
• 若需要离散中位数，可将PERCENTILE_CONT(0.5)替换为PERCENTILE_DISC(0.5)，用法完全一致。
• 主查询中用MAX(median_value)是因为同一分组内的中位数完全相同，用MIN()或SQL Server 2022+支持的ANY_VALUE()均可。

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