MySQL/MariaDB怎么實(shí)現(xiàn)數(shù)據(jù)透視表

這篇文章將為大家詳細(xì)講解有關(guān)MySQL/MariaDB怎么實(shí)現(xiàn)數(shù)據(jù)透視表，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

使用 CASE 表達(dá)式和分組聚合

數(shù)據(jù)透視表的本質(zhì)就是按照行和列的不同組合進(jìn)行數(shù)據(jù)分組，然后對(duì)結(jié)果進(jìn)行匯總；因此，它和數(shù)據(jù)庫(kù)中的分組（GROUP BY）加聚合函數(shù)（COUNT、SUM、AVG 等）的功能非常類似。

我們首先使用以下 GROUP BY 子句對(duì)銷售數(shù)據(jù)進(jìn)行分類匯總：

select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品",
    coalesce(channel, '【所有渠道】') "渠道",
    any_value(coalesce(extract(year_month from saledate), '【所有月份】')) "月份",
    sum(amount) "銷量"
from sales_data
group by product,channel,extract(year_month from saledate) with rollup;

以上語(yǔ)句按照產(chǎn)品、渠道以及月份進(jìn)行匯總；with rollup 選項(xiàng)用于生成不同層次的小計(jì)、合計(jì)以及總計(jì)；coalesce 函數(shù)用于將匯總行中的 NULL 值顯示為相應(yīng)的信息；any_value 函數(shù)用于返回分組內(nèi)的任意數(shù)據(jù)，如果去掉會(huì)返回語(yǔ)法錯(cuò)誤（MySQL 的一個(gè) bug）。該查詢返回的結(jié)果如下：

產(chǎn)品      |渠道      |月份       |銷量    |
---------|---------|-----------|-------|
桔子      |京東      |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|
【全部產(chǎn)品】|【所有渠道】|【所有月份】|2771682|

實(shí)際上，我們已經(jīng)得到了銷量的匯總結(jié)果，只不過(guò)需要將數(shù)據(jù)按照不同月份顯示為不同的列；也就是需要將行轉(zhuǎn)換為列，這個(gè)功能可以使用 CASE 表達(dá)式實(shí)現(xiàn)：

select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品", coalesce(channel, '【所有渠道】') "渠道", 
    sum(case extract(year_month from saledate) when 201901 then amount else 0 end) "一月",
    sum(case extract(year_month from saledate) when 201902 then amount else 0 end) "二月",
    sum(case extract(year_month from saledate) when 201903 then amount else 0 end) "三月",
    sum(case extract(year_month from saledate) when 201904 then amount else 0 end) "四月",
    sum(case extract(year_month from saledate) when 201905 then amount else 0 end) "五月",
    sum(case extract(year_month from saledate) when 201906 then amount else 0 end) "六月",
    sum(amount) "總計(jì)"
from sales_data
group by product, channel with rollup;

