SQL中如何创建Map类型列 实现receiver ID与Quantity键值映射

场景说明 #

• 原表结构：
  
• 目标表要求：按非receiver、Quantity的维度分组，每组生成一个Map字段，以receiver ID为键、对应Quantity为值，目标表结构如下：
  

核心实现逻辑 #

核心分两步：

1. 按业务需要保留的维度字段分组，将同组下的receiver ID和Quantity组装为键值对结构体，聚合为结构体数组
2. 调用map_from_entries()函数将结构体数组转为Map类型

示例代码（以Spark SQL/Databricks为例） #

SELECT
  `Item ID`,
  `Item Name`,
  map_from_entries(
    collect_list(
      struct(`receiver ID` AS key, Quantity AS value)
    )
  ) AS `map`
FROM 你的原表名
-- GROUP BY 字段和SELECT中除聚合函数外的维度字段保持完全一致即可
GROUP BY `Item ID`, `Item Name`;

不同引擎适配说明 #

• Trino/Presto：将collect_list替换为array_agg，结构体需要显式声明类型，示例片段：

  map_from_entries(
    array_agg(
      cast(row("receiver ID", Quantity) as row(key varchar, value bigint))
    )
  ) as `map`
  

• BigQuery：可直接使用MAP_FROM_ENTRIES搭配ARRAY_AGG，示例片段：

  MAP_FROM_ENTRIES(
    ARRAY_AGG(STRUCT(`receiver ID` as key, Quantity as value))
  ) as `map`
  

• ClickHouse：不使用map_from_entries，直接用数组映射函数实现，示例片段：

  mapFromArrays(
    groupArray(`receiver ID`),
    groupArray(Quantity)
  ) as `map`
  

注意事项 #

• 如果同一分组下存在重复的receiver ID，生成Map时会自动覆盖重复键，仅保留最后一条匹配的值。如果需要避免数据丢失，可以提前按维度+receiver ID聚合Quantity（比如求和、取最大值）后再生成Map
• 如果需要Map内的键按固定顺序排列，可以在聚合前对数据排序，或者对聚合生成的结构体数组用排序函数处理后再转Map
• 业务场景更复杂时，只需要调整GROUP BY后的维度字段列表，保证SELECT里的非聚合字段和GROUP BY字段一一对应即可，核心Map生成逻辑不需要改动

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