Oracle Unpivot詳解

一、UNPIVOT概述

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

集合符合集合的語法，需要有一個SELECT語句作為基礎數據集，該基礎數據集中包含待合併的列，需要使用UNPIVOT來合併多個列。

UNPIVOT是廣泛應用在數據倉庫和BI(商業智能)場景中的，常用於將擁有多個相同性質列的表進行重構，也能夠將多張表中的相同欄位進行合併完整數據集。

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

二、UNPIVOT實例

下面展示一個UNPIVOT的應用實例，假設我們有一個包含各個國家2020年1-12月份旅遊人數的表。我們需要將所有旅遊人數合併成為一個列，可以使用UNPIVOT來完成。具體實現代碼如下：

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.不同類型列的合併

當待合併的列的類型不同時，列的類型將自動轉換成相同的類型，比如，等號右邊的列都是NUMBER類型，而等號左邊的列是VARCHAR2類型，UNPIVOT操作後country列自動轉換為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 和行列轉置實現UNPIVOT操作

如果數據集本身沒有支持UNPIVOT的操作，我們可以使用UNION ALL和行列轉置來實現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;

四、總結

本文詳細介紹了Oracle中的UNPIVOT操作，包括UNPIVOT的概述、應用實例和擴展知識。對於數據倉庫和BI領域的開發者，UNPIVOT是一個必須掌握的SQL操作，可以方便的解決表中多個相同性質列的重構問題。