SQL Server 2012中聚合函数与PERCENTILE_CONT联用问题求助

问题核心在于：聚合函数（MIN/MAX）与窗口函数（PERCENTILE_CONT）混合使用时，默认会返回与原表行数一致的结果，而你需要的是单行汇总数据。以下是两种可行的解决方案：

方法1：用GROUP BY (SELECT 1)强制单组聚合 #

通过将整个结果集视为一个分组，让聚合函数和窗口函数都作用于同一组，最终返回单行汇总：

SELECT 
    MIN(time_seconds) AS min,
    MAX(time_seconds) AS max,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY time_seconds) OVER () AS '95 percentil'
FROM your_table_name
GROUP BY (SELECT 1);

替换your_table_name为你的实际表名即可。

方法2：子查询分离计算逻辑 #

先分别计算聚合值和百分位数，再合并结果并去重：

SELECT
    agg.min,
    agg.max,
    pctl.'95 percentil'
FROM
    (SELECT MIN(time_seconds) AS min, MAX(time_seconds) AS max FROM your_table_name) agg
CROSS JOIN
    (SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY time_seconds) OVER () AS '95 percentil' FROM your_table_name) pctl
GROUP BY
    agg.min, agg.max, pctl.'95 percentil';

补充说明：针对你的示例数据，PERCENTILE_CONT(0.95)的精确计算结果为112.8（线性插值逻辑：(7-1)0.95+1=6.7，取第6位值96加上0.7(120-96)），若需要与你期望的110一致，可添加ROUND函数处理：

ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY time_seconds) OVER (), 0) AS '95 percentil'

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