使用Bigquery中的Select从行数据动态创建列 [英] Dynamically creating columns from row data using Select in Bigquery

问题描述

背景

我想在sql select语句中动态重命名case语句.

I want to rename my case statement in sql select statement dynamically.

例如:

SELECT (case when id= x.id then x.sums end) x.id as (select id,count(*) sums from table group by id) x

SELECT (case when id= x.id then x.sums end) x.id as (select id,count(*) sums from table group by id) x

我想要的输出是创建的列的列表，其中标签作为与"id"列不同的id.

what i want the output is list of columns created ,with Labels as distinct id's from "id" column.

但是，此变量 x.id 并不是动态输出值，而是我只输出了x.id列.

However,this variable x.id is not dynamically outputing values,rather i get output a single column x.id.

例如:

表格中的列...

1 ---- x1 --- x2

1----x1---x2

2 ---- x2 ---- x3

2----x2----x3

3 ---- x4 ---- x5

3----x4----x5

运行查询后需要的列...

columns expected after running query...

但实际的o/p列为::

but actual o/p column is::

查询 任何想法，如何使用选择查询动态生成列，如果我错了，请纠正我.

Query Any ideas,how to generate columns dynamically using select query,please correct me ,if i am wrong.

推荐答案

以下内容适用于BigQuery！

Below is for BigQuery!

请注意:您对输出列名称的期望不正确！
列名不能以数字开头-因此在下面的示例中-我将使用id_1，id_2和id_3而不是1、2和3

Please note: your expectations about output column names are not correct!
Column name cannot start with digit - so in below example - i will be using id_1, id_2 and id_3 instead of 1, 2 and 3

SELECT
  SUM(CASE WHEN id = 1 THEN 1 END) AS id_1,
  SUM(CASE WHEN id = 2 THEN 1 END) AS id_2,
  SUM(CASE WHEN id = 3 THEN 1 END) AS id_3
FROM YourTable

上面的示例假设您预先知道ID，并且ID很少，因此为每个ID手动写几行带有SUM(...)的行就没什么大不了了

Above example assumes you know in advance your IDs and there are very few of them so it is not a big deal to write manually few numbers of lines with SUM(...) for each id

如果不是这种情况，您可以先通过在以下查询中运行

If this is not a case - you can first generate above query programmatically by running below query

SELECT 'SELECT ' + 
   GROUP_CONCAT_UNQUOTED(
      'SUM(CASE WHEN id = ' + STRING(id) + ' THEN 1 END) AS id_' + STRING(id)
   ) 
   + ' FROM YourTable'
FROM (
  SELECT id FROM (
    SELECT * FROM YourTable GROUP BY id ORDER BY id
)

结果-您将获得类似于下面的字符串

as a result - you will get string like below

SELECT SUM(CASE WHEN id = 1 THEN 1 END) AS id_1,SUM(CASE WHEN id = 2 THEN 1 END) AS id_2,SUM(CASE WHEN id = 3 THEN 1 END) AS id_3 FROM YourTable

因此，现在只需将其复制并粘贴到查询编辑器中并运行它

So, now just copy it and paste into Query Editor and run it

您可以在此处看到类似的示例- https://stackoverflow.com/a/36623258/5221944

you can see similar example here - https://stackoverflow.com/a/36623258/5221944

这篇关于使用Bigquery中的Select从行数据动态创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！