[大数据]玩转SQL窗口函数

玩转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

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

3 总结

窗口函数有以下功能：

• 同时具有分组（partition by）和排序（order by）的功能
• 不减少原表的行数，所以经常用来在每组内排名

注意事项

• 窗口函数原则上只能写在select子句中
• 窗口函数使用场景一般是排名问题和top N问题上

参考:

https://zhuanlan.zhihu.com/p/92654574

https://blog.csdn.net/shaiguchun9503/article/details/82349050

https://doris.apache.org/zh-CN/sql-reference/sql-functions/window-function.html#function%E4%BD%BF%E7%94%A8%E4%B8%BE%E4%BE%8B

https://blog.csdn.net/shaiguchun9503/article/details/82349050