必须用窗口函数+CTE拆解过滤与计算逻辑，因嵌套子查询中PERCENTILE_CONT不支持标量调用、易致基准错位及性能雪崩；CTE先统一过滤再分组计算，保障上下文一致。
直接用嵌套子查询算分位数，在多条件过滤下极易出错——不是结果不准，就是报错或性能崩掉；必须用窗口函数 + CTE 拆解过滤与计算逻辑。
为什么不能在 WHERE 里套 PERCENTILE_CONT 子查询
常见错误是写成 SELECT * FROM t WHERE score > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY score) FROM t WHERE status = 'active')。问题有三：
- 子查询里的
WHERE status = 'active'和外层查询的过滤条件不一致，导致“分位数基准”和“筛选基准”错位 -
PERCENTILE_CONT是窗口函数，不是标量聚合函数，多数数据库（PostgreSQL/SQL Server）不允许在子查询中直接用它返回单值——会报ERROR: aggregate function calls cannot contain window function calls - 如果外层再加
GROUP BY或JOIN，子查询会被重复执行，性能雪崩
用 CTE 先过滤再算分位数，确保上下文一致
把“条件过滤”和“分位数计算”拆成两步，中间结果具名、复用、可调试：
WITH filtered AS (
SELECT id, score, dept
FROM scores
WHERE status = 'active' AND created_at >= '2026-01-01'
),
dept_p90 AS (
SELECT dept,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY score) AS p90_score
FROM filtered
GROUP BY dept
)
SELECT f.*, d.p90_score
FROM filtered f
JOIN dept_p90 d ON f.dept = d.dept
WHERE f.score > d.p90_score;关键点：
-
filteredCTE 统一承载所有业务过滤条件，后续所有计算都基于它，避免重复写WHERE -
dept_p90在干净数据上按部门算 90 分位，PERCENTILE_CONT的WITHIN GROUP才能正确生效 - 最后 JOIN + WHERE 是“用分位数筛原始行”，保留明细，不丢失上下文
MySQL 用户注意：没有 PERCENTILE_CONT，得用变量或 LIMIT/OFFSET 模拟
MySQL 8.0+ 仍不支持标准 PERCENTILE_CONT，硬套子查询会失败。可行替代方案：
- 用
ROW_NUMBER()+ 总数算位置：先在 CTE 中给每组排序编号，再用CEIL(0.9 * COUNT(*))找目标行号，最后JOIN取值 - 避免在子查询里嵌套
ORDER BY ... LIMIT 1 OFFSET N—— MySQL 优化器常误判为不可下推，全表扫描 - 如果只是近似分位，可用
APPROX_PERCENTILE（ClickHouse）或PERCENTILE_DISC（部分兼容版），但语义不同，务必验证业务是否接受离散近似
多条件组合时，NULL 和空组容易被忽略
当过滤条件含 AND category IN (...) 或 dept IS NOT NULL，可能使某组无数据，导致 dept_p90 CTE 缺失该部门——JOIN 后直接丢行。解决办法：
- 把
dept_p90改成左连接：LEFT JOIN dept_p90 d ON f.dept = d.dept，再用WHERE d.p90_score IS NOT NULL显式控制 - 对空组补默认值：
COALESCE(d.p90_score, 0)，但需确认 0 是否业务合理 - 在
filteredCTE 末尾加HAVING COUNT(*) > 10（如果分位数要求最小样本量），提前排除无效组
最麻烦的不是语法，而是“过滤条件写在哪一层”——写错一层，分位数就变成另一个世界的数字。别图省事堆子查询，CTE 的括号再多，也比查三天才发现基准数据不对强。
