PERCENTILE_CONT()返回值与输入参数无关的SQL问题求助

这问题我之前帮别人排查过，核心是你把聚合函数（AVG、STD）和窗口函数（PERCENTILE_CONT）的用法混在一起了，导致逻辑冲突，才会出现不管传什么百分位参数结果都一样的情况。

问题根源分析 #

你写的SQL里先执行了GROUP BY col1, col2, col3，这一步会把原始数据按三个字段分组，每组只保留一行聚合后的结果（包含AVG(col4)和STD(col4)）。而后面的PERCENTILE_CONT用了窗口函数OVER (PARTITION BY col1, col2, col3)，它是作用在已经分组后的结果集上的——这时候每个分区里只有一行数据！

试想一下：只有一个数据点的时候，不管你计算5%、50%还是95%百分位数，结果必然是这个唯一的数据点本身，这就是为什么所有百分位返回值都相同的原因。另外，很多SQL引擎（比如PostgreSQL、MySQL严格模式）甚至会直接报错，因为你SELECT里的col4既没在GROUP BY里，也没被聚合函数包裹，属于非法引用。

两种正确的写法 #

写法一：保留原始明细行，用窗口函数做分组统计

如果你需要保留原始数据的每一行，同时附上对应分组的统计值，就把所有聚合函数都改成窗口函数，去掉GROUP BY：

SELECT 
    col1, 
    col2, 
    col3, 
    AVG(col4) OVER (PARTITION BY col1, col2, col3) AS avg_col4,
    STD(col4) OVER (PARTITION BY col1, col2, col3) AS std_col4,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY col4) OVER (PARTITION BY col1, col2, col3) AS 5th_percentile,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col4) OVER (PARTITION BY col1, col2, col3) AS 50th_percentile,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY col4) OVER (PARTITION BY col1, col2, col3) AS 95th_percentile
FROM table 
LIMIT 100;

这种写法会给每一行数据都带上其所在分组的均值、标准差和三个百分位数。

写法二：只返回分组汇总行，用聚合式PERCENTILE_CONT

如果你的需求是每个col1, col2, col3分组只返回一行汇总结果，那直接把PERCENTILE_CONT当成聚合函数用，去掉窗口函数的OVER子句：

SELECT 
    col1, 
    col2, 
    col3, 
    AVG(col4) AS avg_col4,
    STD(col4) AS std_col4,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY col4) AS 5th_percentile,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col4) AS 50th_percentile,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY col4) AS 95th_percentile
FROM table 
GROUP BY col1, col2, col3
LIMIT 100;

这应该是你最想要的效果：每个分组独立计算均值、标准差和三个不同的百分位数，结果不会再重复。

额外提示 #

如果你的SQL引擎支持PERCENTILE_DISC，可以根据需求选择：它返回原始数据中存在的数值，而PERCENTILE_CONT会返回插值后的结果，但两者的修正逻辑是一样的，按照上面的写法调整即可。

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