sparksql over sparksql over函数

一、窗口函数

1. 窗口函数有什么用？

在日常工作中，经常会遇到在每组内排名，比如下面的业务需求：

• 排名问题
• topN问题
• 进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了。

1. 什么是窗口函数？

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

窗口函数的基本语法如下：

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

注意：partition可以省略，省略之后便不指定分组。

那么语法中的<窗口函数>有哪些呢？

<窗口函数>的位置，可以放以下两种函数：

1. 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2. 聚合函数，如sum, avg, count, max, min等。

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

二、如何使用窗口函数

接下来，就结合实例给大家介绍几种窗口函数的用法。

1. 专用窗口函数rank

如下表，是班级表中的内容

如果我们想在每个班级内按成绩排名，得到下面的结果。

以班级“1”为例，这个班级的成绩“95”排在第一位，这个班级的“83”排在第四位。上面这个结果确实按我们的要求在每个班级内按成绩排名了。

得到上面结果的sql语句代码如下：

select *, rank() over (partition by 班级
                       order by 成绩 desc) as ranking from 班级表

我们来解释一下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话分成了两部分：

1. 每个班级内：按班级分组

partition by用来对表分组。在这个例子中，所以我们制定了按“班级”分组（partition by 班级）

2. 按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了关键词desc表示降序排列。

通过下图，我们就可以理解partition by（分组）和order by（在组内排序）的作用了。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句的排序功能，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partition by不会减少原表中的行数。例如下面统计每个班级的人数。

相信通过这个例子，你已经明白了这个窗口函数的使用：

select *, rank() over (partition by 班级
                       order by 成绩 desc) as ranking 
from 班级表;

现在我们说回来，为什么叫“窗口”函数呢？

这是因为partition by分组后的结果成为“窗口”，这里的窗口不是我们家的门窗，而是表示“范围”的意思。

简单来说，窗口函数有以下功能：

1. 同时具有分组和排序的功能
2. 不减少原表的行数

语法如下:

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

三、 其他专用窗口函数

1. 专用窗口函数rank, dense_rank, row_number有什么用呢？

根据下面例子，大家就可以看懂它们的区别了：

select *,rank() over (order by 成绩 desc) as ranking,
         dense_rank() over (order by 成绩 desc) as dese_rank,
         row_number() over (order by 成绩 desc) as row_num
from 班级表;

得到结果：

从上面结果可以看出：

• rank函数：这个例子中是5、5、5、8名，也就是如果有并列名次的行，会占用下一名次的位置。
• dense_rank函数：这个例子中是5、5、5、6名，也就是如果有并列名次的行，不用占用下一名次的位置。
• row_number函数：这个例子中是5、6、7、8名，也就是不考虑并列名次的情况。

最后需要强调一点：

在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

2. 聚合窗口函数

聚合窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

先单独以sum举个例子：

不仅是sum求和，avg、count、min、max也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。

我们来看一下窗口函数是聚合函数时的结果：

查询语句如下：

select *, 
sum(成绩) over (order by 学号) as current_sum,
avg(成绩) over (order by 学号) as current_avg,
count(成绩) over (order by 学号) as current_count,
max(成绩) over (order by 学号) as current_max,
min(成绩) over (order by 学号) as current_min
 from 班级表;

如果想要看所有人的总和、平均等聚合结果，看最后一行即可。

这样使用窗口函数有什么用呢？

聚合函数作为窗口函数，可以在每一行的数据里直观地看到截止到本行的数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体数据的影响。

四、窗口函数经典问题

1、排名问题

【题目】下表是“班级表”中的内容，记录了每个学生所在班级和对应的成绩。

现在需要按成绩来排名，如果两个分数相同，那么排名是要并列的。正常排名是1、2、3、4，但现在前三名是并列的名次，排名结果是1、1、1、2。

【解答】根据题目要求，我们用下述窗口函数查询

select *, dense_rank() over (order by 成绩 desc) as dese_rank        
from 班级表;

【本题考点】

1. 如何使用窗口函数
2. 专用窗口函数排名的区别：rank, dense_rank, row_number

【举一反三】

1. 涉及到排名的问题，都可以使用窗口函数来解决。记住rank, dense_rank, row_number

排名的区别。

2、topN问题

工作中会经常遇到这样的业务问题：

如何找到每个类别下用户最喜欢的产品是哪个？

如果找到每个类别下用户点击最多的5个商品是什么？

这类问题就是常见的分组取每组最大值、最小值，每组最大的N条（topN）记录。

面对该类例子，如何解决呢？

下面我们通过成绩表的例子来给出答案。

• 分组取最大值

案例：按课程号分组取成绩最大值所在行的数据

我们可以通过分组（group by）和汇总函数得到每个组里的一个值（最大值、最小值、平均值等）。但是无法得到成绩最大值所在行的数据。

我们可以通过关联子查询来实现：

上面查询结果课程号“0001”有2行数据是因为最大成绩80有2个。

• 分组取每组最小值

