BigQuery SQL One从长表到宽表的热编码 [英] BigQuery SQL one hot encode from a long to a wide table

问题描述

我有一个main表，如下所示：

id | name | age 
1    John   20
2    David  35
3    Alice  27

我有第二个表special，它编码1：M关系：

id | specialid 
1    1577
1    2868
2    9375
3    1309
3    5240
3    2346

如何连接和一个热编码specialtomain以生成如下表：

id | name | age | 1577 | 2868 | 9375 | 1309 | 5240 | 2346
1    John   20     1       1      0      0     0      0
2    David  35     0       0      1      0     0      0
3    Alice  27     0       0      0      1     1      1    

如果存在共享相同代码的其他人员，则显然应使用同一列：

id | name | age | 1577 | 2868 | 9375 | 1309 | 5240 | 2346 | 2223
1    John   20     1       1      0      0     0      0       0
2    David  35     0       0      1      0     0      0       0
3    Alice  27     0       0      0      1     1      1       0    
4    Sarah  32     0       1      0      1     1      0       1   

这是为了构建机器学习模型。在我的实际用例中，有7624个唯一的特殊代码。有没有办法自动为每个唯一的特殊代码构建一列？

推荐答案

在我的实际用例中，有7624个唯一的特殊代码。有没有办法自动为每个唯一的特殊代码构建一列？

下面是BigQuery标准SQL

#standardSQL
create temp table data as
select *
from `project.dataset.main`
left join `project.dataset.special`
using(id);

EXECUTE IMMEDIATE (
  SELECT """
  SELECT id, name, age, """ || 
    STRING_AGG("""MAX(IF(specialid = '""" || specialid || """', 1, 0)) AS _""" || REPLACE(specialid, '.', '_'), ', ') 
  || """
  FROM data 
  GROUP BY id, name, age
  """
  FROM (
    SELECT DISTINCT specialid 
    FROM data
    ORDER BY specialid
  )
);      

是否应用于问题输出中的样本数据

这篇关于BigQuery SQL One从长表到宽表的热编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！