一、What?

二、Why?

之前学过的group by子句分组的功能和order by子句排序的功能。

那么，为什么还要用窗口函数呢？

group by分组汇总后改变了表的行数，一行只有一个类别。

而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

三、How?

要实现在每个组内排名，具体如何使用？基本语法如下：

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

解释：partiition by（分组）和order by（在组内排序）

接下来从专用窗口函数和聚合函数两方面来讲。

1.专用窗口函数

rank函数

现在有一张班级表，学生来自不同的班级，要求按照班级对学生的成绩进行排名

最终实现这样的结果

具体思路：

确定模板中需要填入的数据-

(1)窗口函数：rank

(2)‹用于分组的列名› :班级

(3)用于排序的列名：成绩

套入模板就是

rank over (partition by 班级
               order by 成绩)

最终结果加上select语句：

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

专用窗口函数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 班级表

2.聚合函数

常见的聚合函数有：sum,avg, count, max, min等，聚合函数使用时需要在（）增加列名。

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 班级表

结果截图：

含义：sum（成绩）表示对该行以及之前的几行成绩求和，avg（成绩）表示对该行以及之前的几行的成绩求平均数

作用：

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

四、实战案例

1.下图是"班级"表中的内容，记录了每个学生所在班级，和对应的成绩。

要求：现在需要按成绩来排名，如果两个分数相同，那么排名要是并列的。

正常排名是1，2，3，4，但是现在前3名是并列的名次，排名结果是：1，1，1，2。

解答：

思路：
step1:班级表涉及多个班级，要按照成绩排列，可以使用窗口函数中的专用窗口函数。
正常排列情况是用row_num，但是现在要求并列排列，rank， dense_rank都是并列排列，
根据1,1，1,2选择dense_rank排列方式
step2:接下来套用模板
select *,                 
dense_rank() over (order by 成绩 desc) as dese_rank
from 班级表

【举一反三】

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

例子，编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

思路：分数排名【使用窗口函数】，
如果分数相同，排名相同-说明使用rank，dense_rank函数
名次之间没有间隔，使用dense_rank

select score,                
dense_rank() over(order by Score desc) as dense_rank 
from Scores;

2.【面试题类型】topN问题

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

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

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

成绩表是学生的成绩，里面有学号（学生的学号），课程号（学生选修课程的课程号），成绩（学生选修该课程取得的成绩）

(1)分组取最大值

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

方案一：关联子查询
思路：
按课程号分组，max(成绩)可以知道成绩最大值，利用where 成绩=max(成绩)可以找到成绩最大值所在行
max(成绩)可以使用关联子查询获得，如果没有where a.课程号=b.课程号关联语句，
则只能找到成绩最大的数据，但是无法定位到某个课程的成绩最大数据行。
select *
from score as a
where a.成绩 in (
select max(b.成绩)
from score as b
where a.课程号=b.课程号
group by b.课程号
)

方法二：使用窗口函数rank
select *
from (
select 课程号,学号,成绩,
rank() over (partition by 课程号
       order by 成绩 desc)as 排名
from score ) as a
where a.排名<=1;

(2)分组取最小值

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

思路同上
select *
from score as a
where a.成绩 in (
select min(b.成绩)
from score as b
where a.课程号=b.课程号
group by b.课程号
)

方法二：窗口函数
select *
from (
select 课程号,学号,成绩,
rank() over (partition by 课程号
       order by 成绩 asc)as 排名
from score ) as a
where a.排名<=1;

(3)每组最大的N条记录

案例：现有“成绩表”，记录了每个学生各科的成绩。表内容如下。

问题：查找每个学生成绩最高的2个科目

思路：top2问题使用窗口函数
1.分组对象是课程号
2.排序对象是成绩

select *
from(
select *,
row_number() over (partition by 姓名
                   order by 成绩 desc) as ranking
from 各科成绩表) 
as a
where ranking<=2;

3.问题：查找单科成绩高于该科目平均成绩的学生名单

思路：使用avg()聚合函数，按照科目进行分组，求出平均成绩
然后利用子查询筛选出成绩>avg(成绩)的学生名单
使用avg()聚合函数
select *
from (
select*,
avg(成绩) over (partition by 科目
                order by 成绩) as 平均成绩
from 各科成绩表
) as a
where 成绩>平均成绩

ITPub博客

Part15:SQL高级功能-窗口函数（解决分组排名问题，比如topN问题）

一、What?

二、Why?

三、How?

四、实战案例

最新文章