如何在BigQuery表中基于条件生成包含对应列名的数组类型新列

方案1：创建视图（推荐，无需修改原表结构） #

直接创建视图即可得到带Array_col的结果，无需修改原Users表，后续原表数据更新视图也会自动同步：

CREATE OR REPLACE VIEW `myproject.mydataset.users_with_color_array` AS
SELECT
  *,
  -- 构造符合条件的颜色列名数组
  ARRAY(
    SELECT color
    FROM UNNEST([
      IF(Red = 1, 'Red', NULL),
      IF(Blue = 1, 'Blue', NULL),
      IF(Green = 1, 'Green', NULL),
      IF(Yellow = 1, 'Yellow', NULL)
    ]) AS color
    WHERE color IS NOT NULL
  ) AS Array_col
FROM `myproject.mydataset.users`

如果需要将数组转为逗号分隔的字符串展示，可以用ARRAY_TO_STRING函数包裹上面的数组逻辑：
ARRAY_TO_STRING(上述数组逻辑, ', ') AS Array_col

方案2：直接给原表新增持久化列 #

如果需要把Array_col永久存储在原表中，执行以下两步操作：

1. 新增Array_col列，类型为字符串数组

ALTER TABLE `myproject.mydataset.users`
ADD COLUMN Array_col ARRAY<STRING>

2. 全表更新给Array_col赋值

UPDATE `myproject.mydataset.users`
SET Array_col = ARRAY(
    SELECT color
    FROM UNNEST([
      IF(Red = 1, 'Red', NULL),
      IF(Blue = 1, 'Blue', NULL),
      IF(Green = 1, 'Green', NULL),
      IF(Yellow = 1, 'Yellow', NULL)
    ]) AS color
    WHERE color IS NOT NULL
  )
WHERE 1=1 -- 匹配全表所有行

批量生成列判断逻辑（颜色列较多时使用） #

如果颜色列数量多，不想手动逐个写IF判断，可以用你之前查询系统表的方法自动生成判断片段：

SELECT STRING_AGG("IF(" || column_name || "=1, '" || column_name || "', NULL)", ", ")
FROM `myproject.mydataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'users'
-- 过滤出所有颜色类列，避免带上User、其他不相关列
AND column_name IN ('Red','Blue','Green','Yellow')

执行后将结果复制替换上面方案中UNNEST里的内容即可。

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