[MySQL|Postgresql] Pivot 通用技巧

  • 7290
  • 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表最關鍵,有兩個知識點

  1. 動態組出SQL語法
  2. 動態執行SQL語法

 

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

下面語法是動態產生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


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