sql - 将表转换为多行的 one-hot 编码

Question

我有一个以下格式的 SQL 表:

ID  Cat
1   A   
1   B
1   D
1   F
2   B
2   C
2   D
3   A
3   F

现在，我想创建一个表，每行一个 ID，一行中有多个 Cat。我想要的输出如下所示:

ID  A  B  C  D  E  F
1   1  1  0  1  0  1
2   0  1  1  1  0  0
3   1  0  0  0  0  1

我已经找到:

Transform table to one-hot-encoding of single column value

但是，我有 1000 多个 Cat，所以我正在寻找自动编写它的代码，而不是手动编写。谁能帮我解决这个问题？

Answer 1

首先让我将您粘贴的数据转换为实际表格:

WITH data AS (
  SELECT REGEXP_EXTRACT(data2, '[0-9]') id, REGEXP_EXTRACT(data2, '[A-Z]') cat
  FROM (
    SELECT SPLIT("""1   A   
    1   B
    1   D
    1   F
    2   B
    2   C
    2   D
    3   A
    3   F""", '\n') AS data1
  ), UNNEST(data1) data2
)

SELECT * FROM data

(下次尝试共享一张 table )

现在我们可以做一些手动的 1-hot 编码:

SELECT id 
 , MAX(IF(cat='A',1,0)) cat_A
 , MAX(IF(cat='B',1,0)) cat_B
 , MAX(IF(cat='C',1,0)) cat_C
FROM data
GROUP BY id

现在我们要编写一个脚本来自动创建我们想要的列:

SELECT STRING_AGG(FORMAT("MAX(IF(cat='%s',1,0))cat_%s", cat, cat), ', ') 
FROM (
  SELECT DISTINCT cat
  FROM data
  ORDER BY 1
)

这会生成一个字符串，您可以将其复制粘贴到查询中，该字符串对您的数组/行进行 1-hot 编码:

SELECT id
,
MAX(IF(cat='A',1,0))cat_A, MAX(IF(cat='B',1,0))cat_B, MAX(IF(cat='C',1,0))cat_C, MAX(IF(cat='D',1,0))cat_D, MAX(IF(cat='F',1,0))cat_F
FROM data
GROUP BY id

这正是问题所要求的。您可以使用 SQL 生成 SQL，但您需要使用该结果编写新查询。