大厂面试高阶SQL题之 开窗+聚合+行列转换（函数）

作为一个职场SQL--BOY，会写一些高难度的SQL是必备的技能之一，

下面给大家分享上海某基金公司（全国top10以内）大数据开发岗位笔试SQL题之一：

大厂面试SQL题：

写一段sql把student_score表（图1）的数据转化为图2展示出来

图1：

student_score

name（姓名）	subject（科目）	score（分数）
小帅	语文	67
小美	语文	50
小黑	语文	67
小明	语文	20
大林	数学	76
大红	数学	68
孟川	数学	36
七月	数学	36

图2：

结果展示

subject (科目)	max_score (最高分者)	min_score (最低分者)	gap (相差)
语文	小帅，小黑	小明	47
数学	大林	孟川，七月	40

emmmm这个是我之前遇到的笔试真题之一，说难也难，但你实力强劲，这就刚好给你拿来热身。

--------------------------------------------------------------------------------------------------------------------------------

大家可以先花三分钟时间，想想如何解题，整理出解题思路。

--------------------------------------------------------------------------------------------------------------------------------

大致思路：

        原表subject字段，语文，数学有很多条记录，但是结果展示subject字段中语文，数学就分别只有一条，很明显，这是要咱聚合。  

        怎么聚合呢？先看语文科目中，第二个max字段，有（小帅，小黑），第三个min字段，有（小明），很明显，max是把成绩第一名的给聚合到一块了，min是把最后一名的给聚合到一块了，然后相差67-20=47。很明显，聚合前，需要把第一名和最后一名先筛选出来。

        表1中的姓名name本来是一列，但是在结果展示中变为2列，很明显，聚合之后要进行 行列转换。

好了，大致思路出来了，接下来就是要按照思路呈现自己的结果了 。

步骤一：排序开窗筛选

因为它是按照第一名，和最后一名的给聚合到一块了，很明显，咱当务之急是要把正一倒一名给排序筛选出来, 使用开窗函数，按照科目分组，分数排序。(注意，此处有一个难点，出现排序并列这类情况，用哪个开窗函数呢？）。这里用哪个开窗函数是有讲究的，相信大部分小伙伴还没有用过rank和dense_rank。

tmp1

name	subject	score	RN1 (倒序）	RN2 (正序)
小帅	语文	67	1	3
小黑	语文	67	1	3
小明	语文	20	3	1
大林	数学	76	1	3
孟川	数学	36	3	1
七月	数学	36	3	1

代码如下：

select  t2.*
from 
         (select  t1.name
                    , t1.subject
                    , t1.score
                    , dense_rank()over(partiton by subject order by score desc)  as RN1
                    , dense_rank()over(partiton by subject order by score asc  )  as RN2
          from  student_score  t1 )  t2
where  t2.RN1=1  or t2.RN2=1   ;

代码图片如下：

步骤二：聚合（难点）

然后第二步应该是要咱（按照科目，分数）聚合了，但为了后面方便行列转换（要依据哪个是最高分最低分），还需要排序字段来定位，排序字段暂时不舍去，所以聚合之后最好还是可以展现RN1，RN2字段。（在这里就考验你对数据的敏感性了，因为要知道需要对什么进行group by，往往是有难度的）

可以用group_concat函数，当然也可以用 concat_ws(',' ,collect_list) 来求解。

tmp2

subject	name	score	RN1	RN2
语文	小帅，小黑	67	1	3
语文	小明	20	3	1
数学	大林	76	1	3
数学	孟川，七月	36	3	1

代码如下： 

select     t1.subject 
              ,t1.score
              ,t1.RN1
              ,t2.RN2     
              ,group_concat(t1.name)
from    tmp1  t1
group by t1.subject 
              ,t1.score
              ,t1.RN1
              ,t2.RN2    ；

代码图片如下：

步骤三：行列转换

通过步骤二的求解，已经得到聚合后的结果，仔细看看最终的数据，发现姓名的字段（最高分者，最低分者）出现了两列，并且分数字段是gap（其实就是相当于两个字段，一个最高分，一个最低分，然后做减法减出来的）也是两列。说明了对步骤二进行行列转换。

tmp3

subject （科目）	max_score （最高分者）	min_score （最低分者）	gap （相差）
语文	小帅，小黑	小明	47
数学	大林	孟川，七月	40

代码如下：

select subject
         ,max(case when RN1=1 then name else '' end)  as max_score
         ,max(case when RN2=1 then name else '' end)  as min_score
         ,sum(case when RN1=1 then score else score*(-1) end )  as gap
from tmp2
grooup by subject ; 

代码图片如下：

------------------------------------------------------------------------------------------------------------------------------

好了，上面的三个步骤已经给大家拆解开了，但笔试sql要求是要一条sql解决，这个大家按照思路的结果集套个子查询就好了。

结语：

大厂笔试题，都是要求有很高的数据分析能力，以及数据的敏感性（特别是步骤二），这个是必备素养之一。

上海大数据行业的圈子就这么小，希望这个你可以学会，增加一丝笔试的胜率与从容，毕竟如果笔试都通过不了，也就无缘面试了。