2021-05-12 14:32:11
詳解Oracle 中實現資料透視表的幾種方法
資料透視表(Pivot Table)是 Excel 中一個非常實用的分析功能,可以用於實現複雜的資料分類彙總和對比分析,是資料分析師和運營人員必備技能之一。今天我們來談談如何在 Oracle 資料庫中實現資料透視表。
本文使用的範例資料可以點此下載。
使用 CASE 表示式實現資料透視表
資料透視表的本質就是按照行和列的不同組合進行資料分組,然後對結果進行彙總;因此,它和資料庫中的分組(GROUP BY)和聚合函數(COUNT、SUM、AVG 等)的功能非常類似。
我們首先使用以下 GROUP BY 子句對銷售資料進行分類彙總:
select coalesce(product, '【全部產品】') "產品", coalesce(channel, '【所有渠道】') "渠道", coalesce(to_char(saledate, 'YYYYMM'), '【所有月份】') "月份", sum(amount) "銷量" from sales_data group by rollup (product,channel,to_char(saledate, 'YYYYMM'));
以上語句按照產品、渠道以及月份進行彙總;rollup 選項用於生成不同層次的小計、合計以及總計;coalesce 函數用於將彙總行中的 NULL 值顯示為相應的資訊。查詢返回的結果如下:
產品 |渠道 |月份 |銷量 |
---------|---------|-----------|-------|
桔子 |京東 |201901 | 41289|
桔子 |京東 |201902 | 43913|
桔子 |京東 |201903 | 49803|
桔子 |京東 |201904 | 49256|
桔子 |京東 |201905 | 64889|
桔子 |京東 |201906 | 62649|
桔子 |京東 |【所有月份】| 311799|
桔子 |店面 |201901 | 41306|
桔子 |店面 |201902 | 37906|
桔子 |店面 |201903 | 48866|
桔子 |店面 |201904 | 48673|
桔子 |店面 |201905 | 58998|
桔子 |店面 |201906 | 58931|
桔子 |店面 |【所有月份】| 294680|
桔子 |淘寶 |201901 | 43488|
桔子 |淘寶 |201902 | 37598|
桔子 |淘寶 |201903 | 48621|
桔子 |淘寶 |201904 | 49919|
桔子 |淘寶 |201905 | 58530|
桔子 |淘寶 |201906 | 64626|
桔子 |淘寶 |【所有月份】| 302782|
桔子 |【所有渠道】|【所有月份】| 909261|
...
香蕉 |【所有渠道】|【所有月份】| 925369|
【全部產品】|【所有渠道】|【所有月份】|2771682|
實際上,我們已經得到了資料透視表的彙總結果,只不過需要將資料按照不同月份顯示為不同的列;也就是需要將行轉換為列,這個功能可以使用 CASE 表示式實現:
select coalesce(product, '【全部產品】') "產品", coalesce(channel, '【所有渠道】') "渠道", sum(case to_char(saledate, 'YYYYMM') when '201901' then amount else 0 end) "一月", sum(case to_char(saledate, 'YYYYMM') when '201902' then amount else 0 end) "二月", sum(case to_char(saledate, 'YYYYMM') when '201903' then amount else 0 end) "三月", sum(case to_char(saledate, 'YYYYMM') when '201904' then amount else 0 end) "四月", sum(case to_char(saledate, 'YYYYMM') when '201905' then amount else 0 end) "五月", sum(case to_char(saledate, 'YYYYMM') when '201906' then amount else 0 end) "六月", sum(amount) "總計" from sales_data group by rollup (product, channel);
第一個 SUM 函數中的 CASE 表示式只彙總 201901 月份的銷量,其他月份銷量設定為 0;後面的 SUM 函數依次類推,得到了每個月的銷量彙總和所有月份的總計。
產品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |總計 |
----------|----------|------|------|------|------|------|------|-------|
桔子 |京東 | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子 |淘寶 | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子 |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
蘋果 |京東 | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋果 |淘寶 | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋果 |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉 |京東 | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉 |淘寶 | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉 |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部產品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
Oracle 中的 decode 函數也可以實現類似 CASE 表示式的功能。
以上實現資料透視表的方法存在一定的侷限性,假如還有 7 月份到 12 月份的銷量需要統計,我們就需要修改查詢語句增加這部分的處理。因此,Oracle 11g 引入了一個新的子句來實現自動的行轉列:PIVOT。
使用 PIVOT 子句實現資料透視表
Oracle 中的 PIVOT 子句用於將行轉換為列,基本語法如下:
SELECT col1, col2, ... FROM tbl PIVOT ( pivot_clause, pivot_for_clause, pivot_in_clause );
PIVOT 子句包含 3 個部分:
- pivot_clause,定義需要彙總的資料,也就是聚合函數。例如使用 SUM(amount) 彙總銷量;
- pivot_for_clause,指定需要從行轉換成列的欄位。例如使用 for saledate 將每個月的資料顯示為一列;
- pivot_in_clause,指定將 pivot_for_clause 欄位中的哪些資料值轉換為列。例如 in (‘201901', ‘201902') 表示只將 201901 和 201902 兩個月份的資料轉換為列。
對於上文中的範例,我們可以使用以下 PIVOT 子句:
with d(saledate, product, channel, amount) as ( select to_char(saledate, 'YYYYMM'), product, channel, amount from sales_data ) select * from d pivot ( sum(amount) for saledate in ('201901', '201902', '201903', '201904', '201905', '201906') ) order by product, channel;
其中,PIVOT 子句按照月份對銷量進行彙總並且將月份轉換為列顯示,返回的結果如下:
PRODUCT |CHANNEL |'201901'|'201902'|'201903'|'201904'|'201905'|'201906'|
---------|--------|--------|--------|--------|--------|--------|--------|
桔子 |京東 | 41289| 43913| 49803| 49256| 64889| 62649|
桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931|
桔子 |淘寶 | 43488| 37598| 48621| 49919| 58530| 64626|
蘋果 |京東 | 38269| 40593| 56552| 56662| 64493| 62045|
蘋果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933|
蘋果 |淘寶 | 42969| 43289| 48769| 58052| 58872| 59844|
香蕉 |京東 | 36879| 36981| 51748| 54801| 64936| 60688|
香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597|
香蕉 |淘寶 | 42468| 41955| 52780| 54971| 56504| 59213|
接下來我們需要增加一個總計行和總計列,為此可以先將 sales_data 資料進行分組統計然後再使用 PIVOT 子句進行轉換:
with d(saledate, product, channel, amount) as ( select to_char(saledate, 'YYYYMM'), product, channel, sum(amount) from sales_data group by rollup (to_char(saledate, 'YYYYMM'), product, channel) ), pt as ( select * from d pivot ( sum(amount) for saledate in ('201901' s01, '201902' s02, '201903' s03, '201904' s04, '201905' s05, '201906' s06) ) ) select coalesce(product, '【全部產品】') "產品", coalesce(channel, '【所有渠道】') "渠道", s01 "一月", s02 "二月", s03 "三月", s04 "四月", s05 "五月", s06 "六月", s01+s02+s03+s04+s05+s06 "總計" from pt order by product, channel;
我們在 PIVOT 子句返回的結果之上增加了一個 SELECT 查詢,並且修改了返回欄位的名稱,讓結果更加接近 EXCEL 資料透視表:
產品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |總計 |
----------|----------|------|------|------|------|------|------|-------|
桔子 |京東 | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子 |淘寶 | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子 |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
蘋果 |京東 | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋果 |淘寶 | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋果 |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉 |京東 | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉 |淘寶 | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉 |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部產品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
PIVOT 子句也可以一次執行多個聚合操作,或者按照多個欄位進行分組。例如:
with d(saledate, product, channel, amount) as ( select to_char(saledate, 'YYYYMM'), product, channel, amount from sales_data where to_char(saledate, 'YYYYMM') in ('201901', '201902', '201903') ) select * from d pivot ( sum(amount) for (channel, saledate) in (('淘寶','201901'), ('店面','201901'), ('京東','201901'), ('淘寶','201902'), ('店面','201902'), ('京東','201902'), ('淘寶','201903'), ('店面','201903'), ('京東','201903')) ); PRODUCT|'淘寶'_'201901'|'店面'_'201901'|'京東'_'201901'|'淘寶'_'201902'|'店面'_'201902'|'京東'_'201902'|'淘寶'_'201903'|'店面'_'201903'|'京東'_'201903'| -------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------| 香蕉 | 42468| 41210| 36879| 41955| 39420| 36981| 52780| 50884| 51748| 桔子 | 43488| 41306| 41289| 37598| 37906| 43913| 48621| 48866| 49803| 蘋果 | 42969| 43845| 38269| 43289| 40539| 40593| 48769| 44909| 56552|
以上查詢返回了按照渠道和月份分組的彙總結果,並且將它們轉換為列進行顯示。
與 PIVOT 相反的操作是 UNPIVOT,它可以將列轉換為行。我們通過以下範例將行專列之後的資料再轉換回來:
with d(saledate, product, channel, amount) as ( select to_char(saledate, 'YYYYMM'), product, channel, amount from sales_data ), pt as ( select * from d pivot ( sum(amount) for saledate in ('201901' "201901", '201902' "201902", '201903' "201903", '201904' "201904", '201905' "201905", '201906' "201906") ) ) select * from pt unpivot ( amount for saledate IN ("201901", "201902", "201903", "201904", "201905", "201906") );
其中,unpivot 子句也有三個選項,將每個月份代表的列轉換為 saledate 欄位中的行,並且將對應的資料轉換為 amount 欄位中的行。以上查詢返回的結果如下:
PRODUCT |CHANNEL |SALEDATE|AMOUNT|
--------|--------|--------|------|
桔子 |京東 |201901 | 41289|
桔子 |京東 |201902 | 43913|
桔子 |京東 |201903 | 49803|
桔子 |京東 |201904 | 49256|
桔子 |京東 |201905 | 64889|
桔子 |京東 |201906 | 62649|
香蕉 |店面 |201901 | 41210|
香蕉 |店面 |201902 | 39420|
香蕉 |店面 |201903 | 50884|
香蕉 |店面 |201904 | 52085|
香蕉 |店面 |201905 | 60249|
香蕉 |店面 |201906 | 67597|
...
如果想要解鎖更多的 PIVOT 和 UNPIVOT 的使用姿勢,可以參考官方檔案中的定義和範例。
使用 MODEL 子句實現資料透視表
除了 PIVOT 子句之外,Oracle 還提供一個更加強大的功能:MODEL 子句。簡單來說,MODEL 子句可以實現 EXCEL 等電子試算表中基於位置和符號的單元格參照以及複雜的公式計算。
完整的 MODEL 子句比較複雜,我們直接看一個範例:
with d(saledate, product, channel, amount) as ( select to_char(saledate, 'YYYYMM'), product, channel, sum(amount) from sales_data group by rollup (to_char(saledate, 'YYYYMM'), product, channel) ) select coalesce(product, '【全部產品】') "產品", coalesce(channel, '【所有渠道】') "渠道", s201901 "一月", s201902 "二月", s201903 "三月", s201904 "四月", s201905 "五月", s201906 "六月", stotal "總計" from d model return updated rows partition by (product, channel) dimension by (saledate) measures (amount, 0 s201901, 0 s201902, 0 s201903, 0 s201904, 0 s201905, 0 s201906, 0 stotal) unique dimension rules upsert all (s201901[0] = amount['201901'], s201902[0] = amount['201902'], s201903[0] = amount['201903'], s201904[0] = amount['201904'], s201905[0] = amount['201905'], s201906[0] = amount['201906'], stotal[0] = sum(amount)[saledate between '201901' and '201906']) order by product, channel;
首先,通過 with 子句獲得基本資料。然後使用 model 子句實現行專列;return updated rows 表示只返回計算模型更新和插入的資料,partition by 用於定義分割區(產品和渠道),每個分割區獨立計算;dimension by 指定度量的維度(月份);measures 定義度量,amount 來自源表,0 s201901 表示建立一個度量 s201901 並初始化為 0;unique dimension 表示 partition by 加 dimension by 欄位可以唯一確定模型中的每個單元格;rules 用於定義給每個度量賦值的表示式,upsert all 表示更新已有的單元格,如果不存在則建立單元格;s201901[0] 是通過位置對單元格的參照(維度為 1),amount[‘201901'] 表示月份 201901 對應的 amount 欄位值,stotal[0] 是所有月份的總和。
以上語句返回的結果如下:
產品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |總計 |
----------|----------|------|------|------|------|------|------|-------|
桔子 |京東 | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子 |淘寶 | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子 |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
蘋果 |京東 | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋果 |淘寶 | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋果 |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉 |京東 | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉 |淘寶 | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉 |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部產品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
MODEL 子句允許通過分割區(PARTITION BY)和維度(DIMENSION BY)建立一個多維陣列,並且通過指定規則(RULES)來操作和更新陣列中單元格中的度量值(MEASURES)。其中,規則支援萬用字元和迴圈迭代,度量可以使用聚合函數和視窗函數。
MODEL 子句完整的使用姿勢請參考官方檔案。
到此這篇關於詳解Oracle 中實現資料透視表的幾種方法的文章就介紹到這了,更多相關Oracle 資料透視表內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!
相關文章