第一個(gè) SUM 函數(shù)中的 CASE 表達(dá)式只匯總 201901 月份的銷量，其他月份銷量設(shè)置為 0；后面的 SUM 函數(shù)依次類推，得到了每個(gè)月的銷量匯總和所有月份的總計(jì)。該查詢返回的數(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|
蘋果       |京東      | 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|

MySQL 中的 IF(expr1,expr2,expr3) 函數(shù)也可以用于替換上面 CASE 表達(dá)式。

有行轉(zhuǎn)列就有列轉(zhuǎn)行，MySQL 也沒(méi)有專門的函數(shù)處理這種情況，可以使用 UNION 操作符將多個(gè)結(jié)果集進(jìn)行合并。例如：

with d as (
 select product, channel,
     sum(case extract(year_month from saledate) when 201901 then amount else 0 end) s01,
     sum(case extract(year_month from saledate) when 201902 then amount else 0 end) s02,
     sum(case extract(year_month from saledate) when 201903 then amount else 0 end) s03,
     sum(case extract(year_month from saledate) when 201904 then amount else 0 end) s04,
     sum(case extract(year_month from saledate) when 201905 then amount else 0 end) s05,
     sum(case extract(year_month from saledate) when 201906 then amount else 0 end) s06
 from sales_data
 group by product, channel
)
select product, channel, 201901 saledate, s01 amount from d
union all
select product, channel, 201902 saledate, s02 from d
union all
select product, channel, 201903 saledate, s03 from d
union all
select product, channel, 201904 saledate, s04 from d
union all
select product, channel, 201905 saledate, s05 from d
union all
select product, channel, 201906 saledate, s06 from d;

通用表表達(dá)（with 子句）構(gòu)造了包含多個(gè)月份的銷量數(shù)據(jù)，每個(gè)月份都是一列；然后每個(gè)查詢返回一個(gè)月份的數(shù)據(jù)，并且通過(guò) union all 操作符將所有結(jié)果合并到一起。

使用預(yù)編譯的動(dòng)態(tài) SQL 語(yǔ)句

使用 CASE 表達(dá)式和聚合函數(shù)實(shí)現(xiàn)數(shù)據(jù)透視表的方法存在一定的局限性，假如還有 7 月份到 12 月份的銷量需要統(tǒng)計(jì)，我們就需要修改查詢語(yǔ)句增加這部分的處理。為此，我們可以使用動(dòng)態(tài) SQL 自動(dòng)生成行列轉(zhuǎn)換的語(yǔ)句：

select group_concat(
 distinct concat(
  ' sum(case extract(year_month from saledate) when ', dt,
  ' then amount else 0 end) as "', dt, '"')
 ) into @sql
from (
 select extract(year_month from saledate) as dt
 from sales_data
 order by saledate
) d;

set @sql
 = concat('select coalesce(product, ''【全部產(chǎn)品】'') "產(chǎn)品", coalesce(channel, ''【所有渠道】'') "渠道",', @sql,
      ', sum(amount) "總計(jì)"
      from sales_data
      group by product, channel with rollup;');
select @sql;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

首先，通過(guò)查詢 sales_data 表找出所有的月份并且構(gòu)造 sum 函數(shù)，將構(gòu)造的語(yǔ)句存入變量 @sql 中；group_concat 函數(shù)可以將多行字符串合并成單個(gè)字符串。

group_concat 函數(shù)允許返回的最大長(zhǎng)度（字節(jié)）由系統(tǒng)變量 group_concat_max_len 進(jìn)行設(shè)置，默認(rèn)值為 1024。

然后，使用 set 命令將查詢語(yǔ)句的其他部分和已有的內(nèi)容進(jìn)行合并，生成的查詢語(yǔ)句如下：

select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品", coalesce(channel, '【所有渠道】') "渠道", 
    sum(case extract(year_month from saledate) when 201901 then amount else 0 end) as "201901", 
    sum(case extract(year_month from saledate) when 201902 then amount else 0 end) as "201902", 
    sum(case extract(year_month from saledate) when 201903 then amount else 0 end) as "201903", 
    sum(case extract(year_month from saledate) when 201904 then amount else 0 end) as "201904", 
    sum(case extract(year_month from saledate) when 201905 then amount else 0 end) as "201905", 
    sum(case extract(year_month from saledate) when 201906 then amount else 0 end) as "201906", 
    sum(amount) "總計(jì)"
from sales_data
group by product, channel with rollup;

最后通過(guò)預(yù)編譯命令執(zhí)行該語(yǔ)句并返回結(jié)果，即使增加了其他月份的銷售數(shù)據(jù)也不需要手動(dòng)修改查詢語(yǔ)句。

使用 CONNECT 存儲(chǔ)引擎

如果使用 MariaDB 10.0 以上的版本，可以利用 CONNECT 存儲(chǔ)引擎中的 PIVOT 表類型實(shí)現(xiàn)數(shù)據(jù)透視表。

首先，我們需要安裝 CONNECT 存儲(chǔ)引擎。Windows 系統(tǒng)可以執(zhí)行以下命令進(jìn)行動(dòng)態(tài)安裝：

INSTALL SONAME 'ha_connect';

也可以在配置文件 my.ini 中增加以下內(nèi)容，不過(guò)需要重啟服務(wù)：

[mysqld]
plugin_load_add = ha_connect

對(duì)于 Linux 系統(tǒng)，安裝過(guò)程可以參考官方文檔。

接下來(lái)我們定義一個(gè) pivot 類型的表：

create table pivot_sales(
 product varchar(20) not null,
 channel varchar(20) not null,
 `201901` decimal(10,2) not null flag=1,
 `201902` decimal(10,2) not null flag=1,
 `201903` decimal(10,2) not null flag=1,
 `201904` decimal(10,2) not null flag=1,
 `201905` decimal(10,2) not null flag=1,
 `201906` decimal(10,2) not null flag=1
)
engine=connect table_type=pivot
option_list='PivotCol=saledate,FncCol=amount,host=127.0.0.1,user=root, password=p123456,port=3306'
SrcDef='select product,channel,date_format(saledate, ''%Y%m'') saledate,sum(amount) amount from sales_data group by product,channel,date_format(saledate, ''%Y%m'')';

其中，engine 定義存儲(chǔ)引擎為 connect；table_type 定義表的類型為 pivot；option_list 用于定義各種選項(xiàng)，PivotCol 表示要轉(zhuǎn)換成多個(gè)字段的數(shù)據(jù)所在的列，F(xiàn)ncCol 指定要進(jìn)行匯總的字段，其他是連接源表服務(wù)器的信息；SrcDef 用于指定源表查詢語(yǔ)句，也可以使用 Tabname 指定表名；上面的字段是透視表的結(jié)構(gòu)，flag=1 表示聚合之后的字段。

創(chuàng)建成功之后，我們就可以直接查詢 pivot_sales 表中的數(shù)據(jù)了：

select * from pivot_sales;

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|

目前，PIVOT 表支持的功能有限，只能進(jìn)行一些基本的操作。例如：

-- 不會(huì)出錯(cuò)
select * from pivot_sales
where channel ='京東';

-- 語(yǔ)法錯(cuò)誤
select channel from pivot_sales
where channel ='京東';

關(guān)于“MySQL/MariaDB怎么實(shí)現(xiàn)數(shù)據(jù)透視表”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。