AWS Athena无Pivot功能，如何转置聚合查询结果？

由于AWS Athena（基于Presto）原生不支持PIVOT语法，你可以通过UNION ALL + CASE WHEN的组合方式实现结果转置，具体方案如下：

实现思路 #

1. 先用CTE复用你原有的聚合查询结果，避免重复计算；
2. 对每个需要转置的数值列（sre、lacy、tiry、casu、day），分别生成一行记录，指定该行的维度名为原数值列名；
3. 用CASE WHEN匹配每个key值，提取对应维度的数值，通过MAX聚合过滤NULL值；
4. 最后用UNION ALL合并所有维度行，得到转置后的结构。

完整SQL代码 #

WITH aggregated_data AS (
    -- 复用你原有的聚合查询
    SELECT 
        "key",
        sum(coalesce(try_cast(sre as double), 0)) as sre,
        sum(coalesce(try_cast("lacy" as double), 0)) as "lacy",
        sum(coalesce(try_cast("tiry" as double), 0)) as "tiry",
        sum(coalesce(try_cast("casu" as double), 0)) as "casu",
        sum(coalesce(try_cast("day" as double), 0)) as "day"
    FROM table1 
    GROUP BY "key"
    HAVING "key" IN ('mars','posu','lest','post','cuti','demo')
)
-- 逐个处理每个数值列，转置为行
SELECT 
    'sre' AS metric,
    MAX(CASE WHEN "key" = 'mars' THEN sre END) AS mars,
    MAX(CASE WHEN "key" = 'posu' THEN sre END) AS posu,
    MAX(CASE WHEN "key" = 'lest' THEN sre END) AS lest,
    MAX(CASE WHEN "key" = 'post' THEN sre END) AS post,
    MAX(CASE WHEN "key" = 'cuti' THEN sre END) AS cuti,
    MAX(CASE WHEN "key" = 'demo' THEN sre END) AS demo
FROM aggregated_data
UNION ALL
SELECT 
    'lacy' AS metric,
    MAX(CASE WHEN "key" = 'mars' THEN "lacy" END) AS mars,
    MAX(CASE WHEN "key" = 'posu' THEN "lacy" END) AS posu,
    MAX(CASE WHEN "key" = 'lest' THEN "lacy" END) AS lest,
    MAX(CASE WHEN "key" = 'post' THEN "lacy" END) AS post,
    MAX(CASE WHEN "key" = 'cuti' THEN "lacy" END) AS cuti,
    MAX(CASE WHEN "key" = 'demo' THEN "lacy" END) AS demo
FROM aggregated_data
UNION ALL
SELECT 
    'tiry' AS metric,
    MAX(CASE WHEN "key" = 'mars' THEN tiry END) AS mars,
    MAX(CASE WHEN "key" = 'posu' THEN tiry END) AS posu,
    MAX(CASE WHEN "key" = 'lest' THEN tiry END) AS lest,
    MAX(CASE WHEN "key" = 'post' THEN tiry END) AS post,
    MAX(CASE WHEN "key" = 'cuti' THEN tiry END) AS cuti,
    MAX(CASE WHEN "key" = 'demo' THEN tiry END) AS demo
FROM aggregated_data
UNION ALL
SELECT 
    'casu' AS metric,
    MAX(CASE WHEN "key" = 'mars' THEN casu END) AS mars,
    MAX(CASE WHEN "key" = 'posu' THEN casu END) AS posu,
    MAX(CASE WHEN "key" = 'lest' THEN casu END) AS lest,
    MAX(CASE WHEN "key" = 'post' THEN casu END) AS post,
    MAX(CASE WHEN "key" = 'cuti' THEN casu END) AS cuti,
    MAX(CASE WHEN "key" = 'demo' THEN casu END) AS demo
FROM aggregated_data
UNION ALL
SELECT 
    'day' AS metric,
    MAX(CASE WHEN "key" = 'mars' THEN day END) AS mars,
    MAX(CASE WHEN "key" = 'posu' THEN day END) AS posu,
    MAX(CASE WHEN "key" = 'lest' THEN day END) AS lest,
    MAX(CASE WHEN "key" = 'post' THEN day END) AS post,
    MAX(CASE WHEN "key" = 'cuti' THEN day END) AS cuti,
    MAX(CASE WHEN "key" = 'demo' THEN day END) AS demo
FROM aggregated_data;

注意事项 #

• 若后续新增key值或数值列，需要手动更新SQL，添加对应的CASE WHEN分支或UNION ALL块；
• 使用MAX聚合是因为每个key对应唯一的聚合值，它可以自动排除CASE WHEN不匹配时返回的NULL值；
• 该方案是Athena不支持PIVOT时的通用替代方案，逻辑清晰且兼容性强。

内容的提问来源于stack exchange，提问作者Md. Parvez Alam