玩转SQL窗口函数

原创

闫同学

发布于 2023-10-10 22:45:08

3150

文章被收录于专栏：扯编程的淡扯编程的淡

玩转SQL窗口函数

说明：MySQL8.0 之后才支持窗口函数

1 提前准备

在MySQL8.0版本数据库下新建数据表，插入数据

CREATE TABLE `student`
(   `id`      int(10) DEFAULT NULL,
    `name`    varchar(20) DEFAULT NULL,
    `age`     int(10) DEFAULT NULL,
    `address` varchar(20) DEFAULT NULL
) ENGINE=InnoDB;

insert into student values(1,'zs',23,'Beijing');
insert into student values(2,'ls',21,'Tianjing');
insert into student values(3,'ww',13,'Shanghai');
insert into student values(4,'zl',23,'Beijing');
insert into student values(5,'mq',4,'Nanjing');
insert into student values(6,'wb',3,'Beijing');
insert into student values(7,'hj',21,'Hebei');
insert into student values(8,'sw',34,'Henan');
insert into student values(9,'sa',42,'Beijing');

2 MySQL窗口函数及其语法

分类：

专用窗口函数，比如rank、dense_rank、row_number等
聚合函数，如sum、 avg、count、max、min等

2.1 基本语法

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

2.2 专用窗口函数示例

2.2.1 rank函数

作用：

RANK()函数，顾名思义排名函数，可以对某一个字段进行排名.

示例：

根据address分组，再根据age排序

select *,rank() over (partition by address order by age desc) as ranking from student

2.2.2 dense_rank函数

作用：

DENSE_RANK() 函数用来表示排名，与RANK()不同的是，DENSE_RANK() 不会出现空缺数字。比如，如果出现了两个并列的1，DENSE_RANK() 的第三个数仍然是2，而RANK()的第三个数是3。

示例：

根据address分组，再根据age排序

select *,dense_rank() over (partition by address order by age desc) as ranking from student

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tavdb8RP-1646454507181)(SQL窗口函数.assets/image-20220305111103656.png)]

2.2.3 row_number函数

作用：

ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号，他不能用做于学生成绩的排名，一般多用于分页查询

示例：

根据address分组，再根据age排序

select *,row_number() over (partition by address order by age desc) as ranking from student

2.2.4 first_value函数

作用：

FIRST_VALUE() 返回窗口范围内的第一个值。

示例：

根据address分组，返回某组中最大age的数量

select *,FIRST_VALUE(age) over (partition by address order by age desc) as ranking from student

2.2.5 综合使用

相当于将三个函数的排名情况进行对比

select *,
       rank() over (order by age desc) as ranking,
        dense_rank() over (order by age desc) as dese_rank,
        row_number() over (order by age desc) as row_num
from student

聚合窗口函数，不断向下聚会取结果

select *,
       sum(age) over (order by age) as current_sum,
        avg(age) over (order by age) as current_avg,
        count(age) over (order by age) as current_count,
        max(age) over (order by age) as current_max,
        min(age) over (order by age) as current_min
from student