详谈转置 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, '