pivot 自动增加数据（详谈转置pivot）

问题概要

在日常工作中时常会遇到将数据表的行列进行转换的问题。SQL 提供了静态转置的功能 pivot 和 unpivot，但适用范围很受限，要用 SQL 实现一些比较复杂的转置功能常常会遇到语句过于复杂的问题，而且缺少一个标准的解决思路。而集算器的 SPL 语言，则因其语法的灵活和函数库的丰富，可以完美地弥补 SQL 在这方面的不足。

下面则通过实例详细阐述一下转置功能的实现。

基础篇

一、行转列

1、数据库的 pivot

pivot 并不是从一开始就存在的功能，只有主流大数据库厂商较新版本产品，例如 Oralce 11g 以上或 SqlServer2005 以上，才支持这个功能。

从名称中可以猜到，这个功能是实现行与列的转换，也就是将行中的值作为列名。但是，数据库的行、列，与普通的表格不一样，不能直接将 X 轴与 Y 轴相互对掉就算大功告成。究其原因，数据库的列是有唯一性的（也就是列名是不能重复的），而行中存储的是动态的数据，如果不作为主键，就是可以重复的。所以，pivot 的实际应用，基本都要跟随在分组聚合运算之后，通过分组把用于转置的列（通常都是维度）中每一行数据都处理成不重复的值后，再将各行的值作为列名来展开。

如果想统计每个班的各科最高分，传统的做法是：

select CLASS,SUBJECT, max(SCORE) THE_HIGHEST_SCORE from STUDENTSCORE group by CLASS,SUBJECT

pivot 自动增加数据（详谈转置pivot）(1)

上面的结果可以说观感非常不好：首先，在“班级”一列里，一班、二班重复出现，很容易就让人看错行；其次，在“科目”一列里，语文、数学和英语三个科目都放在一起，然而实际上这三个科目的最高分并没有什么比较的意义。

事实上，我们应该更希望看到以下这样的结果：

pivot 自动增加数据（详谈转置pivot）(2)

这个结果中，把科目这一列中的三个科目，各自分离出来单独作为一列，既减少了无用的重复，又明确了各科目最高分之间的相互独立性，看上去清晰明了了很多。

可以说，pivot 就是为了这个目的而诞生的，为了实现上面的结果，现在的查询写法如下：

select * from (select CLASS, SUBJECT, SCORE from STUDENTSCORE) pivot (max( SCORE) for SUBJECT in ('Maths' as MAX_MATHS, 'English' as MAX_ENGLISH, 'Chinese' as MAX_CHINSES))

2、集算器的 pivot

有的同学可能会问，既然数据库中已经有了 pivot，那为什么我还需要集算器的 pivot 呢？

答案是：首先，不是所有的数据库都提供 pivot；其次，就算所有的数据库都提供 pivot，但如果是汇总了多个数据库的数据后还想再来个 pivot？那还是要用到集算器的 pivot。

下面我们来看集算器的 pivot 如何使用

pivot 自动增加数据（详谈转置pivot）(3)

代码说明：

A1：第一步连接数据库

A2：第二步提取数据做预处理 (这一步可进一步扩展为做汇总或聚合等复杂的计算，具体方法请参考相关文章)

A3：第三步即实现 pivot 的列转行功能并呈现出来，其效果与 Oracle 的 pivot 是完全一样的。

pivot 自动增加数据（详谈转置pivot）(4)

3、pivot 的其他意义

除了数据呈现需求，将行转为列后，还可以使用列间的计算方法。因为列与行的属性不同，有些列间的计算要在行间实现会比较繁琐。比如学校对班级成绩的某种考核评比，数、外、语三科的权重分别是：0.6、0.3 和 0.1，用两个班的三科平均分来计算评比指标：

pivot 自动增加数据（详谈转置pivot）(5)

计算结果：

pivot 自动增加数据（详谈转置pivot）(6)

上面的计算，假如要在行间实现，则会麻烦许多，有兴趣的同学可以自己试一下。

二、列转行

1、数据库的 unpivot

有行转列，自然就有列转行。还是以 Oracle 为例，它提供的列转行函数是 unpivot。

列转行的功能在业务上又有什么意义呢？我们来看这样一份个人成绩表（PersonalScore）：

pivot 自动增加数据（详谈转置pivot）(7)

如果想知道的是每个人最擅长哪个科目（也就是每个人的哪一科得分最高），行间计算时用 max 函数会很方便，而使用列间计算则相对比较繁琐。这时 unpivot 函数就派上用场了：

with T1 as (select * from PERSONALSCORE unpivot (SCORE for SUBJECT in (MATHS,ENGLISH, CHINESE))), T2 as (select NAME NAME, max(SCORE) The_Highest_Score from T1 group by NAME ) select T1.NAME NAME, T1.SUBJECT Good_Subject, T2.The_Highest_Score Good_Score_Score from T1 join T2 on T1.NAME = T2.NAME and T1.SCORE =T2.The_Highest_Score

2、集算器的 pivot@r

那么，如果使用的数据库不是 Oracle 怎么办？还需要研究新数据库的转置语法细节么？如果数据库不支持转置语句又怎么办？需要用 case when 或是子查询之类的来间接实现类似功能么？

不必如此烦恼！因为我们有集算器：

pivot 自动增加数据（详谈转置pivot）(8)

计算结果，二者是一样的（在排序上可能略有差异）：

pivot 自动增加数据（详谈转置pivot）(9)

另外，还需要注意一点：数据库的 unpivot 并不完全是 pivot 的逆运算，因为 pivot 语句中往往包含了聚合函数，而聚合计算本身是不可逆的，也就是说 unpivot 并不能将 pivot 聚合后的结果再还原回原先的详细数据。但是集算器的 pivot 因为并不参与聚合计算（聚合计算在 pivot 执行之前已经单独执行了），所以集算器的 pivot@r 可以说是集算器的 pivot 运算的逆运算。

高级篇

一、双向转置

有时需要一些更复杂的转置操作，比如有这样一个学生成绩表（Score）

pivot 自动增加数据（详谈转置pivot）(10)