MySQL 利用Pivoting格式化做報表

神諭丶發表於2014-08-21
MySql
此處用到了Pivoting技術
注：Pivoting技術是指一種可以把行轉換為列的技術，在Pivoting的執行過程中可能會用到聚合。
這裡討論的都是靜態Pivoting查詢，即使用者需要提前知道旋轉的屬性列的值，對於動態Pivoting，需要動態地構造查詢字串。


並在日誌結尾時附錄Unpivoting技術
注：Unpivoting可以看作是pivoting的反向操作，即將列旋轉成為行


用Pivoting格式化聚合資料：
（一般用於報表展現)

首先做一張測試表


  1. create table formatting(
  2. orderid int not null,
  3. orderdate date not null,
  4. empid int not null,
  5. custid vahrchar(10) not null,
  6. qty int not null,
  7. primary key (orderid,orderdate));

再插入一些測試資料：
  1. insert into formatting select 1,'2010-01-02',3,'A',10;
  2. insert into formatting select 2,'2010-04-02',2,'B',20;
  3. insert into formatting select 3,'2010-05-02',1,'A',30;
  4. insert into formatting select 4,'2010-07-02',3,'D',40;
  5. insert into formatting select 5,'2011-01-02',4,'A',20;
  6. insert into formatting select 6,'2011-01-02',3,'B',30;
  7. insert into formatting select 7,'2011-01-02',1,'C',40;
  8. insert into formatting select 8,'2009-01-02',2,'A',10;
  9. insert into formatting select 9,'2009-01-02',3,'B',20;

此時可以將表formatting看作是一張彙總表，比如網上商城的購物明細。
這份彙總表顯示了訂單號，訂單日期，員工編號，消費者編號和訂單數量。

要在此彙總表的基礎上進一步統計每個消費者每年的訂單數量，可能會想到用分組來獲得結果，比如：


  1. select custid,year(orderdate) year,sum(qty) sum
  2. from formatting
  3. group by custid,year(orderdate);
結果為：


這樣子並不直觀，如果可以通過旋轉得到這樣的結果，那就直觀和清晰多了：




  1. select custid,
  2.         ifnull(sum(case when a=2009 then qty end),0) \"2009\",
  3.         ifnull(sum(case when a=2010 then qty end),0) \"2010\",
  4.         ifnull(sum(case when a=2011 then qty end),0) \"2011\"
  5. from (select custid,year(orderdate) a,qty
  6.     from formatting) p
  7. group by custid;

但是當旋轉的元素非常多的說話，會產生較長的查詢字串
要縮短查詢的字串，可以預先產生一張矩陣表，包含每個要旋轉列的屬性：


  1. create table formatting_temp(
  2. orderyear int primary key,
  3. y2009 int,
  4. y2010 int,
  5. y2011 int);

  1. insert into formatting_temp select 2009,1,0,0;
  2. insert into formatting_temp select 2010,0,1,0;
  3. insert into formatting_temp select 2011,0,0,1;

將formatting和formatting_temp聯接


  1. select custid,
  2.         sum(qty*y2009) "2009",
  3.         sum(qty*y2010) "2010",
  4.         sum(qty*y2011) "2011"
  5. from(select custid,YEAR(orderdate) a,qty
  6.     from formatting) o
  7. join formatting_temp p
  8. on o.a=p.orderyear
  9. group by custid;


【Unpivoting】

Unpivoting操作是將列轉換成行，是Pivoting的逆操作


  1. create table unpivoting(
  2. custid vahrchar(10),
  3. y2009 int,
  4. y2010 int,
  5. y2011 int);


  1. insert into unpivoting
  2. select custid,
  3.         ifnull(sum(case when a=2009 then qty end),0) \"2009\",
  4.         ifnull(sum(case when a=2010 then qty end),0) \"2010\",
  5.         ifnull(sum(case when a=2011 then qty end),0) \"2011\"
  6. from (select custid,year(orderdate) a,qty
  7.     from formatting) p
  8. group by custid;

這裡是把之前的formatting旋轉之後的內容匯入到unpivoting表中。

目前unpivoting表的內容



解決方案及思路：
解決這個問題需要將列旋轉成為行，這裡使用的技巧是對每行資料產生3個副本
每個副本產生一個需要旋轉的列，這個過程可以通過如下的cross join來完成


  1. select *
  2. from unpivoting,(select 2009 as orderyear
  3.                 union all select 2010
  4.                 union all select 2011) a;

結果為：





接下來，只需要根據orderyear列來去的對應旋轉列的值就可以了

由於最後要得到




過濾掉qty=0的情況

因此這個問題的最終解決方案為：

  1. select custid,orderyear,qty
  2. from(
  3.     select custid,orderyear
  4.         case orderyear
  5.             when 2009 then y2009
  6.             when 2010 then y2010
  7.             when 2011 then y2011
  8.         end as qty
  9.     from unpivoting,(select 2009 as orderyear
  10.                 union all select 2010
  11.                 union all select 2011) a
  12.     ) b
  13. where qty <> 0;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1255327/，如需轉載，請註明出處，否則將追究法律責任。

相關文章