如何用Pivot實(shí)現(xiàn)行列轉(zhuǎn)換
首先，我們來(lái)看看Oracle對(duì)于其的解釋：
可見(jiàn)，pivot是數(shù)據(jù)倉(cāng)庫(kù)中的關(guān)鍵技術(shù)，它利用交叉查詢(crosstabulation query)將行轉(zhuǎn)換為列。
基本語(yǔ)法如下：
- SELECT ....
- FROM <table-expr>
- PIVOT
- (
- aggregate-function(<column>)
- FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
- ) AS <alias>
- WHERE .....
下面我們來(lái)通過(guò)具體的案例對(duì)其進(jìn)行闡述。
首先，構(gòu)造案例所需的數(shù)據(jù)，
1> 創(chuàng)建視圖，以EMP表的數(shù)據(jù)作為源數(shù)據(jù)。
- 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為部門(mén)號(hào)，job為工作的類(lèi)型(即工種)，hiredate為雇傭的日期，cnt為特定部門(mén)，特定工種在特定年份雇傭的員工的總數(shù)，sum_sal為特定部門(mén)，特定工種，特定年份雇傭的員工的工資的總和。
2> 視圖的數(shù)據(jù)如下：
- 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.
應(yīng)用場(chǎng)景一：
基本的Pivot轉(zhuǎn)換
例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.
兩例以不同的列進(jìn)行統(tǒng)計(jì)，前者是hiredate，后者是job。
除此之外，前者用了別名，后面沒(méi)有用別名，兩者的顯示效果也是不一樣的。
應(yīng)用場(chǎng)景二：
對(duì)多列進(jìn)行Pivot轉(zhuǎn)換
- 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.
限于篇幅，F(xiàn)OR (job,hiredate) IN語(yǔ)句中沒(méi)有列出更多組合，只列出了四組，當(dāng)然，我們可以根據(jù)實(shí)際場(chǎng)景需要羅列更多的組合。
從本例中可以看出，對(duì)兩個(gè)列進(jìn)行Pivot轉(zhuǎn)換可從三個(gè)維度呈現(xiàn)統(tǒng)計(jì)結(jié)果。
應(yīng)用場(chǎng)景三：
用Pivot實(shí)現(xiàn)多個(gè)聚合
- 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年雇傭的員工的總數(shù)，'1981'_SUM_SAL指的是1981年雇傭員工所開(kāi)出的工資。
具體到本例中，即1981年10號(hào)部門(mén)招聘了2位員工，開(kāi)出的工資合計(jì)為7450元，20號(hào)部門(mén)招聘了2位員工，開(kāi)出的工資合計(jì)為5975元，30號(hào)部門(mén)招聘了6名員工，開(kāi)出的工資合計(jì)為9400元，依次類(lèi)推。
既然有pivot將行轉(zhuǎn)換為列，同樣也有unpivot操作將聚合后的列轉(zhuǎn)換為行。
UNPIVOT
以上述應(yīng)用場(chǎng)景三的結(jié)果作為源數(shù)據(jù)進(jìn)行操作
- 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的結(jié)果為：
- 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.
首先進(jìn)行一維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.
輸出的結(jié)果為不同部門(mén)在不同年份的雇傭人數(shù)，
注意：上述SQL語(yǔ)句中UNPIVOT后加了INCLUDE NULLS，當(dāng)然也可以指定為EXCLUDE NULLS，即排除cnt為空的值，如果不指定，則默認(rèn)為EXCLUDE NULLS。
UNPIVOT后不指定INCLUDE NULLS的輸入結(jié)果為：
- DEPTNO HIRE CNT
- ---------- ---- ----------
- 1981 2
- 1982 1
- 1980 1
- 1981 2
- 1987 2
- 1981 6
- rows selected.
下面，我們?cè)龠M(jìn)行二維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.
輸入結(jié)果為T(mén)1表列轉(zhuǎn)行的結(jié)果。
參考文檔：
SQL for Analysis and Reporting
