在Amazon Athena中使用窗口函数计算百分位数的技术问题

需要生成字段展示产品两个时间戳之间天数的百分位数，该功能已在Amazon Quicksight实现，但受Quicksight聚合限制，结果无法用于其他计算字段，因此需直接在Athena用SQL实现，尝试的SQL语句报错。

尝试过的SQL语句 #

approx_percentile(case when (date_diff('millisecond', b.zp8_ts,b.depein_ts) / (1E3 *3600 *24)<0)OR (date_diff('millisecond', b.zp8_ts,b.depein_ts) / (1E3 *3600 *24) >200) then null else date_diff('millisecond', b.zp8_ts,b.depein_ts) / (1E3 *3600 *24)end,20) OVER (werkziel) 

case when (date_diff('millisecond', b.vhfein_ts,b.vhfaus_ts) / (1E3 *3600 *24)<0)OR (date_diff('millisecond', b.vhfein_ts,b.vhfaus_ts) / (1E3 *3600 *24) >200) then null else Percentile_count(date_diff('millisecond', b.vhfein_ts,b.vhfaus_ts) / (1E3 *3600 *24)) OVER (PARTITION BY concat(c.pk_nr_werk_num2,c.zielort_id),20) end 

Amazon Quicksight中可行的计算字段 #

percentileCont({zp8_depein_90},20,[werkziel])

样本数据 #

修正后的SQL方案 #

你的SQL存在语法和函数使用错误，以下是对齐需求的正确实现：

1. 对应Quicksight的percentileCont计算（zp8_ts与depein_ts） #

使用percentile_cont可与Quicksight的percentileCont完全对齐，计算连续型百分位数：

SELECT
  zp8_ts,
  depein_ts,
  pk_num_werk_num2,
  zielort_id,
  -- 计算20分位数，按werkziel分区
  percentile_cont(0.2)
  WITHIN GROUP (ORDER BY 
    CASE 
      WHEN date_diff('day', zp8_ts, depein_ts) BETWEEN 0 AND 200 
      THEN date_diff('day', zp8_ts, depein_ts) 
      ELSE NULL 
    END
  ) OVER (PARTITION BY werkziel) AS zp8_depein_20th_percentile
FROM your_table_name

若追求查询性能，可使用近似百分位数函数approx_percentile：

SELECT
  zp8_ts,
  depein_ts,
  pk_num_werk_num2,
  zielort_id,
  approx_percentile(
    CASE 
      WHEN date_diff('day', zp8_ts, depein_ts) BETWEEN 0 AND 200 
      THEN date_diff('day', zp8_ts, depein_ts) 
      ELSE NULL 
    END,
    0.2
  ) OVER (PARTITION BY werkziel) AS zp8_depein_20th_percentile
FROM your_table_name

2. 计算vhfein_ts与vhfaus_ts的间隔百分位数 #

SELECT
  vhfein_ts,
  vhfaus_ts,
  pk_num_werk_num2,
  zielort_id,
  percentile_cont(0.2)
  WITHIN GROUP (ORDER BY 
    CASE 
      WHEN date_diff('day', vhfein_ts, vhfaus_ts) BETWEEN 0 AND 200 
      THEN date_diff('day', vhfein_ts, vhfaus_ts) 
      ELSE NULL 
    END
  ) OVER (PARTITION BY pk_num_werk_num2, zielort_id) AS vhf_interval_20th_percentile
FROM your_table_name

关键修正说明 #

• 参数格式错误：Athena的百分位函数接收0到1之间的小数作为百分位参数（20分位数对应0.2），而非整数20
• 窗口子句错误：百分位参数属于函数参数，不能放在OVER()子句中
• 计算优化：直接用date_diff('day', ...)计算天数，避免毫秒转天数的冗余运算
• 函数兼容性：Athena不支持Percentile_count，使用percentile_cont（精确连续百分位）或approx_percentile（近似百分位，性能更优）替代

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