如何在BigQuery中批量实现多标签One-Hot编码（替代Pandas）

针对数组类型的分类字段（如Color、size），无需手动枚举所有标签，可通过BigQuery的动态SQL实现自动独热编码，效果等价于Pandas的MultilabelBinarizer。

解决方案代码 #

DECLARE sql STRING;

WITH table AS (
SELECT 1001 as ID, ['blue','green'] As Color, ['big'] AS size UNION ALL
SELECT 1002 as ID, ['green','yellow'] As Color, ['medium','large'] AS size UNION ALL
SELECT 1003 as ID, ['red'] As Color, ['big'] AS size UNION ALL
SELECT 1004 as ID, ['blue'] As Color, ['big'] AS size
),
all_labels AS (
  -- 展开所有数组字段的标签并标记来源字段
  SELECT 'Color' AS field, color AS label FROM table, UNNEST(Color) color
  UNION ALL
  SELECT 'size' AS field, size AS label FROM table, UNNEST(size) size
),
unique_labels AS (
  -- 获取每个字段的唯一标签集合
  SELECT DISTINCT field, label FROM all_labels
),
column_defs AS (
  -- 生成每个标签对应的哑变量列定义
  SELECT
    CONCAT(
      "MAX(CASE WHEN field = '", field, "' AND label = '", label, "' THEN 1 ELSE 0 END) AS ", field, "_", label
    ) AS column_def
  FROM unique_labels
)
SELECT
  STRING_AGG(column_def, ",\n  ") INTO sql
FROM column_defs;

-- 拼接完整SQL并执行
SET sql = CONCAT(
  "WITH table AS (
SELECT 1001 as ID, ['blue','green'] As Color, ['big'] AS size UNION ALL
SELECT 1002 as ID, ['green','yellow'] As Color, ['medium','large'] AS size UNION ALL
SELECT 1003 as ID, ['red'] As Color, ['big'] AS size UNION ALL
SELECT 1004 as ID, ['blue'] As Color, ['big'] AS size
),
all_labels AS (
  SELECT 'Color' AS field, color AS label FROM table, UNNEST(Color) color
  UNION ALL
  SELECT 'size' AS field, size AS label FROM table, UNNEST(size) size
),
unique_labels AS (
  SELECT DISTINCT field, label FROM all_labels
)
SELECT
  ID,\n  ",
  sql,
  "\nFROM
  table
CROSS JOIN
  unique_labels
GROUP BY
  ID
ORDER BY
  ID"
);

EXECUTE IMMEDIATE sql;

方案说明 #

1. 提取全量标签：通过UNNEST展开每个数组字段的元素，同时记录标签所属的字段，确保覆盖所有可能的分类值。
2. 去重得到唯一标签：对每个字段的标签去重，避免重复生成哑变量列。
3. 动态生成列逻辑：用STRING_AGG将每个标签对应的CASE WHEN表达式拼接成列定义字符串，自动适配任意数量的标签。
4. 执行动态SQL：将生成的列定义插入到主查询模板中，通过EXECUTE IMMEDIATE执行最终SQL，输出独热编码结果。

预期输出 #

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