一、UNPIVOT概述

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

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

UNPIVOT是廣泛應(yīng)用在數(shù)據(jù)倉庫和BI(商業(yè)智能)場景中的，常用于將擁有多個相同性質(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實例

下面展示一個UNPIVOT的應(yīng)用實例，假設(shè)我們有一個包含各個國家2020年1-12月份旅游人數(shù)的表。我們需要將所有旅游人數(shù)合并成為一個列，可以使用UNPIVOT來完成。具體實現(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擴展知識

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

1.包含NULLS的情況

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

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)待合并的列的類型不同時，列的類型將自動轉(zhuǎn)換成相同的類型，比如，等號右邊的列都是NUMBER類型，而等號左邊的列是VARCHAR2類型，UNPIVOT操作后country列自動轉(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)置實現(xiàn)UNPIVOT操作

如果數(shù)據(jù)集本身沒有支持UNPIVOT的操作，我們可以使用UNION ALL和行列轉(zhuǎn)置來實現(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ù)據(jù)倉庫和BI領(lǐng)域的開發(fā)者，UNPIVOT是一個必須掌握的SQL操作，可以方便的解決表中多個相同性質(zhì)列的重構(gòu)問題。