问题概要
? ?在日常工作中时常会遇到将数据表的行列进行转换的问题. SQL提供了静态转置的功能主和透视,但适用范围很受限,要用SQL实现一些比较复杂的转置功能常常会遇到语句过于复杂的问题,而且缺少一个标准的解决思路。而集算器的SPL语言,则因其语法的灵活和函数库的丰富,可以完美地弥补SQL在这方面的不足。
? ?下面则通过实例详细阐述一下转置功能的实现。
基础篇
一、行转列
1,数据库的主
? ?主并不是从一开始就存在的功能,只有主流大数据库厂商较新版本产品,例如Oralce 11 g以上或SqlServer2005以上,才支持这个功能。
? ?从名称中可以猜的到,这个功能是实现行与列的转换,也就是将行中的值作为列名。但是,数据库的行,列,与普通的表格不一样,不能直接轴将X与Y轴相互对掉就算大功告成。究其原因,数据库的列是有唯一性的(也就是列名是不能重复的),而行中存储的是动态的数据,如果不作为主键,就是可以重复的,所以,主的实际应用,基本都要跟随在分组聚合运算之后,通过分组把用于转置的列(通常都是维度)中每一行数据都处理成不重复的值后,再将各行的值作为列名来展开。
? ?从具体应用来看,主的作用,其实就是将某一列的聚合结果,细分为多个更具体的列的聚合结果,以达到更直观的视觉效果。
? ?光说概念是不是比较枯燥,不容易理解?下面我们就以一个具体事例说明,比如Oracle数据库中有一个学生成绩表(StudentScore):
? ?
? ?如果想统计每个班的各科最高分,传统的做法是:
<>之前选择类,主题,马克斯(分数)THE_HIGHEST_SCORE STUDENTSCORE group by类,选择类主题,主题,马克斯(分数)THE_HIGHEST_SCORE STUDENTSCORE group by类,主题
? ?
? ?上面的结果可以说观感非常不好:首先,在“班级”一列里,一班,二班重复出现,很容易就让人看错行;其次,在“科目”一列里,语文、数学和英语三个科目都放在一起,然而实际上这三个科目的最高分并没有什么比较的意义。
? ?事实上,我们应该更希望看到以下这样的结果:
? ?
? ?这个结果中,把科目这一列中的三个科目,各自分离出来单独作为一列,既减少了无用的重复,又明确了各科目最高分之间的相互独立性,看上去清晰明了了很多。
? ?可以说,主就是为了这个目的而诞生的,为了实现上面的结果,现在的查询写法如下:
select * from(从STUDENTSCORE选择类、学科分数)主(max(分数)主题(MAX_MATHS“数学”,“英语”MAX_ENGLISH,‘中国’MAX_CHINSES)) select * from(从STUDENTSCORE选择类、学科分数)主(max(分数)主题(MAX_MATHS“数学”,“英语”MAX_ENGLISH,‘中国’MAX_CHINSES))
2,集算器的主
? ?有的同学可能会问,既然数据库中已经有了主,那为什么我还需要集算器的主呢?
? ?答案是:首先,不是所有的数据库都提供主;其次,就算所有的数据库都提供主,但如果是汇总了多个数据库的数据后还想再来个主?那还是要用到集算器的主。
? ?下面我们来看集算器的主如何使用
A1=连接(orcl) 2=A1。查询(“选择类,主题,马克斯(分数)THE_HIGHEST_SCORE STUDENTSCORE group by类,主体”)3=A2.pivot (THE_HIGHEST_SCORE类;主题;“数学”:“MAX_MATHS”、“英语”:“MAX_ENGLISH”、“中国”:“MAX_CHINESE”)
? ?代码说明:
? ?A1:第一步连接数据库
? ?A2:第二步提取数据做预处理(这一步可进一步扩展为做汇总或聚合等复杂的计算,具体方法请参考相关文章)
? ?A3:第三步即实现主的列转行功能并呈现出来,其效果与甲骨文的主是完全一样的。
? ?
3,主的其他意义
? ?除了数据呈现需求,将行转为列后,还可以使用列间的计算方法。因为列与行的属性不同,有些列间的计算要在行间实现会比较繁琐。比如学校对班级成绩的某种考核评比,数,外,语三科的权重分别是:0.6、0.3和0.1,用两个班的三科平均分来计算评比指标:
A1=连接(orcl) 2=A1。查询(“选择类,主题,avg(分数)AVG_SCORE STUDENTSCORE group by类,主体”)3=A2.pivot (AVG_SCORE类;主题;“数学”、“英语”,“中国”)4=A3。新(类(数学* 0.6 * 0.3 +英语+中国* 0.1):评估)
