mysql列反轉Pivoting

標籤：

Pivoting是一項可以把行旋轉為列的技術。在執行Pivoting的過程中可能會使用到彙總。Pivoting技術應用非常廣泛。下面討論的都是靜態Pivoting查詢，即使用者需要提前知道旋轉的屬性和列的值。對於動態Pivoting，需要動態地構造字串。

開放架構開發架構是一種用於頻繁更改架構的一種設計模式。利用關係型資料庫和SQL語句可以非常有效地處理DML，包括INSERT、SELECT、UPDATE和DELETE。然而，DDL在頻繁進行架構更改時顯得十分不方便。例如城對錶結構進行修改，使用者必須添加、修改或刪除列，而這種操作正是關係型資料庫不擅長的方面。因此，在頻繁更改架構的情況下，可以在一個表中儲存所有的資料，每行儲存一個屬性的值，多用VARCHAR來儲存，因為其容納各種類型的資料。下面的語句產生一張開發架構的表t.

CREATE TABLE t(id INT,attribute VARCHAR(10),value VARCHAR(20),PRIMARY KEY(id,attribute));

INSERT INTO t SELECT 1,‘attr1‘,‘BMW‘;INSERT INTO t SELECT 1,‘attr2‘,‘100‘;INSERT INTO t SELECT 1,‘attr3‘,‘2010-01-01‘;INSERT INTO t SELECT 2,‘attr2‘,‘200‘;INSERT INTO t SELECT 2,‘attr3‘,‘2010-03-04‘;INSERT INTO t SELECT 2,‘attr4‘,‘M‘;INSERT INTO t SELECT 2,‘attr5‘,‘55.60‘;INSERT INTO t SELECT 3,‘attr1‘,‘SUV‘;INSERT INTO t SELECT 3,‘attr2‘,‘10‘;INSERT INTO t SELECT 3,‘attr3‘,‘2011-11-11‘;

表的內容如下從上面的例子中可以看到，在對通過開放架構設計的表進行添加、修改或刪除表和列時，只需要通過INSERT、UPDATE、DELETE操作來完成邏輯架構的更改即可。當然使用這種方法可能導致資料庫的其他特性無法使用，如完整性條件約束、SQL最佳化乖，同時查詢資料變得不如之前的SQL語句來得直接和直觀。所以，對於利用開放架構設計的表，一般使用Pivoting技術來查詢資料。Privoting技術需要和彙總一起使用，首先要確定結果的行數與表中行數的關係。對於開放架構表t，應該有3行5列，這可以通過分組id來得到。因此可以通過下列Pivoting進行行列互轉以得到資料。

SELECT id,    MAX(CASE WHEN attribute=‘attr1‘ THEN value END) AS attr1,    MAX(CASE WHEN attribute=‘attr2‘ THEN value END) AS attr2,    MAX(CASE WHEN attribute=‘attr3‘ THEN value END) AS attr3,    MAX(CASE WHEN attribute=‘attr4‘ THEN value END) AS attr4,    MAX(CASE WHEN attribute=‘attr5‘ THEN value END) AS attr5FROM tGROUP BY id;

Pivoting先根據id進行分組，確定行列互轉後記錄的行數。之後通過已知的5個屬性來確定行列互轉後有5列資料，並通過CASE得到每列的值。由於使用了分組技術，因此一定要使用分組函數來取得列的值，故這裡使用MAX函數，當然也可以使用MIN函數。最後得到的結果如

這種旋轉方式是非常高效的，因為它只對錶進行一次掃描。另外，這是一種靜態Pivoting，使用者必須事件知道一共有多少個屬性，然而對於一般開放架構表，使用者都會定義一個最大的屬性個數，這樣可以比較容易進行Pivoting  關係除法關係除法(Rational Divistion)和常見的關係運算JOIN、SEMI JOIN一樣，都是一個關係代數。當除數集合中的元素數量較小時，Pivoting可用於解決關係除法問題。先通過建立表t並填充資料。

CREATE TABLE t1 (orderid VARCHAR(10) NOT NULL,productid INT NOT NULL,PRIMARY KEY(orderid,productid));

