欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

詳解PostgreSQL中實(shí)現(xiàn)數(shù)據(jù)透視表的三種方法

 更新時(shí)間：2024年04月15日 10:49:46   作者：不剪發(fā)的Tony老師  
數(shù)據(jù)透視表（Pivot Table）是進(jìn)行數(shù)據(jù)匯總、分析、瀏覽和展示的強(qiáng)大工具,可以幫助我們了解數(shù)據(jù)中的對(duì)比情況、模式和趨勢(shì),是數(shù)據(jù)分析師和運(yùn)營(yíng)人員必備技能之一,本給大家介紹PostgreSQL中實(shí)現(xiàn)數(shù)據(jù)透視表的三種方法,需要的朋友可以參考下

數(shù)據(jù)透視表（Pivot Table）是進(jìn)行數(shù)據(jù)匯總、分析、瀏覽和展示的強(qiáng)大工具，可以幫助我們了解數(shù)據(jù)中的對(duì)比情況、模式和趨勢(shì)，是數(shù)據(jù)分析師和運(yùn)營(yíng)人員必備技能之一。今天我們來(lái)談?wù)勅绾卧?nbsp;PostgreSQL 中實(shí)現(xiàn)數(shù)據(jù)透視表的功能。

本文使用的示例數(shù)據(jù)可以點(diǎn)此下載。

使用 CASE 表達(dá)式實(shí)現(xiàn)數(shù)據(jù)透視表

實(shí)現(xiàn)數(shù)據(jù)行轉(zhuǎn)列的一個(gè)通用方法就是利用 CASE 條件表達(dá)式和分組聚合操作。首先使用以下 GROUP BY 子句對(duì)銷售數(shù)據(jù)進(jìn)行分類匯總：

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)品、渠道以及月份進(jìn)行匯總；rollup 選項(xiàng)用于生成不同層次的小計(jì)、合計(jì)以及總計(jì)；coalesce 函數(shù)用于將匯總行中的 NULL 值顯示為相應(yīng)的信息。該查詢返回的結(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|

接下來(lái)我們將數(shù)據(jù)按照不同月份顯示為不同的列，也就是將行轉(zhuǎn)換為列，這個(gè)功能可以使用 CASE 表達(dá)式實(shí)現(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) "總計(jì)"
from sales_data
group by rollup (product, channel);

產(chǎn)品       |渠道       |一月   |二月  |三月   |四月   |五月  |六月   |總計(jì)    |
----------|-----------|------|------|------|------|------|------|-------|
桔子       |店面       | 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|
蘋(píng)果       |店面       | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋(píng)果       |京東       | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋(píng)果       |淘寶       | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋(píng)果       |【所有渠道】|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|

第一個(gè) SUM 函數(shù)中的 CASE 表達(dá)式只匯總 201901 月份的銷量，其他月份銷量設(shè)置為 0；后面的 SUM 函數(shù)依次類推，得到了每個(gè)月的銷量匯總和所有月份的總計(jì)。

使用 CASE 條件表達(dá)式加上分組聚合的方法也適用于其他數(shù)據(jù)庫(kù)。

使用 FILTER 子句實(shí)現(xiàn)數(shù)據(jù)透視表

除了在聚合函數(shù)中使用 CASE 表達(dá)式之外，PostgreSQL 還提供了一個(gè)更簡(jiǎn)單的方法，就是 FILTER 子句。

aggregate_function(<expression>) FILTER (WHERE <condition>)

其中，aggregate_function 可以是任意的聚合函數(shù)或者窗口函數(shù)； FILTER 子句用于指定一個(gè)額外的條件，只有滿足該條件的數(shù)據(jù)行才會(huì)參與計(jì)算。

我們可以使用 SUM 函數(shù)的 FILTER 子句實(shí)現(xiàn)與上文 CASE 表達(dá)式相同的效果：

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) "總計(jì)"
from sales_data
group by rollup (product, channel);

