在BigQuery中将行转置为列（透视实现）

Question

我想使用BigQuery生成一个新的表格，将所有键值对按照键作为列名、值作为它们相应的值放置。

例如：

**Key**                  **Value**
channel_title           Mahendra Guru    
youtube_id              ugEGMG4-MdA  
channel_id              UCiDKcjKocimAO1tV    
examId                  72975611-4a5e-11e5   
postId                  1189e340-b08f 

channel_title           Ab Live  
youtube_id              3TNbtTwLY0U  
channel_id              UCODeKM_D6JLf8jJt    
examId                  72975611-4a5e-11e5   
postId                  0c3e6590-afeb

我想把它转换成：

**channel_title   youtube_id   channel_id         examId               postId**
Mahendra Guru   ugEGMG4-MdA  UCiDKcjKocimAO1tV  72975611-4a5e-11e5   1189e340-b08f
Ab Live         3TNbtTwLY0U  UCODeKM_D6JLf8jJt  72975611-4a5e-11e5   0c3e6590-afeb

如何使用BigQuery进行操作？

- Prabhjot

---

现在您可以调用 fhoffa.x.pivot()，如此文章所述：https://medium.com/@hoffa/easy-pivot-in-bigquery-one-step-5a1f13c6c710 - Felipe Hoffa

Answer 1

BigQuery目前不支持数据透视函数
但是您仍然可以使用以下方法在BigQuery中完成此操作

首先，除了输入数据中的两列之外，您必须还有一列来指定需要合并为一个输出行的输入行组

因此，我假设您的输入表（yourTable）如下所示

**id**  **Key**                  **Value**
   1    channel_title           Mahendra Guru    
   1    youtube_id              ugEGMG4-MdA  
   1    channel_id              UCiDKcjKocimAO1tV    
   1    examId                  72975611-4a5e-11e5   
   1    postId                  1189e340-b08f 

   2    channel_title           Ab Live  
   2    youtube_id              3TNbtTwLY0U  
   2    channel_id              UCODeKM_D6JLf8jJt    
   2    examId                  72975611-4a5e-11e5   
   2    postId                  0c3e6590-afeb  

所以，首先您应该运行以下查询

SELECT 'SELECT id, ' + 
   GROUP_CONCAT_UNQUOTED(
      'MAX(IF(key = "' + key + '", value, NULL)) as [' + key + ']'
   ) 
   + ' FROM yourTable GROUP BY id ORDER BY id'
FROM (
  SELECT key 
  FROM yourTable
  GROUP BY key
  ORDER BY key
) 

上述查询的结果将是一个字符串，如果进行格式化，它将看起来像下面这样。

SELECT 
  id, 
  MAX(IF(key = "channel_id", value, NULL)) AS [channel_id],
  MAX(IF(key = "channel_title", value, NULL)) AS [channel_title],
  MAX(IF(key = "examId", value, NULL)) AS [examId],
  MAX(IF(key = "postId", value, NULL)) AS [postId],
  MAX(IF(key = "youtube_id", value, NULL)) AS [youtube_id] 
FROM yourTable 
GROUP BY id 
ORDER BY id

你现在应该复制上面的结果（注意：你不需要真正格式化它 - 我只是为了演示而这样做），然后像运行普通查询一样运行它。

结果将会如你所预期的那样。

id  channel_id          channel_title   examId              postId          youtube_id   
1   UCiDKcjKocimAO1tV   Mahendra Guru   72975611-4a5e-11e5  1189e340-b08f   ugEGMG4-MdA  
2   UCODeKM_D6JLf8jJt   Ab Live         72975611-4a5e-11e5  0c3e6590-afeb   3TNbtTwLY0U  

请注意：如果您能自己构建正确的查询（如第2步），并且字段数量较少且恒定，或者只需一次性处理，则可以跳过第1步。但是第1步只是帮助步骤，可以让您快速创建它，因此您随时都可以创建它！

如果您有兴趣，可以在我的其他帖子中了解更多关于数据透视表的内容。

如何在BigQuery中扩展数据透视表？
请注意 - 每个表格的列数限制为10K，因此您的组织数量受到了限制。
如果上述内容过于复杂/冗长，您还可以参考以下简化示例：
如何在BigQuery / SQL中将行转置为列，处理大量数据？
如何在Google BigQuery中为数千个类别创建虚拟变量列？
在BigQuery中旋转重复字段

- Mikhail Berlyant

---

注意：此答案适用于BigQuery Legacy SQL！请参阅https://stackoverflow.com/a/61530181/5221944，以获取适用于BigQuery Standard SQL的版本。 - Mikhail Berlyant

现在您可以调用 fhoffa.x.pivot()，如此文章所述：https://medium.com/@hoffa/easy-pivot-in-bigquery-one-step-5a1f13c6c710 - Felipe Hoffa