{首页主词},&

一、UNPIVOT概述

UNPIVOT 是 Oracle 中的一種操作，可以將多個(gè)列按照一定的規(guī)則合并成一個(gè)列。UNPIVOT的常用語法為：UNPIVOT (列名1, 列名2, ... 列名n) INCLUDE(NULLS) [AS] 集合

集合符合集合的語法，需要有一個(gè)SELECT語句作為基礎(chǔ)數(shù)據(jù)集，該基礎(chǔ)數(shù)據(jù)集中包含待合并的列，需要使用UNPIVOT來合并多個(gè)列。

UNPIVOT是廣泛應(yīng)用在數(shù)據(jù)倉庫和BI(商業(yè)智能)場(chǎng)景中的，常用于將擁有多個(gè)相同性質(zhì)列的表進(jìn)行重構(gòu)，也能夠?qū)⒍鄰埍碇械南嗤侄芜M(jìn)行合并完整數(shù)據(jù)集。


SELECT * FROM (
  SELECT deptno, job, sal, comm
  FROM emp
) 
UNPIVOT INCLUDE(NULLS) (
  SALARY
  FOR TYPE IN (SAL, COMM)
);

二、UNPIVOT實(shí)例

下面展示一個(gè)UNPIVOT的應(yīng)用實(shí)例，假設(shè)我們有一個(gè)包含各個(gè)國家2020年1-12月份旅游人數(shù)的表。我們需要將所有旅游人數(shù)合并成為一個(gè)列，可以使用UNPIVOT來完成。具體實(shí)現(xiàn)代碼如下：


CREATE TABLE tourism(
  country VARCHAR2(100),
  Jan NUMBER,
  Feb NUMBER,
  Mar NUMBER,
  Apr NUMBER,
  May NUMBER,
  Jun NUMBER,
  Jul NUMBER,
  Aug NUMBER,
  Sep NUMBER,
  Oct NUMBER,
  Nov NUMBER,
  Dec NUMBER
);

INSERT INTO tourism VALUES('China',100,200,300,400,500,600,700,800,900,1000,1100,1200);
INSERT INTO tourism VALUES('USA',200,400,600,800,1000,1200,1400,1600,1800,2000,2200,2400);
INSERT INTO tourism VALUES('Japan',150,300,450,600,750,900,1050,1200,1350,1500,1650,1800);

SELECT * FROM tourism;

SELECT country, visitors, month
FROM 
(
  SELECT *
  FROM tourism
) 
UNPIVOT INCLUDE(NULLS) (
  visitors
  FOR month IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
);

三、UNPIVOT擴(kuò)展知識(shí)

除了基本的UNPIVOT操作之外，還有一些特殊情況需要注意。

1.包含NULLS的情況

在UNPIVOT語句中，加入 INCLUDE(NULLS) 可以將列值為空的情況也納入U(xiǎn)NPIVOT操作中。


SELECT country, visitors, month
FROM 
(
  SELECT *
  FROM tourism
) 
UNPIVOT INCLUDE(NULLS) (
  visitors
  FOR month IN (Jan, Feb, Mar, Apr, May, NULL, NULL, NULL, Sep, Oct, Nov, Dec)
);

2.不同類型列的合并

當(dāng)待合并的列的類型不同時(shí)，列的類型將自動(dòng)轉(zhuǎn)換成相同的類型，比如，等號(hào)右邊的列都是NUMBER類型，而等號(hào)左邊的列是VARCHAR2類型，UNPIVOT操作后country列自動(dòng)轉(zhuǎn)換為NUMBER類型。


SELECT *
FROM 
(
  SELECT *
  FROM tourism
) 
UNPIVOT INCLUDE(NULLS) (
  tourists, country
  FOR month IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
);

3.使用UNION ALL 和行列轉(zhuǎn)置實(shí)現(xiàn)UNPIVOT操作

如果數(shù)據(jù)集本身沒有支持UNPIVOT的操作，我們可以使用UNION ALL和行列轉(zhuǎn)置來實(shí)現(xiàn)UNPIVOT操作。


SELECT country, visitors, 'Jan' AS month
FROM tourism
UNION ALL
SELECT country, visitors, 'Feb' AS month
FROM tourism
UNION ALL
...
SELECT country, visitors, 'Dec' AS month
FROM tourism;

四、總結(jié)

本文詳細(xì)介紹了Oracle中的UNPIVOT操作，包括UNPIVOT的概述、應(yīng)用實(shí)例和擴(kuò)展知識(shí)。對(duì)于數(shù)據(jù)倉庫和BI領(lǐng)域的開發(fā)者，UNPIVOT是一個(gè)必須掌握的SQL操作，可以方便的解決表中多個(gè)相同性質(zhì)列的重構(gòu)問題。

Oracle Unpivot詳解