INSERT INTO t1 SELECT ‘A‘,1;INSERT INTO t1 SELECT ‘A‘,2;INSERT INTO t1 SELECT ‘A‘,3;INSERT INTO t1 SELECT ‘A‘,4;INSERT INTO t1 SELECT ‘B‘,2;INSERT INTO t1 SELECT ‘B‘,3;INSERT INTO t1 SELECT ‘B‘,4;INSERT INTO t1 SELECT ‘C‘,3;INSERT INTO t1 SELECT ‘C‘,4;INSERT INTO t1 SELECT ‘D‘,

表的內容如下表t儲存訂單中包含的產品，比如A訂單中包含的產品的ID為1、2、3、4，B訂單中所包含的產品為2、3、4，以此類推。這是一個比較典型的關係除法問題。用Pivoting技術可以反訂單中的產品旋轉到單獨的列中。例如要查詢包含productid為2、3、4的訂單，可以採用如下方法：

SELECT orderidFROM (SELECT orderid,MAX(CASE WHEN productid=2 THEN 1 END) AS p2,MAX(CASE WHEN productid=3 THEN 1 END) AS P3,MAX(CASE WHEN productid=4 THEN 1 END) AS p4FROM t1GROUP BY orderid) AS PWHERE p2=1 AND p3=1 AND p4=1;

上述語句返回“A”和“B”。如果單獨運行子查詢，將會得到每個訂單對應的產品ID，得到的結果如下

對於這個問題，彙總函式可以使用COUNT來替換MAX，這會讓派生表的結果顯得更加直觀。此時若產品存在則返回1，不存在則返回0而不是NULL，故SQL語句可調整為 

SELECT orderidFROM (SELECT orderid,COUNT(CASE WHEN productid=2 THEN 1 END) AS p2,COUNT(CASE WHEN productid=3 THEN 1 END) AS P3,COUNT(CASE WHEN productid=4 THEN 1 END) AS p4FROM t1GROUP BY orderid) AS PWHERE p2=1 AND p3=1 AND p4=1;

 

格式化彙總函式Privoting技術還可以用來 格式化彙總資料，一般用於報表的展現。為了示範用Pivoting技術來格式化，下面給出一個例子。先通過建立表t2並填充資料

CREATE TABLE t2 (orderid INT NOT NULL,orderdate DATE NOT NULL,empid INT NOT NULL,custid VARCHAR(10) NOT NULL,qty INT NOT NULL,PRIMARY KEY (orderid,orderdate));

INSERT INTO t2 SELECT 1,‘2010-01-02‘,‘3‘,‘A‘,10;INSERT INTO t2 SELECT 2,‘2010-04-02‘,‘2‘,‘B‘,20;INSERT INTO t2 SELECT 3,‘2010-05-02‘,‘1‘,‘A‘,30;INSERT INTO t2 SELECT 4,‘2010-07-02‘,‘3‘,‘D‘,40;INSERT INTO t2 SELECT 5,‘2011-01-02‘,‘4‘,‘A‘,20;INSERT INTO t2 SELECT 6,‘2011-01-02‘,‘3‘,‘B‘,30;INSERT INTO t2 SELECT 7,‘2011-01-02‘,‘1‘,‘C‘,40;INSERT INTO t2 SELECT 8,‘2009-01-02‘,‘2‘,‘A‘,10;INSERT INTO t2 SELECT 9,‘2009-01-02‘,‘3‘,‘B‘,20;

表的內容如下可以將表t2看作一張匯總表，比如網上商城的購物明細。這份匯總表顯示了訂單號、訂單日期、員工編號、消費者編號和訂單數量。要在此匯總表的基礎上進一步統計每個月消費者每年的數量，可能會想到分組來得到結果，例如

SELECT custid,YEAR(orderdate) AS year,SUM(qty) AS sum_qtyFROM t2 GROUP BY custid,YEAR(orderdate)

運行結果如下上述結果沒有任何問題，只是顯示的可能不夠直觀。如果可以通過旋轉得到輸出結果，那就直觀和清晰多了。這裡同樣可以使用Pivoting技術。與之前唯一的不同是，此處不再使用彙總函式MAX，而是使用SUM函數。這個解決方案的SQL語句如下：

