【注意：這里可以使用任何支持 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
uj5u.com熱心網友回復：
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
如果應用于您問題中的樣本資料 - 輸出是
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/344742.html
標籤：sql 谷歌-bigquery 卷起