SQL高级功能-窗口函数及其经典题目

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语句执行顺序的熟悉程度。