SQL必会的常用函数（三）——窗口函数

这期我们介绍一下窗口函数。那么什么是窗口函数，窗口函数的作用又是什么呢？我们主要围绕这几点来详细认识一下这个函数。

1.什么是窗口函数？

窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。

2.它的作用是什么？

• 解决排名问题，e.g.每个班级按成绩排名
• 解决TOPN问题，e.g.每个班级前两名的学生

3.语法

select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名)

4.分类

窗口函数有以下三种排序方式

函数	作用
rank() over()	1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
row_number() over()	1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over()	1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

注意：窗口函数是对where后者group by子句处理后的结果进行操作，因此按照SQL语句的运行顺序，窗口函数一般放在select子句中。

举个例子：
每类试卷得分前3名

SQL27 每类试卷得分前3名

题目主要信息：

• 找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大

• 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）

• 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

问题拆分：

• 筛选出一个各类标签与用户及排名的表格：
  • 标签信息和得分信息分布在两个表格，需要将其用exam_id连接在一起。知识点：join…on…
  • 排名是以每个标签每个用户为组的，因此要分组。group by tag, e_r.uid 知识点：group by
  • 对每类标签使用分组聚合排名。知识点：row_number() over partition by
  • 排名优先级先是每个用户的最大得分降序，然后是每个用户的最低得分降序，最后用户ID降序。知识点：order by、min()、max()
• 从上述表格中选出排名小于等于3的标签、用户ID及排名。知识点：select…from…where…

代码：

SELECT
	tag,
	uid,
	ranking 
FROM
	(
	SELECT
		tag,
		e_r.uid,
		row_number() over ( PARTITION BY tag ORDER BY tag, max( score ) DESC, min( score ) DESC, e_r.uid DESC ) AS ranking 
	FROM
		exam_record e_r
		JOIN examination_info e_i ON e_r.exam_id = e_i.exam_id 
	GROUP BY
		tag,
		e_r.uid 
	) ranktable 
WHERE
	ranking <= 3