[MySQL|Postgresql] Pivot 通用技巧

2018-05-09 置頂文章

7485
0
SQL
2019-04-24

在Oracle 和 SQLServer 都有現成的PIvot 樞紐分析函式可以用

在MySQL 中如果要使用樞紐分析怎麼辦...

今天和大家分享如何在MySQL 中使用樞紐分析

案例樣本資料：

CREATE TABLE T(
    userName VARCHAR(100),
    Price int,
    Dt DATE
);

INSERT INTO T VALUES ('Tom',100,'2017-01-01');
INSERT INTO T VALUES ('Amy',200,'2017-01-02');
INSERT INTO T VALUES ('Tom',1311,'2017-01-03');
INSERT INTO T VALUES ('Tom',122,'2017-03-01');
INSERT INTO T VALUES ('Tom',111,'2017-04-01');
INSERT INTO T VALUES ('Amy',232,'2017-05-01');
INSERT INTO T VALUES ('Tom',2312,'2017-05-02');
INSERT INTO T VALUES ('Tom',23,'2017-05-03');

期望輸出：

SQLServer Version

在SQLServr中有Pivot 可以這樣做

SELECT userName,
      coalesce(p1.[201701],0) '201701',
      coalesce(p1.[201702],0) '201702',
      coalesce(p1.[201703],0) '201703',
      coalesce(p1.[201704],0) '201704',
      coalesce(p1.[201705],0) '201705',
      coalesce(p1.[201706],0) '201706'
FROM (
    SELECT userName,Price,FORMAT(Dt,'yyyyMM') Dt
    FROM T
) t
PIVOT (
  SUM(Price) FOR  Dt
               IN ([201701],[201702],[201703],[201704],[201705],[201706])
)p1

MySQL Version

Mysql中使用PIVOT 我們可以使用 聚集函式 + CASE WHEN 表達式來達成

SELECT userName,
       SUM(CASE WHEN date_format(Dt,'%Y%m') = '201701' THEN Price ELSE 0 END) `201701`,
       SUM(CASE WHEN date_format(Dt,'%Y%m') = '201702' THEN Price ELSE 0 END) `201702`,
       SUM(CASE WHEN date_format(Dt,'%Y%m') = '201703' THEN Price ELSE 0 END) `201703`,
       SUM(CASE WHEN date_format(Dt,'%Y%m') = '201704' THEN Price ELSE 0 END) `201704`,
       SUM(CASE WHEN date_format(Dt,'%Y%m') = '201705' THEN Price ELSE 0 END) `201705`,
       SUM(CASE WHEN date_format(Dt,'%Y%m') = '201706' THEN Price ELSE 0 END) `201706`
FROM T
GROUP BY userName

sqlfiddle

聚集函式 + CASE WHEN 好看又通用可在各個資料庫中使用(支援聚集函式和CASE WHEN就可用XD)

一般我在任何地方寫PIVOT 都會先使用這種方法^^

Mysql 動態產生 pivot

要動態產生Pivot表最關鍵，有兩個知識點

動態組出SQL語法
動態執行SQL語法

如果要動態執行SQL在Mysql中有三個關鍵字需要使用到

PREPARE 準備SQL語句給一個聲明
EXECUTE動態執行SQL語法
DEALLOCATE PREPARE 釋放聲明資源

下面語法是動態產生SQL語法給@sql變數

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN ''',
      date_format(Dt,'%Y%m'),
      ''' THEN Price ELSE 0 END) AS `',
      date_format(Dt,'%Y%m'), '`'
    )
  ) INTO @sql
FROM T;


SET @sql = CONCAT('SELECT userName, ', @sql, ' 
                   FROM T
                   GROUP BY userName;');

產生SQL如下

SELECT userName, SUM(CASE WHEN '201701' THEN Price ELSE 0 END) AS `201701`,SUM(CASE WHEN '201703' THEN Price ELSE 0 END) AS `201703`,SUM(CASE WHEN '201704' THEN Price ELSE 0 END) AS `201704`,SUM(CASE WHEN '201705' THEN Price ELSE 0 END) AS `201705` 
                   FROM T
                   GROUP BY userName;

最後在動態執行SQL使用

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

完整SQL如下


SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN ''',
      date_format(Dt,'%Y%m'),
      ''' THEN Price ELSE 0 END) AS `',
      date_format(Dt,'%Y%m'), '`'
    )
  ) INTO @sql
FROM T;


SET @sql = CONCAT('SELECT userName, ', @sql, ' 
                   FROM T
                   GROUP BY userName;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

dynamic pivot sqlfiddle

參考連結 :　https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html

如果本文對您幫助很大，可街口支付斗內鼓勵石頭^^