如何用Trino SQL将FruitMarket表数据聚合成指定Map格式？

解决方案SQL #

WITH city_color_groups AS (
  SELECT
    fruit,
    city,
    array_distinct(array_agg(lower(color))) AS colors
  FROM FruitMarket
  GROUP BY fruit, city
)
SELECT
  fruit,
  map_from_entries(array_agg(row(city, colors))) AS fruitInfo
FROM city_color_groups
GROUP BY fruit;

步骤解释 #

1. 分组聚合城市对应颜色
   先通过子查询city_color_groups，按fruit和city双重分组：

   • 用array_agg(lower(color))收集当前水果+城市组合下的所有颜色，同时转成小写统一格式（匹配示例输出的大小写规范）
   • 用array_distinct对颜色数组去重，避免重复颜色值

2. 生成Map类型结果
   基于子查询的结果，再次按fruit分组：

   • 用array_agg(row(city, colors))把每个城市和对应的颜色数组打包成行元素，聚合成一个行数组
   • 用map_from_entries将行数组转换为Map类型，其中城市名是Map的key，颜色数组是对应的value

原SQL问题说明 #

你之前的SQL存在两个核心问题：

• 表中不存在nettingset字段，应该是fruit的笔误
• 没有按正确的层级分组，也没有构建Map所需的键值对结构，导致array_agg无法输出目标格式的结果

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