案例：按课程号分组取成绩最小值所在行的数据（同样使用关联子查询来实现）

• 每组最大的N条数据

【案例】：查询各科成绩前两名的记录

【方案一】

1. 查询出有哪些组

我们可以按课程号分组，查询出有哪些组，对应这个问题里就是有哪些课程号

select 课程号,max(成绩) as 最大成绩
from score
group by 课程号;

先使用order by子句按成绩降序排序（desc），然后使用limit子句返回topN(对应这个问题返回成绩前两名)

【方案二】（下面改为查询每个同学各科成绩前两名，原理同）

1. 看到问题中要查“每个”学生最高的成绩。之前说过，当有“每个”出现的时候，就要想到是要分组了。这里是“每个学生”，结合表的结构，是按学生“姓名”来分组。
2. 将表按学生姓名分组后，把成绩按降序排列，排在最前面的2个就是我们要找的“成绩最高的2个科目”。
3. 现在分组后，需要排序，又不减少原表的行数，这种功能自然想到是窗口函数。

使用哪个专用窗口函数呢？

为了不受并列成绩的影响，使用row_number专用窗口函数。

【解题步骤】

1. 按姓名分组（partition by 姓名）、并按成绩降序排列（order by 成绩 desc）,套入窗口函数的语法，就是下面的sql语句：

select *,row_number () over (partition by 学号 order by 成绩 desc) as ranking
from score;

运行结果如下：

如上表黄色框内的数据，每个同学成绩最好的两个科目就是要求的解。

想得到这个解，只要提取出“ranking”值小于等于2的数据就可以了。那么，只需要在上一步的sql语句里加入条件子句where就可以了。

查询语句如下：

select *
from (
select *,row_number () over (partition by 学号 order by 成绩 desc) as ranking
from score) as a
where ranking <=2

这样处理的原因是不能在上面代码的基础上直接加”where ranking<=2”(由于SQL语句运行顺序原因)，所以处理方法为用子查询，也就是把第一步查询到的结果作为一个新的表。

在实践过程中遇到一个问题，在此备注一下：

条件中不能包含聚组函数，使用where条件显示特定的行。
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

【举一反三】

经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组 ，又要排序的情况”，要能想到用窗口函数来实现。

本题的SQL语句修改下，就可以成为这类问题的万能模板，遇到这类问题往里面套就可以了：

-- topN问题，sql模板
select  *,
from (select  *,row_number () (partition by 要分组的列名 order by 要排序的列名 desc) as ranking from 表名) as a 
where ranking<=N;

3. 如何在每个组里比较

【题目】

查找单科成绩高于该科目平均成绩的学生名单

【解题思路】

1. “查找单科成绩高于该科目的平均成绩”，也就是在“每个”科目里比较。由“每个”我们可以想到需要分组了。
2. 能实现分组功能的SQL有两种：group by 和窗口函数partition by。

使用聚合窗口函数（求平均值avg）,将每门课平均成绩求出以后，然后找出大于平均成绩的数据。

group by 分组汇总后改变了表的行数，一行只有一个类别。而partition by 和rank函数不会减少原表中的行数。例如统计每个班的人数。

所以这里我们使用窗口函数的partition by。

【解题步骤】

1. 聚合函数avg()作为窗口函数，将每一科成绩的平均值求出。

select  *, avg(成绩) over (partition by 课程号) as avg_score  
from score;

运行结果如下：

如上表，按科目分组后的平均成绩已经算出，接下来只要筛选出成绩大于平均分的数据即可。那么只要在上一步语句中加入条件子句where就可以了

select  *, avg(成绩) over (partition by 课程号) as avg_score  
from score
where 成绩>avg_score;

解决方法是什么nie?

子查询呐！看我接下来七十二变！

select *
from (
select *, avg(成绩) over (partition by 课程号) as avg_score 
from score)as a
where 成绩>avg_score;

【本题考点】

1. 主要考察对窗口函数的灵活使用。
2. 在筛选过程中，非常容易因为子查询问题报错，本题也考察了对子查询的熟练应用。
3. 本题间接考察了对SQL语句执行顺序的熟悉程度。

【举一反三】

查找每个组里大于平均值的数据，可以有两种方法：

1. 使用上面讲到的窗口函数来实现；
2. 使用关联子查询，可以回顾《SQL之“复杂查询”》里的案例。

4. 窗口函数的移动平均

这里我们直接用聚合函数avg的窗口函数举例说明：‘

select *, avg(成绩) over (
order by 学号 rows 2 preceding) as current_avg 
from 班级表;

得到结果：

上面查询语句得到的结果是自身记录及前两行的平均。

这样使用窗口函数有什么用呢？

由于这里可以通过preceding关键字调整作用范围，在以下场景中非常适用：

在公司业绩名单排名中，可以通过移动平均直观地看到与相邻名次业绩的平均、求和等统计数据。

好喽，复杂查询就讲到这里啦，SQL重要的是多动手，多记忆，最好把各种语句及情景以脑图的形式放在自己的大脑中，说给大家，也说给自己~