SQL中怎样在单次扫描中同时计算多列不同的百分位数-SQL-PHP中文网

PostgreSQL 9.4+ 支持单次扫描多列百分位数，前提是所有 percentile_cont 共享相同 ORDER BY 和窗口定义；MySQL 需多次排序；ClickHouse 的 quantiles 函数可真单扫，但精度和插值有限制。

sql中怎样在单次扫描中同时计算多列不同的百分位数

PostgreSQL 的 `percentile_cont` 支持单次扫描多列百分位数

PostgreSQL 9.4+ 允许在同一个 SELECT 中对不同列调用多次 percentile_cont，只要它们共享相同的 GROUP BY 或窗口定义，查询引擎会复用排序结果，避免重复扫描。这是目前最接近“单次扫描”的标准方案。

关键点是：所有 percentile_cont 调用必须作用于同一 ORDER BY 子句（即使列不同），且不能混用不同排序方向或不同窗口帧；否则优化器可能退化为多次排序。

percentile_cont(0.5) WITHIN GROUP (ORDER BY sales_amount) 和 percentile_cont(0.9) WITHIN GROUP (ORDER BY profit_margin) 无法共用扫描——列不同、排序依据不同，会触发两次独立排序
正确写法是统一排序依据：比如都按 sales_amount 排，再分别求 profit_margin 在该顺序下的中位数（即 percentile_cont(0.5) WITHIN GROUP (ORDER BY sales_amount) 返回的是对应位置的 profit_margin 值，不是其自身分布的中位数）
若真要各自列的独立百分位数（如 sales_amount 的 P50 和 profit_margin 的 P90），PostgreSQL 仍需两次排序，但可通过 CTE 预先排序一次再引用，减少 I/O 开销

MySQL 8.0+ 必须用窗口函数模拟，无法真正单扫

MySQL 没有 percentile_cont，只能靠 ROW_NUMBER() + COUNT() 手动插值，而每个目标列都需要独立编号和计数，本质上仍是逻辑上多次遍历。

常见错误是试图在一个子查询里同时算两列的序号：

SELECT
  AVG(CASE WHEN rn_sales = mid THEN sales_amount END) AS p50_sales,
  AVG(CASE WHEN rn_margin = mid THEN profit_margin END) AS p50_margin
FROM (
  SELECT
    sales_amount,
    profit_margin,
    ROW_NUMBER() OVER (ORDER BY sales_amount) AS rn_sales,
    ROW_NUMBER() OVER (ORDER BY profit_margin) AS rn_margin,
    (COUNT(*) OVER () + 1) / 2 AS mid
  FROM orders
) t;

这段 SQL 看似“一次查”，但 MySQL 会为每个 OVER 子句执行一次全表排序，实际执行计划里出现多个 filesort。性能比显式两个子查询还差，因为无法并行利用索引。

若已有 (sales_amount) 和 (profit_margin) 单列索引，分开查反而更快
想压测真实开销？用 EXPLAIN FORMAT=TREE 看是否出现多个 Sort 节点

ClickHouse 的 `quantiles` 函数家族是少数真单扫方案

ClickHouse 的 quantiles(0.5, 0.9)(sales_amount) 和 quantiles(0.5, 0.9)(profit_margin) 可以合并在一个聚合中，底层使用 T-Digest 或 QDigest 合并流式摘要，仅扫描数据一次。

注意它返回的是数组，需展开：

SELECT
  quantiles(0.5, 0.9)(sales_amount) AS sales_q,
  quantiles(0.5, 0.9)(profit_margin) AS margin_q
FROM orders;
<p>-- 结果：[1200.0, 5600.0], [0.12, 0.38]</p>