如何用Pivot實現行列轉換

作者：佚名 2015-07-22 12:42:36

在Oracle中，如果要實現行列轉換，較為常見的是用DECODE和CASE語句。對于簡單的行列轉行，DECODE和CASE語句尚能應付。在邏輯比較復雜，分組聚合較多的場景中，DECODE和CASE語句則力有不逮。而pivot則可完美解決這一切。

　　首先，我們來看看Oracle對于其的解釋：

　　可見，pivot是數據倉庫中的關鍵技術，它利用交叉查詢(crosstabulation query)將行轉換為列。

　　基本語法如下：

SELECT .... 
FROM <table-expr> 
   PIVOT 
     ( 
      aggregate-function(<column>) 
      FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>) 
        ) AS <alias> 
WHERE .....

　　下面我們來通過具體的案例對其進行闡述。

　　首先，構造案例所需的數據，

　　1> 創建視圖，以EMP表的數據作為源數據。

CREATE VIEW emp_view AS 
SELECT 
 deptno,job,to_char(hiredate,'yyyy') hiredate, 
 count(*) cnt,sum(sal) sum_sal 
FROM emp 
GROUP BY deptno,job,to_char(hiredate,'yyyy');

　　其中，deptno為部門號，job為工作的類型(即工種)，hiredate為雇傭的日期，cnt為特定部門，特定工種在特定年份雇傭的員工的總數，sum_sal為特定部門，特定工種，特定年份雇傭的員工的工資的總和。

　　2> 視圖的數據如下：

SQL> select * from emp_view; 
 
    DEPTNO JOB       HIRE        CNT    SUM_SAL 
---------- --------- ---- ---------- ---------- 
CLERK     1980          1        800 
ANALYST   1981          1       3000 
ANALYST   1987          1       3000 
CLERK     1981          1        950 
MANAGER   1981          1       2850 
MANAGER   1981          1       2450 
SALESMAN  1981          4       5600 
MANAGER   1981          1       2975 
PRESIDENT 1981          1       5000 
CLERK     1982          1       1300 
CLERK     1987          1       1100 
rows selected.

　　應用場景一：

　　基本的Pivot轉換

　　例1:

SELECT * FROM 
( SELECT deptno,hiredate,cnt 
  FROM emp_view 
 ) PIVOT (SUM(cnt) 
   FOR hiredate IN ('1980' AS "1980",'1981' AS "1981", 
                    '1982' AS "1982",'1987' AS "1987")) 
ORDER BY deptno; 
 
    DEPTNO       1980       1981       1982       1987 
---------- ---------- ---------- ---------- ---------- 
                    2          1 
         1          2                     2 
                    6 
rows selected.

　　例2：

SELECT * FROM 
( SELECT deptno,job,cnt 
  FROM emp_view 
 ) PIVOT (SUM(cnt) 
   FOR job IN ('CLERK','ANALYST','MANAGER','SALESMAN','PRESIDENT')) 
ORDER BY deptno; 
 
    DEPTNO    'CLERK'  'ANALYST'  'MANAGER' 'SALESMAN' 'PRESIDENT' 
---------- ---------- ---------- ---------- ---------- ----------- 
         1                     1                      1 
         2          2          1 
         1                     1          4 
rows selected.

　　兩例以不同的列進行統計，前者是hiredate，后者是job。

　　除此之外，前者用了別名，后面沒有用別名，兩者的顯示效果也是不一樣的。

　　應用場景二：

　　對多列進行Pivot轉換

SELECT * FROM 
( SELECT deptno,job,hiredate,cnt 
  FROM emp_view 
 ) PIVOT (SUM(cnt) 
            FOR (job,hiredate) IN 
              (('CLERK','1980') AS clerk_1980, 
               ('CLERK','1981') AS clerk_1981, 
               ('ANALYST','1987') AS analyst_1987, 
               ('MANAGER','1981') AS manager_1981 
              ) 
           ) 
ORDER by deptno; 
 
    DEPTNO CLERK_1980 CLERK_1981 ANALYST_1987 MANAGER_1981 
---------- ---------- ---------- ------------ ------------ 
                                              1 
         1                       1            1 
                    1                         1 
rows selected.

　　限于篇幅，FOR (job,hiredate) IN語句中沒有列出更多組合，只列出了四組，當然，我們可以根據實際場景需要羅列更多的組合。

　　從本例中可以看出，對兩個列進行Pivot轉換可從三個維度呈現統計結果。

　　應用場景三：

