如何用Presto SQL分组后生成动态字典列？

需求场景 #

输入原始数据：

id     timestamp          collection
1111    1721681210135     music
2222    1721681210140     weather
1111    1721681210100     art
1111    1721681210000     music
.........

需要按id分组后，生成记录各collection值出现次数的字典格式列，期望输出：

id      collection_counts
1111    "music":2,"art":1
2222    "weather":1
............

实现方案 #

可以使用Presto的**histogram函数**直接实现，该函数专门用于统计分组内指定列各值的出现次数，并返回键值对形式的map结构，完全匹配需求。

基础SQL示例 #

SELECT
    id,
    histogram(collection) AS collection_counts
FROM your_table_name
GROUP BY id;

执行后会返回带大括号的标准字典格式，比如{"music":2,"art":1}，如果需要去掉首尾的大括号，可通过字符串替换处理：

格式调整后的SQL示例 #

SELECT
    id,
    replace(replace(cast(histogram(collection) AS varchar), '{', ''), '}', '') AS collection_counts
FROM your_table_name
GROUP BY id;

该语句会输出你期望的"music":2,"art":1格式。

另外也可以使用map_agg函数实现，先统计每个id和collection的出现次数，再聚合为字典：

SELECT
    id,
    map_agg(collection, count) AS collection_counts
FROM (
    SELECT
        id,
        collection,
        COUNT(*) AS count
    FROM your_table_name
    GROUP BY id, collection
) t
GROUP BY id;

这种方式步骤稍多，但灵活性更强，适合需要自定义聚合逻辑的场景。

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