如何在Athena SQL统计视图中复用已计算的聚合列值？

原始数据 #

idx,year,month,day,metadata,not_impt,metricx
123,2022,12,02,"blah blah","lah lah",-123.94
123,2022,11,05,"blah blah asd","lah lah",62.4
123,2022,12,03,"blah blah asd","lah lah",39.512
123,2022,12,09,"blah blah","lah lah",12.412
123,2022,11,19,"blah blah","lah lah",24.43
123,2022,11,26,"blah blahac ","lah lah",94.94
987,2022,12,12,"blah blah","lah lah",-23.94
987,2022,11,15,"blah blahvs","lah lah",42.4
987,2022,11,03,"blah blah","lah lah",32.512
987,2022,12,04,"blah blah kams","lahada lah",19.412
987,2022,12,19,"blah blah","lah lah",21.43
987,2022,11,26,"blah blah","lah lah",74.94

需求说明 #

数据已导入Athena视图tablex，需创建新视图并计算以下统计值：

• 按idx、year和month分组
• avg_metric：每组metricx的平均值
• norm_metric：平均值的min-max归一化值
• errorbar_top_metric和errorbar_bottom_metric：95%置信区间的误差棒上下值

原始SQL（存在重复计算） #

CREATE VIEW AS
SELECT idx,
  concat(cast(year as varchar), '-', cast(month as varchar)) as date,
  count(*) as num_rows,
  AVG(metricx) as avg_metric,
  ((AVG(metricx) - MIN(metricx)) / (MAX(metricx) - MIN(metricx))) as norm_metric,
  
  (
    ((AVG(metricx) - MIN(metricx)) / (MAX(metricx) - MIN(metricx))) -
    (STDDEV_POP(metricx) / SQRT(count(*))) * 1.96
  ) as errorbar_bottom_metric,

  (
    ((AVG(metricx) - MIN(metricx)) / (MAX(metricx) - MIN(metricx))) +
    (STDDEV_POP(metricx) / SQRT(count(*))) * 1.96
  ) as errorbar_top_metric

FROM tablex
GROUP BY idx, year, month

优化写法（避免重复计算） #

数据量较小（少于10万行）时，可通过嵌套子查询或**CTE（公共表表达式）**先计算所有基础聚合值，再复用这些值计算衍生字段，避免重复调用聚合函数，让代码更简洁易维护。

方式1：嵌套子查询 #

CREATE VIEW AS
SELECT 
  idx,
  date,
  num_rows,
  avg_metric,
  norm_metric,
  norm_metric - ci_margin as errorbar_bottom_metric,
  norm_metric + ci_margin as errorbar_top_metric
FROM (
  SELECT 
    idx,
    concat(cast(year as varchar), '-', cast(month as varchar)) as date,
    count(*) as num_rows,
    AVG(metricx) as avg_metric,
    MIN(metricx) as min_metric,
    MAX(metricx) as max_metric,
    STDDEV_POP(metricx) as stddev_metric,
    -- 预计算归一化值
    ((AVG(metricx) - MIN(metricx)) / (MAX(metricx) - MIN(metricx))) as norm_metric,
    -- 预计算95%置信区间边际值
    (STDDEV_POP(metricx) / SQRT(count(*))) * 1.96 as ci_margin
  FROM tablex
  GROUP BY idx, year, month
) subquery

方式2：CTE（可读性更强） #

CREATE VIEW AS
WITH aggregated_data AS (
  SELECT 
    idx,
    concat(cast(year as varchar), '-', cast(month as varchar)) as date,
    count(*) as num_rows,
    AVG(metricx) as avg_metric,
    MIN(metricx) as min_metric,
    MAX(metricx) as max_metric,
    STDDEV_POP(metricx) as stddev_metric,
    ((AVG(metricx) - MIN(metricx)) / (MAX(metricx) - MIN(metricx))) as norm_metric,
    (STDDEV_POP(metricx) / SQRT(count(*))) * 1.96 as ci_margin
  FROM tablex
  GROUP BY idx, year, month
)
SELECT 
  idx,
  date,
  num_rows,
  avg_metric,
  norm_metric,
  norm_metric - ci_margin as errorbar_bottom_metric,
  norm_metric + ci_margin as errorbar_top_metric
FROM aggregated_data

优化说明 #

1. 先在子查询/CTE中一次性计算所有基础聚合值和中间衍生值，避免重复调用AVG()、MIN()、MAX()等函数
2. 外层查询直接复用预计算结果，代码结构更清晰，后续修改逻辑时只需调整一处
3. 小数据量下这种写法不会带来性能损耗，反而提升了代码的可维护性

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