在 BigQuery 中将行转置为列(Pivot 实现) [英] Transpose rows into columns in BigQuery (Pivot implementation)

问题描述

我想生成一个新表并使用 BigQuery 放置所有键值对，键作为列名，值作为各自的值.

I want to generate a new table and place all key value pairs with keys as column names and values as their respective values using 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 做到这一点?

How to do it using BigQuery?

推荐答案

BigQuery 尚不支持透视功能
您仍然可以使用以下方法在 BigQuery 中执行此操作

BigQuery does not support yet pivoting functions
You still can do this in BigQuery using below approach

但首先，除了输入数据中的两列之外，您还必须多列一列来指定输入中需要组合成一行的输入行组

But first, in addition to two columns in input data you must have one more column that would specify groups of rows in input that needs to be combined into one row in output

所以，我假设您的输入表 (yourTable) 如下所示

So, I assume your input table (yourTable) looks like below

**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  

所以，首先你应该运行下面的查询

So, first you should run below query

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
) 

上述查询的结果将是字符串(如果要格式化)将如下所示

Result of above query will be string that (if to format) will look like below

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

您现在应该复制上面的结果(注意:您实际上不需要对其进行格式化 - 我这样做只是为了演示)并将其作为正常查询运行

you should now copy above result (note: you don't really need to format it - i did it for presenting only) and run it as normal query

结果会如你所愿

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 步只是帮助您完成的步骤，因此您可以随时快速创建它！

Please note: you can skip Step 1 if you can construct proper query (as in step 2) by yourself and number of fields small and constant or if it is one time deal. But Step 1 just helper step that makes it for you, so you can create it fast any time!

如果你有兴趣 - 你可以在我的其他帖子中看到更多关于旋转的信息.

If you are interested - you can see more about pivoting in my other posts.

如何在 BigQuery 中扩展数据透视?
请注意 - 每个表有 10K 列的限制 - 因此您只能拥有 10K 个组织.
您还可以查看下面的简化示例(如果上面的示例过于复杂/冗长):
如何在 BigQuery/SQL 中将行转置为具有大量数据的列?
如何为 Google BigQuery 中的数千个类别创建虚拟变量列?
在 BigQuery 中透视重复字段

这篇关于在 BigQuery 中将行转置为列(Pivot 实现)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！