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操作，可以方便的解决表中多个相同性质列的重构问题。