BigQuery标准SQL：基于数组生成类别哑变量列的技术问询

嘿，这个需求我在BigQuery里实操过，给你分享个靠谱的方案～要把数组里的类别转成一个个哑变量列（比如Categories_1、Categories_44这类，用1/0标识用户是否包含对应类别），我们可以用动态SQL来实现，因为静态SQL没法提前知道所有可能的类别值。

具体实现步骤 #

假设你的表是your_dataset.your_table，完整代码如下：

DECLARE columns STRING;

-- 1. 提取所有唯一类别，生成对应的哑变量列定义
SET columns = (
  SELECT STRING_AGG(
    FORMAT('MAX(IF(%d IN UNNEST(Categories), 1, 0)) AS Categories_%d', category, category),
    ', '
  )
  FROM (
    SELECT DISTINCT category
    FROM `your_dataset.your_table`, UNNEST(Categories) AS category
    ORDER BY category
  )
);

-- 2. 执行动态SQL生成结果
EXECUTE IMMEDIATE FORMAT('
  SELECT UserID, %s
  FROM `your_dataset.your_table`
  GROUP BY UserID
', columns);

代码解释 #

• 第一步：通过UNNEST(Categories)把数组拆成单行的类别值，用DISTINCT拿到所有唯一类别，再用STRING_AGG把每个类别转换成对应的哑变量列语句（比如MAX(IF(1 IN UNNEST(Categories), 1, 0)) AS Categories_1）。
• 第二步：用EXECUTE IMMEDIATE执行动态拼接好的SQL，GROUP BY UserID确保每个用户只占一行，MAX函数用来标记：只要用户数组里有这个类别，就返回1，否则返回0。

示例输出 #

用你提供的样本数据执行后，会得到这样的结果：

注意事项 #

• 如果你的类别数量特别多（比如超过10000个），会触发BigQuery的列数上限，这种情况下建议重新评估是否真的需要生成所有哑变量，或者考虑其他存储/分析方式。
• 如果表数据量很大，可以在提取唯一类别时加上过滤条件（比如WHERE UserID BETWEEN ...），提升性能。
• 记得把代码里的your_dataset.your_table替换成你实际的数据集和表名。

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