面试官:有如下学生成绩表:students_grades
查询每门课程grades前两名的学生姓名及成绩,要求输出列格式如下:
name, number, course, grade
这里需要使用开窗函数/分析函数:over()
什么是开窗函数:
开窗函数/分析函数:over()
开窗函数也叫分析函数,有两类:一类是聚合开窗函数,一类是排序开窗函数。
开窗函数的调用格式为:
函数名(列名) OVER(partition by 列名 order by列名)
有时候一组数据只返回一组值是不能满足需求的,如我们经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。
上面这道题的sql就用到了over()开窗函数:
1、先用开窗函数将所有成绩查询出来并排序
select g.*, row_number() over(partition by g.course order by g.grade desc) number from grade g
结果如下:
2、再取前两名的成绩
SELECT
*
FROM
( SELECT g.*, row_number() over ( PARTITION BY g.course ORDER BY g.grade DESC ) number FROM grade g ) gg
WHERE
gg.number <=2
几个排序函数row_number() over()、rank() over()、dense_rank() over()、ntile() over()的区别
(1) row_number() over():对相等的值不进行区分,相等的值对应的排名相同,序号从1到n连续。
(2) rank() over():相等的值排名相同,但若有相等的值,则序号从1到n不连续。如果有两个人都排在第3名,则没有第4名。
(3) dense_rank() over():对相等的值排名相同,但序号从1到n连续。如果有两个人都排在第一名,则排在第2名(假设仅有1个第二名)的人是第3个人。