SELECT custid,       IFNULL(SUM(CASE WHEN orderyear=2009 THEN qty END),0) AS ‘2009‘,       IFNULL(SUM(CASE WHEN orderyear=2010 THEN qty END),0) AS ‘2010‘,       IFNULL(SUM(CASE WHEN orderyear=2011 THEN qty END),0) AS ‘2011‘       FROM (SELECT custid,YEAR(orderdate) AS orderyear,qty FROM t2) AS pGROUP BY custid;

上述SQL語句中的IFNULL函數用來將NULL值返回為0，代表該年消費者沒有產生任何訂單操作。使用Pivoting技術來格式化彙總資料會遇到一個問題，即當旋轉的元素非常多時，會產生較長的查詢字串。要縮短查詢的字元長度，可以預先產生一張矩陣表，包含每個要旋轉列的屬性，運行如下語句建立並填充矩陣表Matrix

CREATE TABLE Matrix (orderyear INT PRIMARY KEY,y2009 INT NULL,y2010 INT NULL,y2011 INT NULL);

INSERT INTO Matrix SELECT 2009,1,0,0;INSERT INTO Matrix SELECT 2010,0,1,0;INSERT INTO Matrix SELECT 2011,0,0,1;

矩陣表Matrix的內容如下因此可以通過將表t和表maxtrix進行串連把原來的：SUM(CASE WHEN orderyear=n THEN qty END) AS N替換為SUM（qty*yN) AS N完整的SQL查詢語句為：

SELECT custid,    SUM(qty*y2009) AS ‘2009‘,    SUM(qty*y2010) AS ‘2010‘,    SUM(qty*y2011) AS ‘2011‘ FROM (SELECT custid,YEAR(orderdate) AS orderyear,qty FROM t2) AS OINNER JOIN Matrix AS P    ON O.orderyear=P.orderyearGROUP BY custid;

運行結果如下Unpivoting可以將Unpivoting看做Pivoting的反向操作，即將列旋轉為行。要完成這個樣本，需要根據下列語句建立並填充表p，

CREATE TABLE p (custid VARCHAR(10) NOT NULL,y2009 INT NULL,y2010 INT NULL,y2011 INT NULL,PRIMARY KEY (custid));

INSERT INTO pSELECT    custid,    IFNULL(SUM(CASE WHEN orderyear=2009 THEN qty END), 0) AS ‘2009‘,    IFNULL(SUM(CASE WHEN orderyear=2010 THEN qty END), 0) AS ‘2010‘,    IFNULL(SUM(CASE WHEN orderyear=2011 THEN qty END), 0) AS ‘2011‘FROM(SELECT custid, YEAR(orderdate) AS orderyear, qtyFROM t2 ) AS PGROUP BY custid;

 這裡把t2表返回後的內容匯入到表p中，如果想得到t2表直接彙總得到的結果，這個問題就變成了Unpivoting問題。解決這個問題需要將列旋轉為行。這裡使用的技巧是對每行資料產生3個副本，每個副本產生一個需要旋轉的列，這個過程可以通過如下的CROSS JOIN來完成。

SELECT * FROM    p,    (SELECT 2009 AS orderyear    UNION ALL SELECT 2010    UNION ALL SELECT 2011) AS o

得到以下結果接著問題就簡單了，只需根據orderyear列來取得對應旋轉列的值，例如： 

CASE orderyearWHEN 2009 THEN y2009WHEN 2010 THEN y2010WHEN 2011 THEN y2011END AS qty

因此這個Unpivoting問題的解決方案如下

SELECT custid,orderyear, CASE orderyearWHEN 2009 THEN y2009WHEN 2010 THEN y2010WHEN 2011 THEN y2011END AS qtyFROM    p,    (SELECT 2009 AS orderyear    UNION ALL SELECT 2010    UNION ALL SELECT 2011) AS o

若要得到最終結果，則還需要過濾qty等於0的情況，因此最終的解決方案為: 

SELECT custid,orderyear,qtyFROM (SELECT custid,orderyear, CASE orderyearWHEN 2009 THEN y2009WHEN 2010 THEN y2010WHEN 2011 THEN y2011END AS qtyFROM    p,    (SELECT 2009 AS orderyear    UNION ALL SELECT 2010    UNION ALL SELECT 2011) AS o) AS MWHERE qty <> 0

mysql列反轉Pivoting