PERCENTILE_CONT函数与GROUP BY语句报错求助

嘿，我来帮你搞定这个SQL报错的问题～

首先得搞清楚错误的根源：你在同一个SELECT语句里同时用了聚合函数（AVG、STDEVP）和窗口函数（PERCENTILE_CONT），而且还加了GROUP BY。SQL的执行顺序里，GROUP BY是先把数据分组聚合，之后才会执行窗口函数。但你的写法里，窗口函数生成的Median列既不在GROUP BY里，也没有被聚合函数包裹，所以SQL引擎就会报错说它不符合GROUP BY的规则。

下面给你两种可行的解决方案：

方案一：先计算窗口函数，再聚合 #

先把所有需要的数据和中位数（窗口函数结果）提前计算好，再在外层做GROUP BY聚合均值和标准差。因为窗口函数是按ITEM_CODE分区的，同一个ITEM_CODE的所有行的中位数都是一样的，所以聚合时用MAX/Min就能把这个值保留下来：

WITH RawData AS (
    SELECT 
        ic.ITEM_CODE,
        vd.DATA1,
        -- 先按ITEM_CODE分区计算中位数
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY vd.DATA1) OVER (PARTITION BY ic.ITEM_CODE) AS Median
    FROM OC_VDATA vd
    INNER JOIN OC_VDAT_AUX vda 
        ON vd.PARTNO = vda.PARTNOAUX 
        AND vd.DATETIME = vda.DATETIMEAUX
    INNER JOIN stagingPLM.dbo.ITEM_CODES ic 
        ON LEFT(vd.PARTNO, 12) = ic.SPEC_NO 
        AND LEFT(vda.PARTNOAUX, 12) = ic.SPEC_NO
    WHERE 
        vda.UDL28 LIKE '%PLASTIC%' 
        AND RIGHT(vd.PARTNO, 6) = '036150' 
        AND CAST(vda.UDL40 AS DATETIME) BETWEEN '2019-05-18' AND '2022-05-18'
),
Q AS (
    SELECT 
        ITEM_CODE,
        AVG(DATA1) AS Mean,
        STDEVP(DATA1) AS StandardDev,
        -- 同ITEM_CODE的Median值一致，用MAX/Min都能拿到正确值
        MAX(Median) AS Median
    FROM RawData
    GROUP BY ITEM_CODE
)
SELECT * FROM Q;

方案二：分开计算聚合值和中位数，再关联 #

把聚合计算（均值、标准差）和中位数计算拆成两个CTE，最后通过ITEM_CODE关联起来，这样逻辑更清晰：

WITH AggregatedData AS (
    SELECT 
        ic.ITEM_CODE,
        AVG(vd.DATA1) AS Mean,
        STDEVP(vd.DATA1) AS StandardDev
    FROM OC_VDATA vd
    INNER JOIN OC_VDAT_AUX vda 
        ON vd.PARTNO = vda.PARTNOAUX 
        AND vd.DATETIME = vda.DATETIMEAUX
    INNER JOIN stagingPLM.dbo.ITEM_CODES ic 
        ON LEFT(vd.PARTNO, 12) = ic.SPEC_NO 
        AND LEFT(vda.PARTNOAUX, 12) = ic.SPEC_NO
    WHERE 
        vda.UDL28 LIKE '%PLASTIC%' 
        AND RIGHT(vd.PARTNO, 6) = '036150' 
        AND CAST(vda.UDL40 AS DATETIME) BETWEEN '2019-05-18' AND '2022-05-18'
    GROUP BY ic.ITEM_CODE
),
MedianData AS (
    SELECT DISTINCT
        ic.ITEM_CODE,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY vd.DATA1) OVER (PARTITION BY ic.ITEM_CODE) AS Median
    FROM OC_VDATA vd
    INNER JOIN OC_VDAT_AUX vda 
        ON vd.PARTNO = vda.PARTNOAUX 
        AND vd.DATETIME = vda.DATETIMEAUX
    INNER JOIN stagingPLM.dbo.ITEM_CODES ic 
        ON LEFT(vd.PARTNO, 12) = ic.SPEC_NO 
        AND LEFT(vda.PARTNOAUX, 12) = ic.SPEC_NO
    WHERE 
        vda.UDL28 LIKE '%PLASTIC%' 
        AND RIGHT(vd.PARTNO, 6) = '036150' 
        AND CAST(vda.UDL40 AS DATETIME) BETWEEN '2019-05-18' AND '2022-05-18'
)
SELECT 
    ad.ITEM_CODE,
    ad.Mean,
    ad.StandardDev,
    md.Median
FROM AggregatedData ad
INNER JOIN MedianData md ON ad.ITEM_CODE = md.ITEM_CODE;

为什么这两种方案可行？ #

• 方案一先让窗口函数跑完，给每个行都带上对应ITEM_CODE的中位数，之后GROUP BY时只需要聚合均值、标准差，中位数因为同组值相同，用MAX/Min就能正确提取。
• 方案二把两个逻辑完全分开，避免了聚合和窗口函数的冲突，最后关联得到完整结果。

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