给定下面的模拟数据集,这也是SQL领域经典的学生成绩单题。预期的两个数据表如下:1)长表:scoreLong2)宽表:scoreWide考察的问题是通过SQL语句实现这两种形式的转换,其中长表转换为宽表,即就是行转成列,宽表转成列。转换长表的行。01Rowtocolumn:sum+if在rowtocolumn中,经典的解决方案是条件聚合,即sum+if组合。基本思路是这样的:在长表的数据组织结构中,同一个uid对应多行,即每门课程一条记录对应一组分数,但是在宽表中,需要改一下同一uid下只有一行对应长表中的一行,只有一列记录课程成绩,而在宽表中,每门课程作为一列记录成绩,从多行到一列row,那么直观的想法就是通过groupby进行聚合;从一列到多列,然后就涉及到导数提取;由于使用了groupby聚合,涉及到对多门课程的成绩进行汇总,但现在需要的不是所有成绩的汇总,而是每门课程的独立成绩,所以需要一个if函数来添加筛选和提取;当然也可以使用casewhen;在if筛选抽取的基础上,针对不同的课程设置不同的抽取条件,最后添加聚合函数,抽取该列的成绩。按照这个思路,实现行转列的SQL语句的写法如下:SELECTTuid,sum(if(course='Chinese',score,NULL))as`Chinese`,sum(if(course='Mathematics',score,NULL))as`Mathematics`,sum(if(course='English',score,NULL))as`English`,sum(if(course='Physics',score,NULL))as`Physics`,sum(if(course='Chemistry',score,NULL))as`Chemistry`FROMscoreLongGROUPBYuid查询结果当然是行转列后的预期结果:其中,if(course='Chinese',score,NULL)语句实现当且仅当课程为中文时,值为课程成绩,否则值为空,相当于推导出一个新的列字段,并且对于每个uid,只有特定课程的结果不为空,其余均为空。这样无论使用什么聚合函数,都可以得到uid下指定课程的成绩结果。这里使用了sum函数,min和max的实际效果是一样的,因为只有一个要聚合的值是非空的。02列转换:Union列转换是上述过程的逆过程,所以思路更直观:行记录由一行变为多行,列字段由多列变为单列;将一列改为单列,相当于一个堆叠的过程,实际上可以看成是复制;如果一行变成多行,那么复制最直观的实现方式当然是使用union,即为每门课程抽取一张衍生表,最后将所有课程的衍生表联合在一起即可,需要注意的是支付给字段的对齐。根据这个思路,给出SQL实现如下:SELECTuid,'Chinese'ascourse,`Chinese`asscoreFROMscoreWideWHERE`Chinese`ISNOTNULLUNIONSELECTuid,'mathematics'ascourse,`math`asscoreFROMscoreWideWHERE`Math`ISNOTNULLUNIONSELECTuid,'English'ascourse,`English`asscoreFROMscoreWideWHERE`English`ISNOTNULLUNIONSELECTuid,'physics'ascourse,`physics`asscoreFROMscoreWideWHERE`physics`ISNOTNULLUNIONESELECTuid,'chemistry'ascourse,`chemistry`asscoreFROMscoreWideWHERE`化学查询结果当然是L`ISNOT预期的长表。这里着重解释其中的三个细节:在每个单门课程的派生表中,比如这句话:SELECTuid,'Chinese'ascourse,`Chinese'asscore,单引号包裹的课程名是一个字符串常量,比如中文类的导数表中的课程名称就叫Chinese,然后给列命名为course;第二个用反引号包裹的课程名称实际上是从宽表中引用该列的值,然后将其命名为score。这其实对应一个知识点:在SQL中,字符串用单引号引起来(其实双引号也可以),而列字段名用反引号引起来。记录,这其实是因为原表中存在空值。如果不进行过滤,本例中最终查询的记录有10条,两条记录的score字段为空。最后,在本例中,使用union关键字实现了多表的垂直拼接。其实用unionall更合理。两者的区别在于union会完成记录的去重;而unionall是简单的拼接。确定没有重复或没有去重。更高的效率。
