窗口函数
1、窗口函数有什么用?
在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求:
排名问题:每个部门按业绩来排名
topN问题:找出每个部门排名前N的员工进行奖励
面对这类需求,就需要使用sql的高级功能窗口函数了。
2、什么是窗口函数?
窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
窗口函数的基本语法如下:
‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)
那么语法中的‹窗口函数›都有哪些呢?
‹窗口函数›的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。
3、如何使用窗口函数
因为我电脑Mysql安装的是 5.6,而窗口函数只有8版本以上才能用,所以这里用HIVE SQL进行演练。通过实例来学习,首先创建表:
create table student2(
id string,
class string,
score string
)
row format delimited fields TERMINATED by '\t';
向表中插入数据:
INSERT into table student2
VALUES
('0002','1','95'),
('0008','1','88'),
('0001','1','86'),
('0004','1','83'),
('0003','1','89'),
('0005','2','86'),
('0006','3','92'),
('0007','3','86');
1.专用窗口函数rank
这是原始表的内容:
如果我们想在每个班级内按成绩排名:
SELECT
*,
rank() over(PARTITION by class order by score desc) as ranking
from student2;
得到下面的结果。
以班级“1”为例,这个班级的成绩“95”排在第1位,这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内,按成绩排名了。
我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”,这句话可以分为两部分:
1)每个班级内:按班级分组
partition by用来对表分组。在这个例子中,所以我们指定了按“班级”分组(partition by 班级)
2)按成绩排名
order by子句的功能是对分组后的结果进行排序,默认是按照升序(asc)排列。在本例中(order by 成绩 desc)是按成绩这一列排序,加了desc关键词表示降序排列。
窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么,为什么还要用窗口函数呢?
这是因为,group by分组汇总后改变了表的行数,一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。
那么,为什么叫“窗口”函数呢?这是因为partition by分组后的结果称为“窗口”,这里的窗口不是我们家里的门窗,而是表示“范围”的意思。
简单来说,窗口函数有以下功能:
1)同时具有分组和排序的功能
2)不减少原表的行数
3)语法如下:
‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)
2、专用窗口函数rank, dense_rank, row_number的区别
select
*,
rank() over(order by score desc ) as ranking,
dense_rank() over(order by score desc) as dese_rank,
row_number() over(order by score desc ) as row_num
from
student2;
得到结果:
从上面的结果可以看出:
rank函数:这个例子中是5位,5位,5位,8位,也就是如果有并列名次的行,会占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4。
dense_rank函数:这个例子中是5位,5位,5位,6位,也就是如果有并列名次的行,不占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2。
row_number函数:这个例子中是5位,6位,7位,8位,也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。
4、经典面试问题
1.窗口函数的使用
问题:现在需要按成绩来排名,如果两个分数相同,那么排名要是并列的。
select
*,
dense_rank() over(order by score desc) as dese_rank
from
student2;
结果如下:
【本题考点】
1.考察如何使用窗口函数
2.专用窗口函数排名的区别:rank, dense_rank, row_number
2、经典Top N问题
(1)分组取每组最小值:
问题:按课程号分组取成绩最小值所在行的数据
(2)每组最大的N条记录:
表内容如下。问题:查找每个学生成绩最高的2个科目
新建一张表:
create table student3(
name string,
subject string,
score string
)
row format delimited fields TERMINATED by '\t';
往表里插入数据:
INSERT into table student3
VALUES
('houzi','chinese','90'),
('sicong','chinese','81'),
('mayun','chinese','79'),
('mahuateng','chinese','88'),
('houzi','math','85'),
('sicong','math','86'),
('mayun','math','92'),
('mahuateng','math','83'),
('mahuateng','chinese','88'),
('houzi','english','87'),
('sicong','english','98'),
('mayun','english','93'),
('mahuateng','english','95');
【解题思路】
1.看到问题中要查“每个”学生最高的成绩。还记得我们之前课程里讲过的吗?当有“每个”出现的时候,就要想到是要分组了。
这里是“每个学生”,结合表的结构,是按学生“姓名”来分组。
2.将表按学生姓名分组后,把成绩按降序排列,排在最前面的2个就是我们要找的“成绩最高的2个科目”。
3.现在分组后,需要排序,又不减少原表的行数,这种功能自然想到是窗口函数。
4.使用哪个专用窗口函数?
为了不受并列成绩的影响,使用row_number专用窗口函数:
select
*,
row_number() over(partition by name order by score desc) ranking
from
student3;
结果如下:
如上表框内的数据,每个同学成绩最好的2个科目,就是要求的解。
想得到这个解,只要提取出“ranking”值小于等于2的数据就可以了。那么,只需要在上一步的slq语句里加入条件字句where就可以了:
select
*,
row_number() over(partition by name order by score desc) ranking
from
student3
where ranking<=2;
很多同学都会用这样的思路解题,但是这样写,sql会报错,为什么呢?
因为sql的书写顺序和运行顺序。在运行顺序中,select字句是最后被运行的,请看下图:
当明白了运行顺序以后,就知道错误的原因了:运行到”where ranking › 2”的时候,因为select字句还没有被执行,因此select中的“ranking”列还没有出现,从而导致报错。
解决方法是什么呢?
这种情况就可以用子查询,也就是把第一步得到查询结果作为一个新的表,sql语句如下:
select
*
from
(select
*,
row_number() over(partition by name order by score desc) ranking
from
student3)t1
where ranking<=2;
结果:
【本题考点】
1.主要考查对窗口函数的灵活使用。
2.在筛选过程中,非常容易因为子查询问题报错,本题也考察了对子查询的熟练运用。
3.本题间接考察了对sql语句执行顺序的熟悉程度。
3、聚合窗口函数面试题
问题:查找单科成绩高于该科目平均成绩的学生名单
【解题思路】
1.“查找单科成绩高于该科目平均成绩”,也就是在“每个”科目里比较。还记得我们之前课程里讲过的吗?当有“每个”出现的时候,就要想到是要分组了。
能实现“分组”功能的sql有两种,一是group by字句,另一个是窗口函数的partition by
2.使用聚合窗口函数(求平均值avg),将每门课的平均成绩求出以后,然后找出大于比平均成绩的数据。
这就要求分组后不能减少表的行数。
group by分组汇总后改变了表的行数,一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。
所以这里我们使用使用窗口函数的partition by。
select
*
from
(select
*,
avg(score) over(partition by subject ) as avg_score
from
student3)t1
where score>avg_score;
查询的结果:
【本题考点】
1.主要考查对窗口函数的灵活使用。
2.在筛选过程中,非常容易因为子查询问题报错,本题也考察了对子查询的熟练运用。
3.本题间接考察了对sql语句执行顺序的熟悉程度。