PostgreSQL 如何實現(xiàn)數(shù)據(jù)透視表
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學,十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗,目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目錄
使用 CASE 表達式實現(xiàn)數(shù)據(jù)透視表
使用 FILTER 子句實現(xiàn)數(shù)據(jù)透視表
使用 CROSSTAB 函數(shù)創(chuàng)建數(shù)據(jù)透視表
總結(jié)
大家好,我是只談技術(shù)不剪發(fā)的 Tony 老師。
數(shù)據(jù)透視表(Pivot Table)是進行數(shù)據(jù)匯總、分析、瀏覽和展示的強大工具,可以幫助我們了解數(shù)據(jù)中的對比情況、模式和趨勢,是數(shù)據(jù)分析師和運營人員必備技能之一。前文已經(jīng)介紹了 MySQL/MariaDB、Oracle 以及 Microsoft SQL Server 中的數(shù)據(jù)透視表實現(xiàn),今天我們來談談如何在 PostgreSQL 中實現(xiàn)相同的功能。
??本文使用的示例數(shù)據(jù)可以點此下載。
使用 CASE 表達式實現(xiàn)數(shù)據(jù)透視表
實現(xiàn)數(shù)據(jù)行轉(zhuǎn)列的一個通用方法就是利用 CASE 條件表達式和分組聚合操作。首先使用以下 GROUP BY 子句對銷售數(shù)據(jù)進行分類匯總:
select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品",
coalesce(channel, '【所有渠道】') "渠道",
coalesce(to_char(saledate, 'YYYYMM'), '【所有月份】') "月份",
sum(amount) "銷量"
from sales_data
group by rollup (product,channel, to_char(saledate, 'YYYYMM'))
order by product, channel, to_char(saledate, 'YYYYMM');
其中,group by 將數(shù)據(jù)按照產(chǎn)品、渠道以及月份進行匯總;rollup 選項用于生成不同層次的小計、合計以及總計;coalesce 函數(shù)用于將匯總行中的 NULL 值顯示為相應的信息。該查詢返回的結(jié)果如下:
產(chǎn)品 |渠道 |月份 |銷量 |
----------|-----------|----------|-------|
桔子 |店面 |201901 | 41306|
桔子 |店面 |201902 | 37906|
桔子 |店面 |201903 | 48866|
桔子 |店面 |201904 | 48673|
桔子 |店面 |201905 | 58998|
桔子 |店面 |201906 | 58931|
桔子 |店面 |【所有月份】| 294680|
桔子 |京東 |201901 | 41289|
桔子 |京東 |201902 | 43913|
桔子 |京東 |201903 | 49803|
桔子 |京東 |201904 | 49256|
桔子 |京東 |201905 | 64889|
桔子 |京東 |201906 | 62649|
桔子 |京東 |【所有月份】| 311799|
桔子 |淘寶 |201901 | 43488|
桔子 |淘寶 |201902 | 37598|
桔子 |淘寶 |201903 | 48621|
桔子 |淘寶 |201904 | 49919|
桔子 |淘寶 |201905 | 58530|
桔子 |淘寶 |201906 | 64626|
桔子 |淘寶 |【所有月份】| 302782|
桔子 |【所有渠道】|【所有月份】| 909261|
...
香蕉 |【所有渠道】|【所有月份】| 925369|
【全部產(chǎn)品】|【所有渠道】|【所有月份】|2771682|
接下來我們將數(shù)據(jù)按照不同月份顯示為不同的列,也就是將行轉(zhuǎn)換為列,這個功能可以使用 CASE 表達式實現(xiàn):
select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品", 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);
產(chǎn)品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |總計 |
----------|-----------|------|------|------|------|------|------|-------|
桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子 |京東 | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子 |淘寶 | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子 |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
蘋果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋果 |京東 | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋果 |淘寶 | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋果 |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉 |京東 | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉 |淘寶 | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉 |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部產(chǎn)品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
第一個 SUM 函數(shù)中的 CASE 表達式只匯總 201901 月份的銷量,其他月份銷量設(shè)置為 0;后面的 SUM 函數(shù)依次類推,得到了每個月的銷量匯總和所有月份的總計。
??使用 CASE 條件表達式加上分組聚合的方法也適用于其他數(shù)據(jù)庫。
使用 FILTER 子句實現(xiàn)數(shù)據(jù)透視表
除了在聚合函數(shù)中使用 CASE 表達式之外,PostgreSQL 還提供了一個更簡單的方法,就是 FILTER 子句。
aggregate_function(<expression>) FILTER (WHERE <condition>)
其中,aggregate_function 可以是任意的聚合函數(shù)或者窗口函數(shù); FILTER 子句用于指定一個額外的條件,只有滿足該條件的數(shù)據(jù)行才會參與計算。
我們可以使用 SUM 函數(shù)的 FILTER 子句實現(xiàn)與上文 CASE 表達式相同的效果:
select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品", coalesce(channel, '【所有渠道】') "渠道",
sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "一月",
sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "二月",
sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "三月",
sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "四月",
sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "五月",
sum(amount) filter(where to_char(saledate, 'YYYYMM') = '201901') "六月",
sum(amount) "總計"
from sales_data
group by rollup (product, channel);
產(chǎn)品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |總計 |
----------|-----------|------|------|------|------|------|------|-------|
桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子 |京東 | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子 |淘寶 | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子 |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
蘋果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋果 |京東 | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋果 |淘寶 | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋果 |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉 |京東 | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉 |淘寶 | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉 |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部產(chǎn)品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
??PostgreSQL 支持在 filter 子句中使用子查詢,例如 exists。
使用 CROSSTAB 函數(shù)創(chuàng)建數(shù)據(jù)透視表
PostgreSQL 的擴展模塊 tablefunc 提供了許多返回結(jié)果為數(shù)據(jù)表的函數(shù),其中 crosstab 函數(shù)可以用于實現(xiàn)數(shù)據(jù)的行列轉(zhuǎn)換。這是一個擴展模塊,所以我們需要先安裝插件:
create extension if not exists tablefunc;
ERROR: could not open extension control file "/usr/pgsql-12/share/extension/tablefunc.control": No such file or directory
以上錯誤表示沒有安裝 postgresql-contrib 包,我們通過操作系統(tǒng)命令進行安裝(CentOS):
sudo yum install postgresql12-contrib
然后再次執(zhí)行上面的 create extension 命令安裝 tablefunc 模塊。
接下來就可以通過 crosstab 函數(shù)將行轉(zhuǎn)換成列,例如:
select *
from crosstab(
)
as ct(pc text, product text, channel text,
"201901" numeric, "201902" numeric, "201903" numeric,
"201904" numeric, "201905" numeric, "201906" numeric);
crosstab 函數(shù)包含 2 個字符串類型的參數(shù),都是查詢語句;第一個 select 語句用于構(gòu)造源數(shù)據(jù),crosstab 的源數(shù)據(jù)需要指定一個標識每個結(jié)果行的字段,示例中使用 pc;第二個 select 語句的結(jié)果用于構(gòu)造轉(zhuǎn)換之后的字段;as 子句用于定義返回結(jié)果的字段類型。
pc |product |channel |201901 |201902 |201903 |201904 |201905 |201906 |
--------|--------|--------|--------|--------|--------|--------|--------|--------|
桔子-京東|桔子 |京東 |41289.00|43913.00|49803.00|49256.00|64889.00|62649.00|
桔子-店面|桔子 |店面 |41306.00|37906.00|48866.00|48673.00|58998.00|58931.00|
桔子-淘寶|桔子 |淘寶 |43488.00|37598.00|48621.00|49919.00|58530.00|64626.00|
蘋果-京東|蘋果 |京東 |38269.00|40593.00|56552.00|56662.00|64493.00|62045.00|
蘋果-店面|蘋果 |店面 |43845.00|40539.00|44909.00|55646.00|56771.00|64933.00|
蘋果-淘寶|蘋果 |淘寶 |42969.00|43289.00|48769.00|58052.00|58872.00|59844.00|
香蕉-京東|香蕉 |京東 |36879.00|36981.00|51748.00|54801.00|64936.00|60688.00|
香蕉-店面|香蕉 |店面 |41210.00|39420.00|50884.00|52085.00|60249.00|67597.00|
香蕉-淘寶|香蕉 |淘寶 |42468.00|41955.00|52780.00|54971.00|56504.00|59213.00|
接下來還需要增加一個總計行和總計列:
select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品", coalesce(channel, '【所有渠道】') "渠道",
sum("201901") "一月", sum("201902") "二月", sum("201903") "三月",
sum("201904") "四月", sum("201905") "五月", sum("201906") "六月",
sum("201901"+"201902"+"201903"+"201904"+"201905"+"201906") "總計"
from crosstab(
)
as ct(pc text, product text, channel text,
"201901" numeric, "201902" numeric, "201903" numeric,
"201904" numeric, "201905" numeric, "201906" numeric)
group by rollup (product, channel);
我們基于 crosstab 函數(shù)的結(jié)果增加了一些總計數(shù)據(jù)并且修改了返回字段的名稱,讓結(jié)果更加接近 EXCEL 數(shù)據(jù)透視表:
產(chǎn)品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |總計 |
----------|-----------|------|------|------|------|------|------|-------|
桔子 |京東 | 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|
【全部產(chǎn)品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
總結(jié)
數(shù)據(jù)透視表是一個非常實用的數(shù)據(jù)分析功能,可以用于實現(xiàn)復雜的數(shù)據(jù)分類匯總和對比分析。本文介紹了在 PostgreSQL 中實現(xiàn)數(shù)據(jù)透視表的三種方式,包括使用 CASE 條件表達式和分組聚合相結(jié)合、聚合函數(shù)的 FILTER 子句以及擴展模塊 tablefunc 中的 crosstab 函數(shù)生成數(shù)據(jù)透視表。