sql如何将不同表的查询结果合并 并排序_数据分析之路——SQL高级功能（7）

*上周把一些常见的求职面试题分析了一下，其中有一个让我纠结了一段时间，今天发现了真正的解决方法，原来是窗口函数啊……

接下来看看窗口函数是什么，怎么用，和一些实例，同时把上周纠结的题目拿来重新做一次。

本周笔记：

通过上述笔记，可以知道窗口函数究竟是什么和如何使用，其中SQL语句的使用通过几个练习来熟悉一下：

练习1：根据每门课程按成绩给学生进行排名

此处可以发现，rank函数可以给出并列排名，接下来稍稍添加和修改成绩，以对三种不同的专用窗口函数作出比较

练习2：根据每门课程按成绩给学生进行排名（分别使用rank, dense_rank，row_number）

由此处可以清晰地看出三者的区别：

• rank函数的并列排名（课程号0001）会占用下一名次的位置，故而为1，1，3；
• dense_rank函数的并列排名则不会占用，故而为1，1，2；
• row_number函数无并列排名机制

练习3：下图是班级表（class）中的内容，记录了每个学生所在班级，和对应的成绩；需要按成绩来排名，如果两个分数相同，那么排名要是并列的，且不占用下一名次的位置

根据题目要求，可以确认需要使用dense_rank函数进行分组和排序，则结果如下：

练习4：top N 类问题

• 查询各科成绩前两名的记录（即上周我有疑问的那个练习）

回顾当时的解决方式

SQL语句（使用了合并查询的方式）

-- 查询各科成绩前两名的记录
(SELECT * FROM score
	WHERE 课程号 = '0001'
	ORDER BY 成绩 DESC
	LIMIT 2)
UNION ALL
(SELECT * FROM score
	WHERE 课程号 = '0002'
	ORDER BY 成绩 DESC
	LIMIT 2)
UNION ALL
(SELECT * FROM score
	WHERE 课程号 = '0003'
	ORDER BY 成绩 DESC
	LIMIT 2)

运行结果

当时的小疑问

虽然此处通过合并方法解决了问题，但是是否有更好的方式呢？尤其是在并不确定课程有多少的情况下？

在学习了窗口查询后，新的思路

select * -> 由于数据列不多，故此使用了全部查询 from (select *, <窗口函数> over ( partition by 学号 -> 此处考虑到需要前两名学生，即便同名次也必须是两人，故此不适用rank 或者 dense_rank，选择row_number order by 成绩 desc) as 名次 -> 按照降序排列成绩 from score) as x -> 由成绩表score当中进行子查询 where 名次 <= 2 group by <分组条件> -> 无 having <分组结果条件> -> 无 order by <排序条件> -> 无 limit <指定行> -> 无

则代码应该为

-- 查询各科成绩前两名的记录
SELECT * FROM (SELECT *,
	ROW_NUMBER() OVER (PARTITION BY 学号
		ORDER BY 成绩 DESC) AS 名次
	FROM score) AS x
	WHERE 名次 <= 2;

运行结果

由于此处仍然不够直观，可以考虑多表查询，并将学生姓名和课程名称显示出来

此处思路

在之前查询的基础上，将学生表student和课程表course内联结

代码

-- 查询各科成绩前两名的记录，包括课程名称、学生姓名、成绩、以及名次
SELECT z.课程名称, y.姓名, x.成绩, x.名次 
	FROM (SELECT *,
		ROW_NUMBER() OVER (PARTITION BY 课程号
			ORDER BY 成绩 DESC) AS 名次
		FROM score) AS x
		INNER JOIN student AS y
			ON x.学号 = y.学号
		INNER JOIN course AS z
			ON x.课程号 = z.课程号
	WHERE 名次 <= 2;

运行结果

练习5：聚合窗口函数

• 通过成绩表进行聚合窗口函数的初步练习

此处因为是按照学号进行了汇总、平均、计数、取最大和最小值，故而每个学号的结果都相同

• 查找单科成绩高于该科目平均成绩的学生名单（此处使用窗口函数）

翻译成自己理解的大白话

查询成绩记录可以由成绩表score获取，由于是记录，故取出所有列*
首先考虑到是各科成绩，需要按照课程号分组，目前分组可以考虑两种方式
group by 和 partition by
此处由于需要求出每门课程的平均分，以及高于平均分的学生名单，故而不能合并记录，此处需要使用窗口函数

分析思路

select * -> 由于数据列不多，故此使用了全部查询 from (select *, <窗口函数> over ( partition by 课程号) as 平均分 -> 此处考虑到需要各科目的平均分，故此使用avg(成绩)，并按照课程号分组 order by <排序条件> -> 无 from score) as x -> 由成绩表score当中进行子查询 where 成绩 > 平均分 group by <分组条件> -> 无 having <分组结果条件> -> 无 order by <排序条件> -> 无 limit <指定行> -> 无

写出SQL语句

-- 查找单科成绩高于该科目平均成绩的学生名单
SELECT *
	FROM (SELECT *,
		AVG(成绩) OVER (PARTITION BY 课程号) AS 平均分
		FROM score) AS x
	WHERE 成绩 > 平均分;

运行结果

由于此处仍然不够直观，可以考虑多表查询，并将学生姓名和课程名称显示出来

代码

-- 查找单科成绩高于该科目平均成绩的学生名单，包括课程名称、学生姓名、成绩、以及平均分
SELECT z.课程名称, y.姓名, x.成绩, x.平均分
	FROM (SELECT *,
		AVG(成绩) OVER (PARTITION BY 课程号) AS 平均分
		FROM score) AS x
		INNER JOIN student AS y
			ON x.学号 = y.学号
		INNER JOIN course AS z
			ON x.课程号 = z.课程号
	WHERE x.成绩 > x.平均分;

运行结果

练习6：班级总成绩表的相邻移动平均和总和

翻译成自己理解的大白话

班级总成绩表可以由班级表class中获取
在需要获取平均值时需要用到汇总函数avg，总和需要用到sum，而移动平均和总和则需要考虑窗口函数
相邻需要在窗口函数当中的分组或者排序部分指定rows 1 preceding，即计算当前行与之前1行的平均值以及总和

分析思路

select *, -> 由于数据列不多，故此使用了全部查询 avg(成绩) over (order by 学号 rows 1 preceding) as 相邻平均值, -> 通过窗口函数求出每三人的平均值 sum(成绩) over (order by 学号 rows 1 preceding) as 相邻总和 -> 通过窗口函数求出每三人的总和 from class -> 由班级表中查询 where <查询条件> -> 无 group by <分组条件> -> 无 having <分组结果条件> -> 无 order by <排序条件> -> 无 limit <指定行> -> 无

写出SQL语句

-- 班级总成绩表的相邻移动平均和总和
SELECT *,
	AVG(成绩) OVER (ORDER BY 学号 ROWS 1 PRECEDING) AS 相邻平均值,
	SUM(成绩) OVER (ORDER BY 学号 ROWS 1 PRECEDING) AS 相邻总和
	FROM class;

运行结果

通过这个比较高级的概念（窗口函数），我们有了更广的维度去解决更复杂的问题，为将来工作中实际解决问题带来了更宽广的思路。

到此，数据分析（中级）部分的学习完毕，接下来先进行一段时间的练习，来解决更多的实际问题，并熟悉分析思路、解决方法以及各种语句和函数的用法，之后，就可以考虑开启高级学习之路了。