文章目录
- 一、什么是开窗函数？
- 二、开窗函数的用法
- 1. 分区排序：row_number () over()
- 2. 几个排序函数的区别
- 3. 其他开窗函数
- 总结
一、什么是开窗函数？
开窗函数也叫分析函数，有两类：一类是聚合开窗函数，一类是排序开窗函数。
开窗函数的调用格式为： 函数名(列名) OVER(partition by 列名 order by列名) 。
开窗函数其实与聚合函数类似，只要了解聚合函数，就很容易理解开窗函数。聚合函数是对一组值执行计算并返回单一的值，如sum()，count()，max()，min()， avg()等，这些函数常与group by子句连用（除了 COUNT 以外，聚合函数忽略空值）。
但有时候一组数据只返回一组值是不能满足需求的，如我们经常想知道各个地区的前几名、各个班或各个学科的前几名，这时候需要每一组返回多个值，用开窗函数则可以非常方便地解决这类问题。
开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。
开窗函数和聚合函数的区别：
（1）SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。
（2）聚合函数每组只返回一个值，开窗函数每组可返回多个值。
二、开窗函数的用法
1. 分区排序：row_number () over()
原始表数据：
-- students_grades
drop table if exists students_grades;
create table students_grades(
id bigint default '0' not null comment '主键id',
name char(100) default '0' not null comment '名字',
course char(100) default '0' not null comment '课程',
grades int default '0' not null comment '分数'
);
insert into students_grades values (1,'张三','数学',98);
insert into students_grades values (2,'张三','语文',89);
insert into students_grades values (3,'张三','英语',77);
insert into students_grades values (4,'张三','物理',72);
insert into students_grades values (5,'李四','数学',79);
insert into students_grades values (6,'李四','语文',86);
insert into students_grades values (7,'李四','英语',92);
insert into students_grades values (8,'王五','数学',86);
insert into students_grades values (9,'王五','语文',86);
insert into students_grades values (10,'王五','英语',96);
insert into students_grades values (11,'王五','物理',72);
insert into students_grades values (12,'赵六','数学',72);
insert into students_grades values (13,'赵六','语文',95);
insert into students_grades values (14,'赵六','英语',95);
查询每门课程course前三名的学生姓名及成绩，要求输出列格式如下：course, number, name, grades
查询语句如下：
select
*
from(
select
course,
row_number() over(partition by course order by grades desc) as number,
name,
grades
from students_grades
) a
where a.number <=3;中间表：
结果表：
2. 几个排序函数的区别
（1） row_number() over()
对相等的值不进行区分，其实就是行号，相等的值排名不同，序号从1到n连续。123
（2） rank() over()
相等的值排名相同，序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。1124
（3） dense_rank() over()
相等值排名相同，序号从1到n连续。如果有两个人都排在第一名，则排在第2名（假设仅有1个第二名）的人是第3个人。1123
学生成绩表同上，查询语句如下：
select
id,
name,
course,
grades,
row_number() over(order by grades desc) as r_num,
rank() over(order by grades desc) as rk,
dense_rank() over(order by grades desc) as d_rank
from students_grades;
注：在使用 row_number() over()函数时候，over()里头的分组以及排序的执行，晚于where、group by、order by的执行。
3. 其他开窗函数
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)
lag和lead可以获取结果集中，按一定排序所排列的当前行的上下相邻若干offset的某个行的某个列(不用结果集的自关联）；
lag，lead分别是向前，向后；
lag和lead有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是超出记录窗口时的默认值）总结
以上就是今天要讲的内容，本文主要介绍了常用的三大开窗（排序）函数的用法，包括：row_number() over()、rank() over()、dense_rank() over()，后续会继续补充其他开窗函数的例子，以便更好理解。
