如何在BigQuery查询中为数组类型字段添加映射描述列？

针对你的需求，要给a.type数组中的每个值添加对应的描述，这里提供两种在BigQuery中实现的方式：

方法一：使用CASE WHEN逐个映射（适合少量映射） #

通过UNNEST展开数组元素，用CASE语句匹配映射关系后，再重新聚合为数组：

SELECT 
  timestamp_trunc(a.timestamp, day) date,
  a.custom_parameter1,
  a.custom_parameter2,
  a.score,
  a.type,
  -- 新增的映射描述列
  ARRAY(
    SELECT 
      CASE elem
        WHEN 23 THEN 'Description1'
        WHEN 24 THEN 'Description2'
        WHEN 25 THEN 'Description3'
        -- 继续添加剩余11组映射
        ELSE 'Unknown' -- 处理不在映射范围内的值
      END
    FROM UNNEST(a.type) elem
  ) AS type_descriptions,
  b.ref
FROM
  `data-views_batch_20221021` a
LEFT OUTER JOIN (
  SELECT 
    client_uuid,
    STRING_AGG(document_referrer, "," LIMIT 1) ref 
  FROM `activities_batch_20221021` 
  WHERE app_id="12345" AND document_referrer IS NOT NULL 
  GROUP BY client_uuid
) b USING (client_uuid)
WHERE
  a.app_id="12345"

方法二：使用MAP函数（适合多组映射，更简洁） #

利用BigQuery的MAP类型创建映射字典，再通过MAP_LOOKUP批量匹配数组元素，代码更简洁易维护：

SELECT 
  timestamp_trunc(a.timestamp, day) date,
  a.custom_parameter1,
  a.custom_parameter2,
  a.score,
  a.type,
  -- 新增的映射描述列
  ARRAY(
    SELECT 
      COALESCE(MAP_LOOKUP(mapping_map, elem), 'Unknown')
    FROM UNNEST(a.type) elem
  ) AS type_descriptions,
  b.ref
FROM
  `data-views_batch_20221021` a,
  -- 定义映射字典
  UNNEST([STRUCT(
    MAP(
      [23, 24, 25], -- 键列表
      ['Description1', 'Description2', 'Description3'] -- 值列表，顺序对应键
      -- 继续添加剩余11组键值对，比如 [26, 'Description4'], ...
    ) AS mapping_map
  )])
LEFT OUTER JOIN (
  SELECT 
    client_uuid,
    STRING_AGG(document_referrer, "," LIMIT 1) ref 
  FROM `activities_batch_20221021` 
  WHERE app_id="12345" AND document_referrer IS NOT NULL 
  GROUP BY client_uuid
) b USING (client_uuid)
WHERE
  a.app_id="12345"

注意事项 #

• 如果a.type数组中存在不在映射范围内的值，两种方法都用ELSE或COALESCE设置了默认值Unknown，可以根据需求调整。
• 方法二中的MAP键值对需要保证键和值的列表长度一致，顺序一一对应。

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