自动从行生成列(无IF) [英] Generate columns from rows automatically (without IF)

问题描述

我的问题与此>如何使用BigQuery?.但是，请想象您有无法预料的行数，因此您无法在IF语句中列出它们. 那么，有什么方法可以从一列中获取DISTINCT值并从中创建列列表?

My question is similar to this How to simulate a pivot table with BigQuery?. But imagine that you have unpredicted amount of rows, so you can't list them in IF statement. So is there any way to take DISTINCT values from one column and create list of columns from it?

推荐答案

下面是一个非常简单的示例，用于显示方法:

Very simple example below just to show approach:

假设您有一个温度示例表:

Assume you have temp.example table:

select * from
(select '2015-08-01' as day, 1 as category, 11 as volume),
(select '2015-08-01' as day, 2 as category, 21 as volume),
(select '2015-08-01' as day, 3 as category, 31 as volume),
(select '2015-08-02' as day, 1 as category, 101 as volume),
(select '2015-08-02' as day, 2 as category, 201 as volume),
(select '2015-08-03' as day, 1 as category, 301 as volume),
(select '2015-08-03' as day, 2 as category, 302 as volume),
(select '2015-08-03' as day, 4 as category, 304 as volume)

，并假设您要构建如下所示的查询，但没有事先知道您有多少个不同的类别

and, assume you want to build query like below but w/o knowing in advance how many distinct categories you have

select 
   day,
   sum(if(category = 1, volume, 0)) as category_1,
   sum(if(category = 2, volume, 0)) as category_2,
   sum(if(category = 3, volume, 0)) as category_3,
   sum(if(category = 4, volume, 0)) as category_4
from temp.example
group by day
order by day

下面是完全执行此操作的GBQ代码

Below is GBQ code that does exactly this

select 'select day, ' + 
   group_concat_unquoted('sum(if(category = ' + string(category) + ', volume, 0)) as category_' + string(category)) 
   + ' from temp.example group by day order by day'
from (select category from temp.example group by category order by category)

此查询的输出是一个查询，如果您运行它，它将为您提供数据透视

Output of this query is a query that will produce pivot for you if you run it

day          category_1  category_2 category_3   category_4  
2015-08-01           11          21         31            0  
2015-08-02          101         201          0            0  
2015-08-03          301         302          0          304  

这篇关于自动从行生成列(无IF)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！