浅谈SQL行列转置

SQL干货分享 2021-04-19

3902

（CSDN博主：写代码也要符合基本法）

马瘦毛长蹄子肥

儿子偷爹不算贼

瞎老头儿娶个瞎大奶奶

老两口过了半辈子

谁也没见过谁

大家好，我实在是写不出来开头了，就让我们直接假设吧

假设我是 Scott，今天老板让我统计一下我司三大部门十四名员工入职年份的分布，于是我轻轻松松写了一段代码

SQL> SELECT d.dname, to_char(e.hiredate, 'yyyy') hireyear, COUNT(1)
  2    FROM emp e, dept d
  3   WHERE d.deptno = e.deptno
  4   GROUP BY d.dname, to_char(e.hiredate, 'yyyy');


DNAME          HIREYEAR   COUNT(1)
-------------- -------- ----------
RESEARCH       1983              1
RESEARCH       1981              2
SALES          1981              6
RESEARCH       1982              1
ACCOUNTING     1981              2
ACCOUNTING     1982              1
RESEARCH       1980              1


7 rows selected

老板看后非常开心，然后因为我没有把数据按照部门和年份两个维度整理成报表扣了我半个月奖金

想要实现数据的维度整合，我们会用下面这样逐层分组的方式实现

SQL> SELECT dname
  2        ,SUM(decode(hireyear, 1980, cnt)) cnt_80
  3        ,SUM(decode(hireyear, 1981, cnt)) cnt_81
  4        ,SUM(decode(hireyear, 1982, cnt)) cnt_82
  5        ,SUM(decode(hireyear, 1983, cnt)) cnt_83
  6    FROM (SELECT d.dname, to_char(e.hiredate, 'yyyy') hireyear, COUNT(1) cnt
  7            FROM emp e, dept d
  8           WHERE d.deptno = e.deptno
  9           GROUP BY d.dname, to_char(e.hiredate, 'yyyy'))
 10   GROUP BY dname;


DNAME              CNT_80     CNT_81     CNT_82     CNT_83
-------------- ---------- ---------- ---------- ----------
ACCOUNTING                         2          1 
RESEARCH                1          2          1          1
SALES                              6

如果有 N 个维度，一般需要通过 N 层分组来实现数据整合

很多旷世奇作让人拍案称奇之处就在于作品的精妙，环环相扣以致牵一发能动全身

但是这种精妙在代码界并不受欢迎，因为它的后果是极难维护

小刘曾就二开过一个前辈用这种通俗方法写的销售报表，光是这个主数据游标，就洋洋洒洒写了数百行，费尽千辛万苦读懂程序后，不由得感叹前辈心思之缜密，构思之精妙，草泥之马币

Oracle 11g 引入了 PIVOT 函数，相较于传统的逐层分组方法，很多资料中都称 PIVOT 函数为我们提供了一个优雅地进行行列转换的方案

比如上例我们使用 PIVOT 可以写作

SQL> SELECT *
  2    FROM (SELECT d.dname, to_char(e.hiredate, 'yyyy') hireyear
  3            FROM emp e, dept d
  4           WHERE d.deptno = e.deptno)
  5  pivot(COUNT(1)
  6     FOR hireyear IN(1980 AS cnt_80
  7                    ,1981 AS cnt_81
  8                    ,1982 AS cnt_82
  9                    ,1983 AS cnt_83));


DNAME              CNT_80     CNT_81     CNT_82     CNT_83
-------------- ---------- ---------- ---------- ----------
ACCOUNTING              0          2          1          0
RESEARCH                1          2          1          1
SALES                   0          6          0          0

代码结构立刻清爽了不少，下面我们再板板正正的看一下 PIVOT 的语法

以及轨道图

由于不同资料中的命名差别，轨道图中的 aggregate_function 对应语法结构图中的 pivot_clause

在 pivot_clause 中指定要进行聚集的列和函数，在 pivot_for_clause 中指定要进行分组和转置的列，在 pivot_in_clause 中限定结果的值范围，产生的每个值的聚集转换为单独一列

在 pivot_clause 和 pivot_in_clause 中都可以添加列别名，最终生成的列名将会是 pivot_for_clause 中的列别名使用下划线拼接 pivot_clause 中聚集列别名而成

事实上，PIVOT 支持在多个列上使用聚集。PIVOT 运算还会使用没有出现在 pivot_for_clause 中的列进行隐性 GROUP BY 运算

注意事项

