当前位置: 代码迷 >> 综合 >> 详谈转置 pivot
  详细解决方案

详谈转置 pivot

热度:74   发布时间:2023-12-09 17:19:02.0

问题概要

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

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

基础篇

一、行转列

1、数据库的 pivot

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

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

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

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

??001png

??如果想统计每个班的各科最高分,传统的做法是:

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

??002png

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

??事实上,我们应该更希望看到以下这样的结果:
??003png

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

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

    select * from (select CLASS, SUBJECT, SCORE from STUDENTSCORE) pivot (max( SCORE) for SUBJECT in ('Ma
  相关解决方案