如何在Amazon Athena中按Sample_Number值重复行并计算速度百分位数？

原数据集 #

需求说明 #

需要将每行数据按「样本数量」列的值重复对应次数，之后计算每个Link编号对应小时段下的速度85百分位数。在PostgreSQL中已通过generate_series实现，SQL语句如下：

select Link_number, Hourband, percentile_cont(0.85) within group (order by Mean_Speed) as Speed_85th
from table, generate_series(1,Sample_Number)

Amazon Athena实现方案 #

Athena基于Presto引擎，不支持PostgreSQL中直接在FROM子句使用generate_series的写法，可以用UNNEST配合SEQUENCE函数实现行重复，完整SQL如下：

SELECT 
  Link_number, 
  Hourband, 
  percentile_cont(0.85) WITHIN GROUP (ORDER BY Mean_speed) AS Speed_85th
FROM 
  your_table_name, -- 替换为你的实际表名
  UNNEST(SEQUENCE(1, Sample_Number)) AS t
GROUP BY 
  Link_number, Hourband

关键说明 #

• SEQUENCE(1, Sample_Number)：生成从1到当前行「样本数量」的整数序列
• UNNEST(...)：将生成的序列展开为多行，等价于PostgreSQL中generate_series的行重复效果
• 后续分组计算百分位数的逻辑与PostgreSQL完全一致，通过GROUP BY指定分组维度，用percentile_cont计算85分位值

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