產(chǎn)品       |渠道       |一月   |二月  |三月   |四月   |五月  |六月   |總計(jì)    |
----------|-----------|------|------|------|------|------|------|-------|
桔子       |店面       | 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|
蘋(píng)果       |店面       | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋(píng)果       |京東       | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋(píng)果       |淘寶       | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋(píng)果       |【所有渠道】|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 的擴(kuò)展模塊 tablefunc 提供了許多返回結(jié)果為數(shù)據(jù)表的函數(shù)，其中 crosstab 函數(shù)可以用于實(shí)現(xiàn)數(shù)據(jù)的行列轉(zhuǎn)換。這是一個(gè)擴(kuò)展模塊，所以我們需要先安裝插件：

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

以上錯(cuò)誤表示沒(méi)有安裝 postgresql-contrib 包，我們通過(guò)操作系統(tǒng)命令進(jìn)行安裝（CentOS）：

sudo yum install postgresql12-contrib

然后再次執(zhí)行上面的 create extension 命令安裝 tablefunc 模塊。

接下來(lái)就可以通過(guò) crosstab 函數(shù)將行轉(zhuǎn)換成列，例如：

select *
from crosstab(
$$select product||'-'||channel pc,product,channel, to_char(saledate, 'YYYYMM'), sum(amount)
from sales_data
group by product||'-'||channel, product, channel, to_char(saledate, 'YYYYMM')
order by 1$$,
$$select distinct to_char(saledate, 'YYYYMM') from sales_data order by 1$$
)
as ct(pc text, product text, channel text,
      "201901" numeric, "201902" numeric, "201903" numeric,
      "201904" numeric, "201905" numeric, "201906" numeric);

crosstab 函數(shù)包含 2 個(gè)字符串類型的參數(shù)，都是查詢語(yǔ)句；第一個(gè) select 語(yǔ)句用于構(gòu)造源數(shù)據(jù)，crosstab 的源數(shù)據(jù)需要指定一個(gè)標(biāo)識(shí)每個(gè)結(jié)果行的字段，示例中使用 pc；第二個(gè) select 語(yǔ)句的結(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|
蘋(píng)果-京東|蘋(píng)果     |京東     |38269.00|40593.00|56552.00|56662.00|64493.00|62045.00|
蘋(píng)果-店面|蘋(píng)果     |店面     |43845.00|40539.00|44909.00|55646.00|56771.00|64933.00|
蘋(píng)果-淘寶|蘋(píng)果     |淘寶     |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|

接下來(lái)還需要增加一個(gè)總計(jì)行和總計(jì)列：

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") "總計(jì)"
from crosstab(
$$select product||'-'||channel pc,product,channel, to_char(saledate, 'YYYYMM'), sum(amount)
from sales_data
group by product||'-'||channel, product, channel, to_char(saledate, 'YYYYMM')
order by 1$$,
$$select distinct to_char(saledate, 'YYYYMM') from sales_data order by 1$$
)
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é)果增加了一些總計(jì)數(shù)據(jù)并且修改了返回字段的名稱，讓結(jié)果更加接近 EXCEL 數(shù)據(jù)透視表：

產(chǎn)品       |渠道       |一月   |二月  |三月   |四月   |五月  |六月   |總計(jì)    |
----------|-----------|------|------|------|------|------|------|-------|
桔子       |京東       | 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|
蘋(píng)果       |京東       | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
蘋(píng)果       |店面       | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
蘋(píng)果       |淘寶       | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
蘋(píng)果       |【所有渠道】|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ù)透視表是一個(gè)非常實(shí)用的數(shù)據(jù)分析功能，可以用于實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)分類匯總和對(duì)比分析。本文介紹了在 PostgreSQL 中實(shí)現(xiàn)數(shù)據(jù)透視表的三種方式，包括使用 CASE 條件表達(dá)式和分組聚合相結(jié)合、聚合函數(shù)的 FILTER 子句以及擴(kuò)展模塊 tablefunc 中的 crosstab 函數(shù)生成數(shù)據(jù)透視表。

以上就是詳解PostgreSQL中實(shí)現(xiàn)數(shù)據(jù)透視表的三種方法的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL數(shù)據(jù)透視表的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