　　用Pivot實現多個聚合

SELECT * FROM 
( SELECT deptno,hiredate,cnt,sum_sal 
  FROM emp_view 
 ) PIVOT ( SUM(cnt) AS cnt, 
           SUM(sum_sal) AS sum_sal 
           FOR hiredate IN ('1980','1981','1982','1987')) 
ORDER BY deptno; 
 
    DEPTNO '1980'_CNT '1980'_SUM_SAL '1981'_CNT '1981'_SUM_SAL '1982'_CNT '1982'_SUM_SAL '1987'_CNT '1987'_SUM_SAL 
---------- ---------- -------------- ---------- -------------- ---------- -------------- ---------- -------------- 
                                   2           7450          1           1300 
         1            800          2           5975                                    2           4100 
                                   6           9400 
rows selected.

　　'1981'_CNT指的是1981年雇傭的員工的總數，'1981'_SUM_SAL指的是1981年雇傭員工所開出的工資。

　　具體到本例中，即1981年10號部門招聘了2位員工，開出的工資合計為7450元，20號部門招聘了2位員工，開出的工資合計為5975元，30號部門招聘了6名員工，開出的工資合計為9400元，依次類推。

　　既然有pivot將行轉換為列，同樣也有unpivot操作將聚合后的列轉換為行。

　　UNPIVOT

　　以上述應用場景三的結果作為源數據進行操作

CREATE TABLE T1 AS 
SELECT * FROM 
( SELECT deptno,hiredate,cnt,sum_sal 
  FROM emp_view 
 ) PIVOT ( SUM(cnt) AS cnt, 
           SUM(sum_sal) AS sum_sal 
           FOR hiredate IN ('1980' AS "1980",'1981' AS "1981", 
                            '1982' AS "1982",'1987' AS "1987")) 
ORDER BY deptno

　　表T1的結果為：

SQL> select * from t1; 
 
    DEPTNO   1980_CNT 1980_SUM_SAL   1981_CNT 1981_SUM_SAL   1982_CNT 1982_SUM_SAL   1987_CNT 1987_SUM_SAL 
---------- ---------- ------------ ---------- ------------ ---------- ------------ ---------- ------------ 
                                 2         7450          1         1300 
         1          800          2         5975                                  2         4100 
                                 6         9400 
rows selected.

　　首先進行一維unpivot

SELECT deptno,DECODE(hiredate,'1980_CNT','1980','1981_CNT','1981','1982_CNT','1982','1987_CNT','1987') AS hiredate,cnt 
FROM T1 
UNPIVOT INCLUDE NULLS 
( cnt 
  FOR hiredate IN ("1980_CNT","1981_CNT","1982_CNT","1987_CNT")); 
 
    DEPTNO HIRE        CNT 
---------- ---- ---------- 
1980 
1981          2 
1982          1 
1987 
1980          1 
1981          2 
1982 
1987          2 
1980 
1981          6 
1982 
1987 
rows selected.

　　輸出的結果為不同部門在不同年份的雇傭人數，

　　注意：上述SQL語句中UNPIVOT后加了INCLUDE NULLS，當然也可以指定為EXCLUDE NULLS，即排除cnt為空的值，如果不指定，則默認為EXCLUDE NULLS。

　　UNPIVOT后不指定INCLUDE NULLS的輸入結果為：

DEPTNO HIRE        CNT 
---------- ---- ---------- 
1981          2 
1982          1 
1980          1 
1981          2 
1987          2 
1981          6 
rows selected.

　　下面，我們再進行二維unpivot

SELECT deptno,hiredate,cnt,sum_sal 
FROM T1 
UNPIVOT 
( (cnt,sum_sal) 
  FOR hiredate IN (("1980_CNT","1980_SUM_SAL") AS 1980, 
                   ("1981_CNT","1981_SUM_SAL") AS 1981, 
                   ("1982_CNT","1982_SUM_SAL") AS 1982, 
                   ("1987_CNT","1987_SUM_SAL") AS 1987)); 
 
    DEPTNO   HIREDATE        CNT    SUM_SAL 
---------- ---------- ---------- ---------- 
      1981          2       7450 
      1982          1       1300 
      1980          1        800 
      1981          2       5975 
      1987          2       4100 
      1981          6       9400 
rows selected.

　　輸入結果為T1表列轉行的結果。

　　參考文檔：

　　SQL for Analysis and Reporting

責任編輯：honglu 來源：博客園

Pivot 行列轉換