自动从行生成列(无IF)

本文介绍了自动从行生成列(无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