相關(guān)文章

  • postgresql中的ltree類型使用方法

    postgresql中的ltree類型使用方法

    這篇文章主要給大家介紹了關(guān)于postgresql中l(wèi)tree類型使用的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用postgresql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2019-09-09
  • Mac OS上安裝PostgreSQL的教程

    Mac OS上安裝PostgreSQL的教程

    今天我們來(lái)看在Mac OS上安裝PostgreSQL的教程,這里我們通過(guò)brew包管理器來(lái)安裝,所以首先我們會(huì)講解brew的安裝配置:
    2016-06-06
  • 使用PostgreSQL數(shù)據(jù)庫(kù)進(jìn)行中文全文搜索的實(shí)現(xiàn)方法

    使用PostgreSQL數(shù)據(jù)庫(kù)進(jìn)行中文全文搜索的實(shí)現(xiàn)方法

    目前在PostgreSQL中常見(jiàn)的兩個(gè)中文分詞插件是zhparser和pg_jieba,這里我們使用zhparser,插件的編譯和安裝請(qǐng)查看官方文檔 ,安裝還是比較復(fù)雜的,建議找個(gè)現(xiàn)成docker鏡像,本文給大家介紹了在PostgreSQL數(shù)據(jù)庫(kù)使用中文全文搜索,需要的朋友可以參考下
    2023-09-09
  • PostgreSQL導(dǎo)出數(shù)據(jù)庫(kù)表(或序列)的結(jié)構(gòu)和數(shù)據(jù)實(shí)例代碼

    PostgreSQL導(dǎo)出數(shù)據(jù)庫(kù)表(或序列)的結(jié)構(gòu)和數(shù)據(jù)實(shí)例代碼

    這篇文章主要給大家介紹了關(guān)于PostgreSQL導(dǎo)出數(shù)據(jù)庫(kù)表(或序列)的結(jié)構(gòu)和數(shù)據(jù)的相關(guān)資料,你可以使用pg_dump命令來(lái)導(dǎo)出PostgreSQL數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)和數(shù)據(jù),需要的朋友可以參考下
    2023-10-10
  • PostgreSQL數(shù)據(jù)庫(kù)中Sequence的使用方法詳解

    PostgreSQL數(shù)據(jù)庫(kù)中Sequence的使用方法詳解

    在 PostgreSQL 數(shù)據(jù)庫(kù)中,Sequence 是一種特殊的表對(duì)象,主要用于生成按順序遞增或遞減的數(shù)字序列,通常用于需要唯一標(biāo)識(shí)符的場(chǎng)景,例如自增 ID,以下是如何在 PostgreSQL 中使用 Sequence 的詳細(xì)步驟,需要的朋友可以參考下
    2024-11-11
  • PostgreSql日期類型處理詳細(xì)實(shí)例

    PostgreSql日期類型處理詳細(xì)實(shí)例

    PostgreSQL提供了大量用于獲取系統(tǒng)當(dāng)前日期和時(shí)間的函數(shù),例如 current_date、current_time、current_timestamp、clock_timestamp()等,這篇文章主要給大家介紹了關(guān)于PostgreSql日期類型處理的相關(guān)資料,需要的朋友可以參考下
    2023-05-05
  • pgsql的UUID生成函數(shù)實(shí)例

    pgsql的UUID生成函數(shù)實(shí)例

    這篇文章主要介紹了pgsql的UUID生成函數(shù)實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
    2021-01-01
  • PostgreSQL的應(yīng)用技巧和示例分享

    PostgreSQL的應(yīng)用技巧和示例分享

    本文會(huì)總結(jié)一些Postgres中，從應(yīng)用需求和場(chǎng)景出發(fā)，不太常見(jiàn)，但比較常用并且有用的SQL語(yǔ)句，文中的示例代碼簡(jiǎn)潔易懂，需要的小伙伴可以收藏一下
    2023-06-06
  • 修改一行代碼提升 Postgres 性能 100 倍

    修改一行代碼提升 Postgres 性能 100 倍

    在一個(gè)（差）的PostgreSQL 查詢中只要一個(gè)小小到改動(dòng)(ANY(ARRAY[...])to ANY(VALUES(...)))就能把查詢時(shí)間從20s縮減到0.2s
    2013-09-09
  • 淺談PostgreSQL中大小寫(xiě)不敏感問(wèn)題

    淺談PostgreSQL中大小寫(xiě)不敏感問(wèn)題

    這篇文章主要介紹了淺談PostgreSQL中大小寫(xiě)不敏感問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
    2021-01-01

最新評(píng)論