Trino中map_agg函数键过滤问题及高效解决方案咨询

问题分析 #

1. WHERE key IN报错原因：
   用WHERE key IN ('uni Amount', 'mrt Amount')过滤后，部分(id, metric_id)分组生成的map可能只包含其中一个键（甚至都不包含），外层直接用kv['xxx']访问不存在的键时会触发报错。

2. map_filter未成功的可能原因：
   你的写法本身逻辑没问题，但可能原表中键存在格式差异（比如多余空格、大小写不一致），或是Trino版本对lambda表达式的IN语法支持有限，改用OR条件即可解决。

最优解决方案（性能优先） #

优先用WHERE先过滤行再聚合，同时通过coalesce处理键不存在的情况，既避免报错又保证性能：

SELECT 
    id,
    metric_id,
    coalesce(kv['uni Amount'], 0) AS uni_amt, -- 键不存在时返回0，可根据需求修改默认值
    coalesce(kv['mrt Amount'], 0) AS mrt_amt
FROM (
    SELECT 
        id,
        metric_id,
        map_agg(key, value) kv
    FROM "table" -- table是关键字，建议加引号避免语法冲突
    WHERE key IN ('uni Amount', 'mrt Amount')
    GROUP BY id, metric_id
) t

备选方案（保留全部分组场景） #

如果需要保留所有(id, metric_id)分组（即使某些分组没有目标键），用map_filter确保只保留指定键，同时搭配coalesce处理访问问题：

SELECT 
    id,
    metric_id,
    coalesce(kv['uni Amount'], 0) AS uni_amt,
    coalesce(kv['mrt Amount'], 0) AS mrt_amt
FROM (
    SELECT 
        id,
        metric_id,
        map_filter(
            map_agg(key, value), 
            (k, v) -> k = 'uni Amount' OR k = 'mrt Amount'
        ) kv
    FROM "table"
    GROUP BY id, metric_id
) t

性能说明 #

• WHERE过滤是在聚合前减少数据量，性能远优于map_filter（聚合后再过滤map），所以能使用WHERE的场景优先选择。
• 用OR替代IN在lambda中不会影响性能，二者逻辑完全等价。

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