详谈转置 pivot

问题概要

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

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

基础篇

一、行转列

1、数据库的 pivot

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

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

  从具体应用来看，pivot 的作用，其实就是将某一列的聚合结果，细分为多个更具体的列的聚合结果，以达到更直观的视觉效果。

  光说概念是不是比较枯燥，不容易理解？下面我们就以一个具体事例说明，比如 Oracle 数据库中有一个学生成绩表（StudentScore）：

  

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

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

  

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

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

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

  可以说，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))    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 如何使用

	A
1	=connect(“orcl”)
2	=A1.query(“select CLASS,SUBJECT,max( SCORE) THE_HIGHEST_SCORE from STUDENTSCORE group by CLASS,SUBJECT”)
3	=A2.pivot(CLASS;SUBJECT,THE_HIGHEST_SCORE;”Maths”:”MAX_MATHS”,”English”:”MAX_ENGLISH”, “Chinese”:”MAX_CHINESE”)

  代码说明：
  A1：第一步连接数据库
  A2：第二步提取数据做预处理 (这一步可进一步扩展为做汇总或聚合等复杂的计算，具体方法请参考相关文章)
  A3：第三步即实现 pivot 的列转行功能并呈现出来，其效果与 Oracle 的 pivot 是完全一样的。
  

3、pivot 的其他意义

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

	A
1	=connect(“orcl”)
2	=A1.query(“select CLASS,SUBJECT,avg(SCORE) AVG_SCORE from STUDENTSCORE group by CLASS, SUBJECT”)
3	=A2.pivot(CLASS;SUBJECT,AVG_SCORE; “Maths”,”English”,”Chinese”)
4	=A3.new(CLASS,(Maths *0.6+ English *0.3+ Chinese *0.1):Assessment)

  计算结果：
  

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

二、 列转行

1、数据库的 unpivot

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

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

  

  如果想知道的是每个人最擅长哪个科目（也就是每个人的哪一科得分最高），行间计算时用 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    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 或是子查询之类的来间接实现类似功能么？

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

	A
1	=connect(“orcl”)
2	=A1.query(“select * from PERSONALSCORE”)
3	=A2.pivot@r(NAME; SUBJECT, SCORE; MATHS:”MATHS”, ENGLISH:”ENGLISH”, CHINESE:”CHINESE”)
4	=A3.group(NAME).(~.top@1(-1; SCORE))
5	=A4.new(NAME,SUBJECT:Good_Subject,SCORE:Good_Subject_Score)

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

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

高级篇

一、 双向转置

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

  

  而我们想要得到类似下面结构的学生成绩表（含义是查看某个学生某科目的成绩变化趋势）：

NAME	SUBJECT	TERM 1	TERM 2
Zhangsan	MATHS	99	87

  这里，首先要将数学、语文等列合并成科目列，需要列转行的操作；而要将学期列拆分成学期一、学期二等列，需要行转列的操作。

  考虑到数据表的结构一般是行数远大于列数，所以我们可以先进行列转行，再进行行转列。由于本表的原始数据在行列转换后数据与转换前的表中数据可以一一对应（不需要计算聚合），因此使用集算器的 pivot@r 和 pivot 函数显然会更方便。

	A
1	=connect(“orcl”)
2	=A1.query(“select * from SCORE”)
3	=A2.pivot@r(NAME,TERM;SUBJECT,SCORE)
4	=A3.pivot(NAME,SUBJECT;TERM,SCORE)

  运行结果：
  

二、 动态列转置

  上面举的例子都属于静态转置，要求处理的表格和数据都是“规规矩矩”的。但实际业务中却总有不那么守规矩的异类存在，而且相信数量还不少，这时用 SQL 不管是 pivot/unpivot、还是 case when，还是别的啥，都有点力不从心……那该怎么办？这时集算器的优势就体现出来了：

  比如有下面一个记录收入情况的个人收入表（PersonalIncome）

  

  但我们想得到一个类似下面结构的表

  MANE INCOME_SOURCE_1 INCOMR_AMOUNT_1 INCOME_SOURCE_2 INCOMR_AMOUNT_2 ……

  Zhangsan Wages 8000 Stock 6000 ……

  我们不确定行转列后，列的数量，甚至连列名也不能完全确定。这时就不能使用只适用于静态转置的 pivot 函数了，而需要使用动态转置的方法。而集算器的 SPL 语言在动态编程方面，要远比 SQL 语言灵活得多：

	A	B
1	=connect(“orcl”)
2	=A1.query(“select * from PERSONALINCOME”).group(NAME)
3	=A2.max(~.len())
4	=create(NAME, ${A3.(“INCOME_SOURCE_”+string(~)+”, INCOME_SOURCE_”+string(~)).concat@c()})
5	for A2	=A5. NAME |A5.conj([INCOME_SOURCE, INCOME_AMOUNT])
6		>A4.record(B5)

  结果如下：
  

三、 转置同时列间计算

  假设我有一张关于蔬菜的一周价格清单

  

  而我想由此计算得出关于各种蔬菜的一周价格走势，其中走势又包含四种状态：上涨、下降、平稳和初始（周一的值）。

  设计出来的表结构大体如下

  VEGETABLES Monday Tuesday Wednesday Thursday ……

  Eggplant Initial Rise Decline Rise ……

  Cucumber Initial Rise Rise ……

  ……

  虽然需要使用的转置属于静态类型，但在转置时需要实现列间的计算，这种计算对于 SQL 来说，处理起来非常麻烦。但若使用灵活性更强的集算器的 SPL 语言，则会轻松许多：

	A	B
1	=connect(“orcl”)	=[“Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”,”Sunday”]
2	=A1.query(“select * from VEGETABLEPRICES”)
3	=create(Vegetables, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
4	for A2.group(VEGETABLES)	=A4.align(B1, WEEK)
5		=B4.(if(#==1:”Initial”, PRICE_RMB > PRICE_RMB [-1]:”Rise”, PRICE_RMB < PRICE_RMB [-1]:”Decline”,PRICE_RMB = PRICE_RMB [-1]:”Stable”))
6		>A3.record(A4.VEGETABLES |B5)

  获得“蔬菜的一周价格走势”表如下
  

总结

  相比于 SQL 提供的 pivot 和 unpivot，集算器 SPL 语言所提供的转置功能要更加灵活，适应性也更加广泛，可以满足各种复杂的转置需求。