大查询转置 [英] Big Query Transpose

问题描述

我有一个包含以下列的Google Big Query表：

  date |用户| group |值
 ---------------------------- 
 date1 | user1 | group1 | 10 
 ---------------------------- 
 date1 | user2 | group1 | 5 
 ---------------------------- 
 date2 | user1 | group1 | 20 
 ---------------------------- 
 date2 | user2 | group1 | 10 
 --------------------------- 
等... 
  
 
 
 
现在我想将其转换为： 
 
 
  group | date1 | date2 
 ---------------------- 
 group1 | 15 | 30 
  
所以我想要每组每天的价值总和。我写了一个如下所示的查询： 
 
 
  SELECT date，group，value FROM [table] GROUP BY date，group，value 
  
但是我如何转置这个以便每个colums是一个日期并且每一行都是一个总数的集合对于这个值吗？
解决方案
在BigQuery中没有很好的做法，但您可以在下面执行想法
 
 
第1步
 
 
查询
  SELECT'SELECT [group]，'+ 
 GROUP_CONCAT_UNQUOTED（
'SUM（IF（[ （'date'，''''+ [date] +'，value，NULL））as [d_'+ REPLACE（[date]，'/'，'_'）+']'
）
 +'FROM YourTable GROUP BY [group] ORDER BY [group]'
 FROM（
 SELECT [date] FROM YourTable GROUP BY [date] ORDER BY [date] 
）
  
因此，您会得到如下的字符串（它被格式化为了便于阅读） 
 
 
  SELECT 
 [group]，
 SUM（IF（[date] = date1，value，NULL））AS [d_date1]，
 SUM（IF（[date] =date2，value，NULL））AS [d_date2] 
 FROM YourTable 
 GROUP BY [group] 
 ORDER BY [group] 
  
 
 
 
 
 
步骤2 
 
 
只需运行以上组合查询   结果如下
  group d_date1 d_date2 
 group1 15 30 
  
 
 
 
 
 
注1 
 
 
有许多团体可以进行太多的手动工作。
 
在这种情况下 - 第1步可以帮助您生成查询
 
 注意2  
 
以上步骤可轻松实现您选择的任何客户端，或者您可以在BigQiery Web UI 
 
 
呦您可以在我的其他帖子中看到更多关于摆动的信息。
 
 
 如何在BigQuery中缩放转轴？ 
 
请注意 - 每张表格有10K列的限制 - 如此您受到10K组织的限制。
 
您也可以在下面看到简化示例（如果上面的一个太复杂/冗长）：
 
  
 
  
 
 在BigQuery中重复显示字段 
 
I have a Google Big Query table with the following columns:
date  | user | group | value
----------------------------
date1 | user1 | group1 | 10
----------------------------
date1 | user2 | group1 | 5
----------------------------
date2 | user1 | group1 | 20
----------------------------
date2 | user2 | group1 | 10
---------------------------
etc...
Now I want to convert this to this:
group  | date1 | date2
----------------------
group1 | 15    | 30
So I want to have the sum of value for each day per group. I wrote a query that looks like this:
SELECT date, group, value FROM [table] GROUP BY date, group, value
But how do I transpose this so that each colums is a date and each row is a collection of totals for the value?
解决方案
There is no nice way of doing this in BigQuery as of yet, but you can do it following below idea

  
Step 1
Run below query  
SELECT 'SELECT [group], ' + 
   GROUP_CONCAT_UNQUOTED(
      'SUM(IF([date] = "' + [date] + '", value, NULL)) as [d_' + REPLACE([date], '/', '_') + ']'
   ) 
   + ' FROM YourTable GROUP BY [group] ORDER BY [group]'
FROM (
  SELECT [date] FROM YourTable GROUP BY [date] ORDER BY [date]
)
As a result - you will get string like below (it is formatted below for readability sake)
SELECT 
  [group], 
  SUM(IF([date] = "date1", value, NULL)) AS [d_date1],
  SUM(IF([date] = "date2", value, NULL)) AS [d_date2] 
FROM YourTable 
GROUP BY [group] 
ORDER BY [group]   



  
Step 2  
Just run above composed query  

Result will be like below
group   d_date1 d_date2  
group1  15      30      



  
Note 1
Step 1 is helpful if you have many groups to pivot so too much of manual work.

In this case - Step 1 helps you to generate your query

  
Note 2
Above steps are easily implemented in any client of your choice or you can just run those in BigQiery Web UI

  
You can see more about pivoting in my other posts.
How to scale Pivoting in BigQuery?

Please note – there is a limitation of 10K columns per table - so you are limited with 10K organizations.

You can also see below as simplified examples (if above one is too complex/verbose):

How to transpose rows to columns with large amount of the data in BigQuery/SQL?

How to create dummy variable columns for thousands of categories in Google BigQuery?

Pivot Repeated fields in BigQuery   

                        
这篇关于大查询转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！