如何在基于Glue Data Catalog的Amazon Athena中计算四分位数？还有哪些方法？

除了你已经尝试的percentile_cont（连续型百分位数，基于插值计算），Athena还支持以下几种计算四分位数、百分位数的方法：

1. 使用percentile_disc（离散型百分位数） #

这个函数返回数据集中实际存在的数值，而非插值结果，适合需要精确匹配原始数据的场景。

示例查询：

select
  percentile_disc(0.25) within group (order by duration) as percentile_25,
  percentile_disc(0.50) within group (order by duration) as percentile_50,
  percentile_disc(0.75) within group (order by duration) as percentile_75,
  percentile_disc(0.99) within group (order by duration) as percentile_99
from "schema"."table_name";

2. 使用NTILE分桶法 #

通过将数据均匀分桶，提取对应分位的边界值，适合需要直观划分数据区间的场景。注意：如果数据量无法被4整除，桶的大小会略有差异。

示例查询（以四分位数为例）：

with ranked_data as (
  select
    duration,
    ntile(4) over (order by duration) as quartile_bucket
  from "schema"."table_name"
)
select
  min(case when quartile_bucket = 1 then duration end) as min_q1,
  max(case when quartile_bucket = 1 then duration end) as percentile_25,
  max(case when quartile_bucket = 2 then duration end) as percentile_50,
  max(case when quartile_bucket = 3 then duration end) as percentile_75,
  max(case when quartile_bucket = 4 then duration end) as max_q4
from ranked_data;

3. 使用approx_percentile近似计算 #

针对超大规模数据集，这个函数通过近似算法提升查询性能，适合对精度要求不高但需要快速获取结果的场景。

示例查询：

select
  approx_percentile(duration, 0.25) as percentile_25,
  approx_percentile(duration, 0.50) as percentile_50,
  approx_percentile(duration, 0.75) as percentile_75,
  approx_percentile(duration, 0.99) as percentile_99
from "schema"."table_name";

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