如何在Trino中无需硬编码键计算Map类型列的平均值？

要实现无需硬编码键的查询，核心思路是先把Map列展开为键值对行，再分组计算平均值，具体步骤如下：

1. 基础实现：返回长表格式 #

通过UNNEST将每个Map拆成独立的键值对行，再按键分组计算平均值，自动覆盖所有出现过的键：

SELECT
    kv.key,
    AVG(kv.value) AS average_score
FROM (
    VALUES
        (map_from_entries(ARRAY[('x', 1), ('y', 1)])),
        (map_from_entries(ARRAY[('y', 2)])),
        (map_from_entries(ARRAY[('x', 3)]))
) AS t(action_to_score)
CROSS JOIN UNNEST(action_to_score) AS kv(key, value)
WHERE kv.value IS NOT NULL -- 过滤非空值，与原查询AVG行为一致
GROUP BY kv.key
ORDER BY kv.key;

执行结果：

key | average_score
-----|---------------
 x   | 2.0
 y   | 1.5

2. 进阶实现：返回宽表格式（与原查询结构一致） #

如果需要和原查询一样的宽表结构（每个键作为列），可以用PIVOT将行转成列。多数SQL引擎需要明确指定列名，若要完全动态生成列名，需结合动态SQL（比如Spark SQL、Trino可通过脚本先获取所有键，再拼接查询）：

以Trino/Presto为例的静态PIVOT查询（动态场景下可通过脚本生成IN子句的键列表）：

SELECT *
FROM (
    SELECT
        kv.key,
        kv.value
    FROM (
        VALUES
            (map_from_entries(ARRAY[('x', 1), ('y', 1)])),
            (map_from_entries(ARRAY[('y', 2)])),
            (map_from_entries(ARRAY[('x', 3)]))
    ) AS t(action_to_score)
    CROSS JOIN UNNEST(action_to_score) AS kv(key, value)
    WHERE kv.value IS NOT NULL
) AS source
PIVOT (
    AVG(value)
    FOR key IN ('x', 'y')
) AS pivoted;

执行结果与原查询完全一致：

x    y
----- -----
 2.0  1.5

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