2021-05-12 14:32:11
MySQL/MariaDB 如何實現資料透視表的範例程式碼
前文介紹了Oracle 中實現資料透視表的幾種方法,今天我們來看看在 MySQL/MariaDB 中如何實現相同的功能。
本文使用的範例資料可以點此下載。
使用 CASE 表示式和分組聚合
資料透視表的本質就是按照行和列的不同組合進行資料分組,然後對結果進行彙總;因此,它和資料庫中的分組(GROUP BY)加聚合函數(COUNT、SUM、AVG 等)的功能非常類似。
我們首先使用以下 GROUP BY 子句對銷售資料進行分類彙總:
select coalesce(product, '【全部產品】') "產品", 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;
以上語句按照產品、渠道以及月份進行彙總;with rollup 選項用於生成不同層次的小計、合計以及總計;coalesce 函數用於將彙總行中的 NULL 值顯示為相應的資訊;any_value 函數用於返回分組內的任意資料,如果去掉會返回語法錯誤(MySQL 的一個 bug)。該查詢返回的結果如下:
產品 |渠道 |月份 |銷量 |
---------|---------|-----------|-------|
桔子 |京東 |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 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) "總計" from sales_data group by product, channel with rollup;
第一個 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|
MySQL 中的 IF(expr1,expr2,expr3) 函數也可以用於替換上面 CASE 表示式。
有行轉列就有列轉行,MySQL 也沒有專門的函數處理這種情況,可以使用 UNION 操作符將多個結果集進行合併。例如:
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;
通用表表達(with 子句)構造了包含多個月份的銷量資料,每個月份都是一列;然後每個查詢返回一個月份的資料,並且通過 union all 操作符將所有結果合併到一起。
使用預編譯的動態 SQL 語句
使用 CASE 表示式和聚合函數實現資料透視表的方法存在一定的侷限性,假如還有 7 月份到 12 月份的銷量需要統計,我們就需要修改查詢語句增加這部分的處理。為此,我們可以使用動態 SQL 自動生成行列轉換的語句:
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, ''【全部產品】'') "產品", coalesce(channel, ''【所有渠道】'') "渠道",', @sql, ', sum(amount) "總計" from sales_data group by product, channel with rollup;'); select @sql; prepare stmt from @sql; execute stmt; deallocate prepare stmt;
首先,通過查詢 sales_data 表找出所有的月份並且構造 sum 函數,將構造的語句存入變數 @sql 中;group_concat 函數可以將多行字串合併成單個字串。
group_concat 函數允許返回的最大長度(位元組)由系統變數 group_concat_max_len 進行設定,預設值為 1024。
然後,使用 set 命令將查詢語句的其他部分和已有的內容進行合併,生成的查詢語句如下:
select coalesce(product, '【全部產品】') "產品", 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) "總計" from sales_data group by product, channel with rollup;
最後通過預編譯命令執行該語句並返回結果,即使增加了其他月份的銷售資料也不需要手動修改查詢語句。
使用 CONNECT 儲存引擎
如果使用 MariaDB 10.0 以上的版本,可以利用 CONNECT 儲存引擎中的 PIVOT 表型別實現資料透視表。
首先,我們需要安裝 CONNECT 儲存引擎。Windows 系統可以執行以下命令進行動態安裝:
INSTALL SONAME 'ha_connect';
也可以在組態檔 my.ini 中增加以下內容,不過需要重啟服務:
[mysqld] plugin_load_add = ha_connect
對於 Linux 系統,安裝過程可以參考官方檔案。
接下來我們定義一個 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 定義儲存引擎為 connect;table_type 定義表的型別為 pivot;option_list 用於定義各種選項,PivotCol 表示要轉換成多個欄位的資料所在的列,FncCol 指定要進行彙總的欄位,其他是連線源表伺服器的資訊;SrcDef 用於指定源表查詢語句,也可以使用 Tabname 指定表名;上面的欄位是透視表的結構,flag=1 表示聚合之後的欄位。
建立成功之後,我們就可以直接查詢 pivot_sales 表中的資料了:
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 表支援的功能有限,只能進行一些基本的操作。例如:
-- 不會出錯 select * from pivot_sales where channel ='京東'; -- 語法錯誤 select channel from pivot_sales where channel ='京東';
到此這篇關於MySQL/MariaDB 如何實現資料透視表的範例程式碼的文章就介紹到這了,更多相關MySQL/MariaDB資料透視表內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!
相關文章