英文:
Transposing quarters as row to column using BigQuery
问题
使用BigQuery是否可以将7个季度转换为列？
我的数据：
期望的输出：
我使用的方法：
- Offset子句
选择名称，
SUM(CASE WHEN QuarterEnding = 'Q1, 2020' THEN AT END) Q12020，
SUM(CASE WHEN QuarterEnding = 'Q2, 2020' THEN AT END) Q22020，
SUM(CASE WHEN QuarterEnding = 'Q3, 2020' THEN AT END) Q32020，
SUM(CASE WHEN QuarterEnding = 'Q4, 2020' THEN AT END) Q42020，
从`table1`
按1组。
英文:
I have data that contains Quarters as rows.
Is it possible to convert 7 Quarters into columns using BigQuery?
My data :
Desired output :
Methods used by me :
- Offset clause
Select Name,
SUM(CASE WHEN QuarterEnding ='Q1, 2020' THEN AT END) Q12020,
SUM(CASE WHEN QuarterEnding='Q2, 2020' THEN AT END) Q22020,
SUM(CASE WHEN QuarterEnding='Q3, 2020' THEN AT END) Q32020,
SUM(CASE WHEN QuarterEnding='Q4, 2020' THEN AT END) Q42020,
FROM `table1`
GROUP by 1;
答案1
得分: 2
SELECT * FROM (
SELECT * EXCEPT(City) FROM table1
) PIVOT (
SUM(AT) FOR TRANSLATE(QuarterEnding, '', ', '''')
IN ('Q12020', 'Q22020', 'Q32020', 'Q42020',
'Q12021', 'Q22021', 'Q32021', 'Q42021')
);
英文:
As @Pi-R mentioned, example query in the documentation is very close to what you want but for your specific desired output, you might consider below.
SELECT * FROM (
SELECT * EXCEPT(City) FROM `table1`
) PIVOT (
SUM(`AT`) FOR TRANSLATE(QuarterEnding, ', ', '')
IN ('Q12020', 'Q22020', 'Q32020', 'Q42020',
'Q12021', 'Q22021', 'Q32021', 'Q42021')
);
答案2
得分: 1
你可以查看 BigQuery 的透视功能 https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator。这里有与你的用例相同的情况
首先，你需要重命名季度的名称，以便它们可以成为字段。
Q1, 2020 => Q12020
然后，你可以使用
SELECT * FROM `table_name`
PIVOT(min(ID) FOR QuarterEnding IN ('Q12020', 'Q22020', 'Q32020', 'Q42020','Q12021', 'Q22021', 'Q32021', 'Q42021'))
英文:
You can look at pivot function of BigQuery https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator. There is the same use case as yours
First you have to rename quarter's name so they can be a field.
Q1, 2020 => Q12020
Then you can use
SELECT * FROM `table_name`
PIVOT(min(ID) FOR QuarterEnding IN ('Q12020', 'Q22020', 'Q32020', 'Q42020','Q12021', 'Q22021', 'Q32021', 'Q42021'))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论