如何用Athena Query将Server Timing JSON转换为日期聚合列

你当前的问题是cross join unnest会把数组中的每个元素拆成单独行，要得到预期的宽表格式，需要将拆分后的行重新聚合，或者使用Athena支持的PIVOT语法实现列转行。以下是两种可行方案：

方案一：条件聚合（通用SQL写法） #

先通过unnest展开数组，再按日期分组，用CASE WHEN提取对应指标的耗时：

with base as (
select
    datepart,
    cast(json_extract(attribution, '$.navigationEntry.serverTiming') as array<map<varchar,varchar>>) as serverTiming
from frontend.ui_stats_new
where datepart > to_iso8601(current_date - interval '4' day) 
  AND metricname ='TTFB' 
  AND page in ('searchPage') 
  and attribution is not null
limit 10
),
unnested as (
select 
    datepart,
    serverTiming_obj['name'] as metric_name,
    cast(serverTiming_obj['dur'] as integer) as duration
from base 
cross join unnest(serverTiming) as t(serverTiming_obj)
)
select 
    datepart,
    max(case when metric_name = 'agg' then duration end) as Agg,
    max(case when metric_name = 'react' then duration end) as React
from unnested
group by datepart
order by datepart desc;

方案二：使用Athena PIVOT语法（更简洁） #

Athena支持PIVOT操作，可直接将行数据转为列：

with base as (
select
    datepart,
    cast(json_extract(attribution, '$.navigationEntry.serverTiming') as array<map<varchar,varchar>>) as serverTiming
from frontend.ui_stats_new
where datepart > to_iso8601(current_date - interval '4' day) 
  AND metricname ='TTFB' 
  AND page in ('searchPage') 
  and attribution is not null
limit 10
),
unnested as (
select 
    datepart,
    serverTiming_obj['name'] as metric_name,
    cast(serverTiming_obj['dur'] as integer) as duration
from base 
cross join unnest(serverTiming) as t(serverTiming_obj)
)
select *
from unnested
pivot (
    max(duration)
    for metric_name in ('agg' as Agg, 'react' as React)
)
order by datepart desc;

注意事项 #

• 必须将dur字段转为数值类型（cast(...) as integer），避免按字符串处理导致聚合逻辑出错。
• 如果同一日期下同一指标存在多条记录，可根据需求替换max()为sum()或avg()来合并结果；若每个日期每个指标仅一条记录，max()即可满足需求。

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