如何用 APPROX_PERCENTILE / PERCENTILE_CONT 计算近似分位数_网络技术

核心区别在于计算方式与适用场景：PERCENTILE_CONT 精确插值但易OOM超时，APPROX_PERCENTILE 基于sketch近似计算，快且省内存但有误差；如查1亿行95分位数，前者可能卡住，后者秒级返回。

简单说：PERCENTILE_CONT 是精确计算（基于排序后插值），APPROX_PERCENTILE 是近似计算（基于采样或 sketch 算法）。前者在大数据量下可能 OOM 或超时，后者快且省内存，但结果有误差范围。

典型场景：查 1 亿行用户停留时长的 95 分位数 —— 用 PERCENTILE_CONT 可能卡住，APPROX_PERCENTILE 几秒返回，误差通常

PERCENTILE_CONT 要求输入列可排序，且必须配合 OVER() 或分组使用（如 GROUP BY）
APPROX_PERCENTILE 多数引擎（Trino、Spark SQL、BigQuery）支持直接聚合，不强制排序，也无 OVER 限制
PostgreSQL 只有 PERCENTILE_CONT；ClickHouse 两者都有，但 APPROX_PERCENTILE 对应的是 quantile 系列函数

语法差异极大，抄错一个关键字就失败。重点看三类常见错误：

Trino：用 approx_percentile(x, 0.95)，不是 approx_percentile(x, 95)（必须是 0~1 小数）
BigQuery：用 APPROX_QUANTILES(x, 100)[OFFSET(95)] 或 APPROX_PERCENTILE(x, 0.95)（v1.4+ 支持后者）
Spark SQL：approx_percentile(col, 0.95)，但注意它不支持多分位数一次算；想同时要 0.5/0.9/0.95，得写三次或改用 approx_quantile
PostgreSQL：没有 APPROX_PERCENTILE，强行用会报 function approx_percentile does not exist；只能用 PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY x)

不是 bug，通常是数据或参数没对齐。常见原因：

输入列为全 NULL：多数引擎返回 NULL，APPROX_PERCENTILE 不做空值过滤，需显式加 WHERE x IS NOT NULL
百分位参数越界：传了 1.2 或 -0.1，Trino 报 percentile must be between 0 and 1，Spark 可能静默转成 0 或 1
内存不足触发降级：Trino 默认 sketch size 是 10k，超大数据倾斜时 sketch 失效，误差飙升甚至返回 null；可调大 approx_percentile.accuracy 配置
类型不匹配：对字符串列用 APPROX_PERCENTILE，报错类似 Unexpected type: varchar —— 它只接受数值型（DOUBLE、REAL、BIGINT）

如果查询跑了几分钟还不出结果，大概率是用了 PERCENTILE_CONT 处理千万级以上数据。它本质是全局排序 + 插值，shuffle 数据量极大。

确认执行计划：Trino 查 EXPLAIN (TYPE DISTRIBUTED)，看是否有 Sort + Window 节点；Spark 看 Spark UI 的 Stage 是否出现 long-running SortMergeJoin
临时替换测试：把 PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY x) 换成 APPROX_PERCENTILE(x, 0.95)，对比耗时
精度够用就别硬扛：95 分位数误差 ±0.3 秒对业务影响不大，但省下 90% 时间，这种 trade-off 很常见

真正难的是评估误差是否可接受——得拿小样本跑一遍 PERCENTILE_CONT 和 A

PPROX_PERCENTILE 对比，而不是凭感觉。