使用GROUP BY ROLLUP透视表数据 [英] Pivot table data using GROUP BY ROLLUP
本文介绍了使用GROUP BY ROLLUP透视表数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
[注意：这里可以使用任何支持ROLLUP的SQL数据库。我在示例中使用了BigQuery。]
我希望在Excel中获取以下数据透视表的所有数据：
应该有36个数据单元格。到目前为止，我拥有的BigQuery查询如下：
SELECT Year, Quarter, ProductGroup, Product, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ROLLUP(ProductGroup, Product, Year, Quarter)
union distinct
SELECT Year, Quarter, ProductGroup, Product, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ROLLUP(Year, Quarter, Product, ProductGroup)
但是，它只给出34个结果，而我希望得到36个结果。在此查询中提供所有数据透视表组合的正确方式是什么？此处附加数据：
$ cat > Products.csv
ProductGroup,Product,Year,Quarter,Revenue,Units,Count,Product Key,Reseller,Product Info,QuarterAsNumber
Electronics,Phone,2018,Q1,103,7,1,2018-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2018,Q1,102,4,1,2018-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2019,Q1,98,12,1,2019-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Computer,2018,Q1,104,3,1,2018-Q1,Samsung,Format=XML; <Properties>…,1
Electronics,Computer,2019,Q1,83,7,1,2019-Q1,Google,Format=XML; <Properties>…,1
Media,Theater,2018,Q1,17,4,1,2018-Q1,Sony,Format=XML; <Properties>…,1
Media,Theater,2019,Q1,20,7,1,2019-Q1,Sony,Format=XML; <Properties>…,1
Media,Movies,2018,Q1,25,12,1,2018-Q1,Microsoft,Format=XML; <Properties>…,1
Media,Movies,2019,Q1,26,13,1,2019-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2018,Q2,105,5,1,2018-Q2,Samsung,Format=XML; <Properties>…,2
Electronics,Phone,2019,Q2,82,15,1,2019-Q2,LG,Format=XML; <Properties>…,2
Electronics,Computer,2018,Q2,99,4,1,2018-Q2,LG,Format=XML; <Properties>…,2
Electronics,Computer,2019,Q2,84,20,1,2019-Q2,Sony,Format=XML; <Properties>…,2
Media,Theater,2018,Q2,17,4,1,2018-Q2,Microsoft,Format=XML; <Properties>…,2
Media,Theater,2019,Q2,22,5,1,2019-Q2,Sony,Format=XML; <Properties>…,2
Media,Movies,2018,Q2,25,12,1,2018-Q2,Samsung,Format=XML; <Properties>…,2
Media,Movies,2019,Q2,26,14,1,2019-Q2,Google,Format=XML; <Properties>…,2
Electronics,Phone,2000,Q1,103,7,1,2000-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2001,Q1,102,4,1,2001-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2002,Q1,98,12,1,2002-Q1,Microsoft,Format=XML; <Properties>…,1
Electronics,Computer,2003,Q1,104,3,1,2003-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Computer,2004,Q1,83,7,1,2004-Q1,Samsung,Format=XML; <Properties>…,1
Media,Theater,2005,Q1,17,4,1,2005-Q1,Google,Format=XML; <Properties>…,1
Media,Theater,2006,Q1,20,7,1,2006-Q1,Sony,Format=XML; <Properties>…,1
Media,Movies,2007,Q1,25,12,1,2007-Q1,Sony,Format=XML; <Properties>…,1
Media,Movies,2008,Q1,26,13,1,2008-Q1,Microsoft,Format=XML; <Properties>…,1
Electronics,Phone,2009,Q2,105,5,1,2009-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Phone,2010,Q2,82,15,1,2010-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Computer,2011,Q2,99,4,1,2011-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Computer,2012,Q2,84,20,1,2012-Q2,Sony,Format=XML; <Properties>…,2
Media,Theater,2013,Q2,17,4,1,2013-Q2,Sony,Format=XML; <Properties>…,2
Media,Theater,2014,Q2,22,5,1,2014-Q2,Sony,Format=XML; <Properties>…,2
Media,Movies,2015,Q2,25,12,1,2015-Q2,Sony,Format=XML; <Properties>…,2
Media,Movies,2016,Q2,26,14,1,2016-Q2,Samsung,Format=XML; <Properties>…,2
Media,Movies,2017,Q1,26,13,1,2017-Q1,Google,Format=XML; <Properties>…,1
Electronics,Phone,2018,Q2,105,5,1,2018-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Phone,2019,Q2,82,15,1,2019-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Computer,2020,Q2,99,4,1,2020-Q2,Microsoft,Format=XML; <Properties>…,2
Electronics,Phone,2020,Q1,103,7,1,2020-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2020,Q2,102,4,1,2020-Q2,Samsung,Format=XML; <Properties>…,2
Electronics,Phone,2020,Q3,98,12,1,2020-Q3,LG,Format=XML; <Properties>…,3
Electronics,Computer,2020,Q4,104,3,1,2020-Q4,LG,Format=XML; <Properties>…,4
Electronics,Computer,2020,Q1,83,7,1,2020-Q1,Sony,Format=XML; <Properties>…,1
Media,Theater,2020,Q1,17,4,1,2020-Q1,Microsoft,Format=XML; <Properties>…,1
Media,Theater,2020,Q1,20,7,1,2020-Q1,Sony,Format=XML; <Properties>…,1
以下是我要查找的内容的长格式：
-- >, ProductGroup>
SELECT ProductGroup, NULL, NULL, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ROLLUP (ProductGroup)
-- ProductGroup>Product
union distinct
SELECT ProductGroup, Product, NULL, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Product
-- ProductGroup>Product>Year
union distinct
SELECT ProductGroup, Product, Year, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Product, Year
-- ProductGroup>Year
union distinct
SELECT ProductGroup, NULL, Year, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Year
-- ProductGroup>Year>Quarter
union distinct
SELECT ProductGroup, NULL, Year, Quarter, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Year, Quarter
-- ProductGroup>Product>Year>Quarter
union distinct
SELECT ProductGroup , Product, Year, Quarter, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Product, Year, Quarter
-- Year>Quarter
union distinct
SELECT NULL , NULL, Year, Quarter, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY Year, Quarter
-- Year
union distinct
SELECT NULL , NULL, Year, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY Year
推荐答案
BigQuery没有多维数据集或分组集，因此下面是您可以使用的技巧-在我看来非常通用，可以扩展到任何维度，同时避免重复的代码行
select
(case when grp_set & 1 > 0 then ProductGroup end) as ProductGroup,
(case when grp_set & 2 > 0 then Product end) as Product,
(case when grp_set & 4 > 0 then Year end) as Year,
(case when grp_set & 8 > 0 then Quarter end) as Quarter,
sum(Revenue) as Revenue,
sum(Units) as Units
from `first-outlet-750.biengine_tutorial.Product`, unnest(generate_array(1, 16)) grp_set
where Year IN (2020) and Quarter in ('Q1')
group by 1, 2, 3, 4
having not (Year is null and not Quarter is null)
and not (ProductGroup is null and not Product is null)
-- order by 1, 2, 3, 4
如果应用于问题中的样本数据，则输出为
这篇关于使用GROUP BY ROLLUP透视表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
