数据库窗口函数实战

目录

前言

窗口函数语法

创建测试表和数据

使用示例

PARTITION BY

窗口函数

ROW_NUMBER

RANK

DENSE_RANK

RANGE

ROWS

---

前言

SQL 具有很高的灵活性，可以根据需求进行复杂的数据查询和分析，支持多表联合查询（join）、排序（order by）、分组（group by）、聚合（sum）等操作。虽然以上这些操作已经能解决绝大多的查询问题了，但是还是存在一些较为特殊的场景没法很好地处理。

比如leetcode中一道SQL题：

1321. 餐馆营业额变化增长 - 力扣（LeetCode）https://leetcode.cn/problems/restaurant-growth/description/像以上的题目的场景，每一行数据的某个字段都需要与表中的其他部分数据相同字段做聚合操作，像这种场景就无法使用 group by 实现，因为使用 group by 来对字段分组，会将表中的全部数据做聚合操作。

而数据库的窗口函数处理这些问题就非常合适。

窗口函数语法

<窗口函数 / 聚合函数> over (

partition by <用于分组的列名>
order by <用于排序的列名>
rows/range子句 <用于定义窗口大小>

)

创建测试表和数据

以下使用 MySQL 来进行演示 

create table score (
  id int auto_increment primary key,
  student_id int,
  subject varchar(20),
  score double
);

insert into score(student_id, subject, score) values(1, 'chinese', 70);
insert into score(student_id, subject, score) values(1, 'math', 70);
insert into score(student_id, subject, score) values(1, 'english', 70);
insert into score(student_id, subject, score) values(2, 'chinese', 80);
insert into score(student_id, subject, score) values(2, 'math', 80);
insert into score(student_id, subject, score) values(2, 'english', 90);
insert into score(student_id, subject, score) values(3, 'chinese', 70);
insert into score(student_id, subject, score) values(3, 'math', 50);
insert into score(student_id, subject, score) values(3, 'english', 90);

 得到如下初始数据：三个学生语数英成绩

使用示例

PARTITION BY

需求：得到每个学生的总分

可以使用分组汇总来得到每个学生的总分，partition by 和 group by ，都是用于分组，但是两个分组有点区别

使用 group by 来分组，会改变总行数，因为同组的全部数据都会被汇总成一条

select student_id, sum(score) total 
from score
group by student_id

 

而使用 partition by 来分组，则不会改变总行数

select student_id, sum(score) over (partition by student_id) as total
from score

 

窗口函数

需求：得到每个学生各科的成绩排名

有三个窗口函数可以解决排名问题：

1. ROW_NUMBER()：为每一行分配一个唯一的整数，常用于生成行号。
2. RANK()：计算每个行的排名，若有相同值则排名相同，下一个排名按跳过相同值后的高一位开始。
3. DENSE_RANK()：计算每个行的排名，若有相同值则排名相同，下一个排名连续递增。

ROW_NUMBER

select *, row_number() over (partition by subject order by score desc) as ranking
from score;

 

RANK

select *, rank() over (partition by subject order by score desc) as ranking
from score;

 

 可以看到 english 科目，有两个90分都是第一名，70分只能是第三名，没有第二名

DENSE_RANK

select *, dense_rank() over (partition by subject order by score desc) as ranking
from score;

 

 可以看到 english 科目，有两个90分都是第一名，70分是第二名，不会跳过

RANGE

需求：统计每个同学 math 科目分数差距不超过10分的人数

使用聚合函数 COUNT() + RANGE 子句实现

select *,
count(*) over (order by score range between 10 preceding and 10 following) as '当前分数相差±10的人数'
from score
where subject = 'math'

 50 - 10 = 40，50 + 10 = 60，表中 [40, 60] 区间的分数只有 50 分数一个，故得到的结果为 1

同理，表中 [60, 80] 区间的分数有 70 和 80 分数，故得到的结果为 2

表中 [70, 90] 区间的分数有 70 和 80 分数，故得到的结果为 2

ROWS

没有范围子句，则是窗口大小默认是全部数据，rows 子句其实就是按照行数来确定窗口的大小

以查询 chinese 成绩为例：

select *,
sum(score) over (order by id rows 1 preceding) as '当前行+前一行',
sum(score) over (order by id rows between 1 preceding and 1 following) as '前一行+当前行+后一行',
sum(score) over (order by id rows between unbounded preceding and current row) as '开头累加到当前行',
sum(score) over (order by id rows between current row and unbounded following) as '当前行累加到结尾'
from score
where subject = 'chinese'