使用BigQuery进行一位有效编码（虚拟变量）

Question

我想使用BigQuery代替Pandas来创建我的类别变量的虚拟变量（one-hot-encoding）。因为最终会有大约200列，所以我不能手动硬编码它。

测试数据集（实际数据集比这个多得多）。

WITH table AS (
SELECT 1001 as ID, 'blue' As Color, 'big' AS size UNION ALL
SELECT 1002 as ID, 'yellow' As Color, 'medium' AS size UNION ALL
SELECT 1003 as ID, 'red' As Color, 'small' AS size UNION ALL
SELECT 1004 as ID, 'blue' As Color, 'small' AS size)

SELECT *
FROM table

预期结果：

- Alex

Answer 1

以下内容适用于BigQuery标准SQL

DECLARE Colors, Sizes ARRAY<STRING>;

SET (Colors, Sizes) = (SELECT AS STRUCT ARRAY_AGG(DISTINCT Color), ARRAY_AGG(DISTINCT Size) FROM `project.dataset.table`);

EXECUTE IMMEDIATE '''
CREATE TEMP TABLE result AS  -- added line
SELECT ID, ''' || (
  SELECT STRING_AGG("COUNTIF(Color = '" || Color || "') AS Color_" || Color ORDER BY Color)
  FROM UNNEST(Colors) AS Color
) || (
  SELECT ', ' || STRING_AGG("COUNTIF(Size = '" || Size || "') AS Size_" || Size ORDER BY Size)
  FROM UNNEST(Sizes) AS Size
) || '''
FROM `project.dataset.table`
GROUP BY ID
ORDER BY ID
''';  -- added `;`

SELECT * FROM result;  -- added line   

如果应用于您问题中的样本数据-输出如下所示

- Mikhail Berlyant

---

谢谢。虽然它不返回表格，而是一些“作业”。我该如何将输出保存为BigQuery中的表格？ - Alex

当然，抱歉 - 我以为这个在需要的时候使用起来很明显。不过请查看我的答案更新。请注意添加了两行代码和一行更新代码（它们都有相应的注释，所以很容易识别）:o) - Mikhail Berlyant

抱歉，也许很明显但我之前没有使用EXECUTE IMMEDIATE。即使添加了你提供的新行，我仍然需要点击“查看结果”才能看到表格输出。我想自动保存独热编码数据集。 - Alex

为了自动保存结果，您可以在查询选项卡中更改设置以持久化到表格，请参考此文档。 - Yasser