如何在Amazon Athena中用PERCENT_RANK()计算指定分位数？

问题分析 #

你之前的两个尝试都存在用法问题：

• percentile_cont的错误写法：混淆了聚合函数和窗口函数的语法，WITHIN GROUP是聚合函数专属用法，不能和窗口子句OVER()混用。
• PERCENT_RANK()的误用：这个函数是计算每行在分组内的排名占比，并非直接返回分组的指定分位数值，所以得到的是0.83、0.8这类每行的相对值，不是你需要的25/75/95/99分位数。

正确解决方案 #

Athena基于Presto，支持两种常用的分位数计算方式，可根据需求选择：

1. 精确连续分位数（percentile_cont） #

使用聚合函数语法，结合GROUP BY按x,y分组，直接计算每个分组的指定分位值：

SELECT
  x,
  y,
  -- 25分位数
  percentile_cont(0.25) WITHIN GROUP (ORDER BY ID_count) AS p25,
  -- 50分位数（中位数）
  percentile_cont(0.5) WITHIN GROUP (ORDER BY ID_count) AS p50,
  -- 75分位数
  percentile_cont(0.75) WITHIN GROUP (ORDER BY ID_count) AS p75,
  -- 95分位数
  percentile_cont(0.95) WITHIN GROUP (ORDER BY ID_count) AS p95,
  -- 99分位数
  percentile_cont(0.99) WITHIN GROUP (ORDER BY ID_count) AS p99
FROM your_table
GROUP BY x, y;

该函数返回连续型分位值，适合需要精确插值的场景。

2. 近似离散分位数（适合大数据集） #

如果数据集极大、追求计算性能，可使用基于TDigest算法的近似分位数函数，结果为近似值但计算速度更快：

SELECT
  x,
  y,
  percentile(0.25, ID_count) AS p25,
  percentile(0.5, ID_count) AS p50,
  percentile(0.75, ID_count) AS p75,
  percentile(0.95, ID_count) AS p95,
  percentile(0.99, ID_count) AS p99
FROM your_table
GROUP BY x, y;

也可使用等价的approx_percentile函数：

SELECT
  x,
  y,
  approx_percentile(ID_count, 0.25) AS p25,
  approx_percentile(ID_count, 0.5) AS p50,
  approx_percentile(ID_count, 0.75) AS p75,
  approx_percentile(ID_count, 0.95) AS p95,
  approx_percentile(ID_count, 0.99) AS p99
FROM your_table
GROUP BY x, y;

补充：窗口函数形式的分位数（每行返回分组分位值） #

如果需要给每行重复返回对应分组的分位值，可使用窗口函数语法的percentile_cont（注意不要加WITHIN GROUP）：

SELECT
  *,
  percentile_cont(0.25) OVER (PARTITION BY x, y ORDER BY ID_count) AS p25_window
FROM your_table;

但这种写法会产生大量重复值，一般来说聚合+GROUP BY的方式更符合获取分组分位值的需求。

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