任何仅在 pivot_clause 中引用的列，都不能出现在 SELECT 列表中
任何仅在 pivot_for_clause 中引用的列，都不能出现在 SELECT 列表中
pivot_clause 中的所有列都必须使用聚集函数

pivot_clause 中是要列出具体的值的，Oracle 并不支持动态的值列表。但如果使用 XML 关键字，生成的转置数据集就会以 XML 格式提供结果

SELECT *
  FROM (SELECT d.dname, to_char(e.hiredate, 'yyyy') hireyear
          FROM emp e, dept d
         WHERE d.deptno = e.deptno)
pivot xml(COUNT(1) AS cnt
   FOR hireyear IN(ANY));

Scott 乐呵呵的拿着转换好的数据给老板看，老板看后十分欣慰，然后就因为部门为纵轴而年份为横轴扣光了 Scott 这月的奖金

类似 PIVOT 的反转置（列转行）数据 UNPIVOT 并不是转置的逆运算

因为转置数据生成聚集，而反转置不能撤销转置生成的聚集

事情一旦发生了就是回不去的

这里我们将前例行转列后的结果创建为临时表 EMP_PIVOT

SQL> SELECT * FROM emp_pivot;


DNAME              CNT_80     CNT_81     CNT_82     CNT_83
-------------- ---------- ---------- ---------- ----------
ACCOUNTING              0          2          1          0
RESEARCH                1          2          1          1
SALES                   0          6          0          0

一般方法实现列转行

SQL> SELECT p.dname, 1980 hireyear, p.cnt_80 cnt
  2    FROM emp_pivot p
  3  UNION ALL
  4  SELECT p.dname, 1981 hireyear, p.cnt_81 cnt
  5    FROM emp_pivot p
  6  UNION ALL
  7  SELECT p.dname, 1982 hireyear, p.cnt_82 cnt
  8    FROM emp_pivot p
  9  UNION ALL
 10  SELECT p.dname, 1983 hireyear, p.cnt_83 cnt
 11    FROM emp_pivot p;


DNAME            HIREYEAR        CNT
-------------- ---------- ----------
ACCOUNTING           1980          0
RESEARCH             1980          1
SALES                1980          0
ACCOUNTING           1981          2
RESEARCH             1981          2
SALES                1981          6
ACCOUNTING           1982          1
RESEARCH             1982          1
SALES                1982          0
ACCOUNTING           1983          0
RESEARCH             1983          1
SALES                1983          0


12 rows selected

使用 UNPIVOT

SQL> SELECT *
  2    FROM emp_pivot
  3  unpivot(cnt FOR hireyear IN(cnt_80 AS 1980
  4                             ,cnt_81 AS 1981
  5                             ,cnt_82 AS 1982
  6                             ,cnt_83 AS 1983));


DNAME            HIREYEAR        CNT
-------------- ---------- ----------
ACCOUNTING           1980          0
ACCOUNTING           1981          2
ACCOUNTING           1982          1
ACCOUNTING           1983          0
RESEARCH             1980          1
RESEARCH             1981          2
RESEARCH             1982          1
RESEARCH             1983          1
SALES                1980          0
SALES                1981          6
SALES                1982          0
SALES                1983          0


12 rows selected

胜负一目了然

同理，语法结构图中的 unpivot_clause 对应轨道图中圈出的部分，unpivot_for_clause 对应轨道图中的 pivot_for_clause

在 unpivot_clause 中定义表示反转置值后的列名称，在 unpivot_for_clause 中定义反转置查询所得到的列的列名称，在 unpivot_in_clause 中定义要进行反转置的待转置列（不是值）的列表

如上例中，unpivot_in_clause 中罗列了要反转置的列，这些列名将成为 hireyear 列的值以表示每行数据是由哪列转置来的，而这些列中的值则对应到 cnt 列中展示

unpivot_in_clause 中给列加上别名可以将这些描述性的数据修改为与原始列名不同的名称

默认行为 EXCLUDE NULLS 会移除 unpivot_clause 所定义列中的空值，如果声明 INCLUDE NULLS 则不会筛选，最终将据有空值的数据行也选择出来

UNPIVOT 执行上还有一个限制：unpivot_in_clause 中引用的所有列应当具有一致的数据类型

参考文献：

[1] Morton, K. & K.Osborne., R.Sands., R.Shamsudeen., J.Still. Pro Oracle SQL 2nd Editin [M]. Berkeley: Apress, 2013:533, 536.

[2] Oracle Database. SQL Language Reference 11g Release 2(11.2) [S]. 2016-01.

数据库

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

浅谈SQL行列转置

评论