MySQL --- 窗口函数

快乐画饼

已于 2022-12-08 15:05:50 修改

阅读量290

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库

于 2022-12-08 14:54:30 首次发布

本文链接：https://blog.csdn.net/qq_57151931/article/details/128234670

版权

mysql 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

概念

窗口限定一个范围，它可以理解为满足某些条件的记录集合，窗口函数也就是在窗口范围内执行的函数。

语法

窗口函数有over关键字，指定函数执行的范围，可分为三部分：分组子句(partition by)，排序子句(order by)，窗口子句(rows)

<函数名> over (partition by <分组的列> order by <排序的列> rows between <起始行> and <终止行>)

TIP：Mysql8才支持窗口函数

窗口确定

分组子句(partition by)

不分组可以写成partition by null或者直接不写

后面可以跟多个列，如 partition by cid, sname

TIP：

partition by与group by的区别

1）前者不会压缩行数但是后者会

2）后者只能选取分组的列和聚合的列

也就是说group by 后生成的结果集与原表的行数和列数都不同

排序子句(order by)

不排序可以写成order by null 或者直接不写

asc或不写表示升序，desc表示降序

后面可以跟多个列，如 order by cid, sname

窗口子句(rows)

窗口子句的描述：

起始行: N preceding/unbounded preceding

当前行: current row

终止行: N following/unbounded following

TIP:

排序子句后面缺少窗口子句，窗口规范默认是 rows between unbounded preceding and current row

排序子句和窗口子句都缺失，窗口规范默认是 rows between unbounded preceding and unbounded following

总体流程

1.通过partition by 和 order by 子句确定大窗口（定义出上界unbounded preceding和下界unbounded following）

2.通过row 子句针对每一行数据确定小窗口（滑动窗口）

3.对每行的小窗口内的数据执行函数并生成新的列

函数分类

排序类

函数名	描述
RANK（）	分区中当前行的排名，带有间隙
DENSE_RANK（）	分区中当前行的排名，无间隙
ROW_NUMBER（）	其分区中的当前行数

案例

表图

代码

-- 【排序类】
-- 按班级分组后打上序号 不考虑并列
select *, row_number() over (partition by cid order by score desc) as '不可并列排名'
from SQL_5;
-- 按班级分组后作跳跃排名 考虑并列
select *, rank() over (partition by cid order by score desc) as '跳跃可并列排名'
from SQL_5;
-- 按班级分组后作连续排名 考虑并列
select *, dense_rank() over (partition by cid order by score desc) as '连续可并列排名'
from SQL_5;
-- 合并起来对比
select *,
       row_number() over (partition by cid order by score desc) as '不可并列排名',
       rank() over (partition by cid order by score desc)       as '跳跃可并列排名',
       dense_rank() over (partition by cid order by score desc) as '连续可并列排名'
from SQL_5;

结果

跨行类

函数名	描述
LAG（）	分区内滞后当前行的参数值
LEAD（）	分区内当前行前导行的参数值

案例

代码

-- 【跨行类】
-- lag/lead 函数 参数1:比较的列 参数2: 偏移量 参数3:找不到的默认值
-- 同一班级内，成绩比自己低一名的分数是多少
select *, lag(score, 1) over (partition by cid order by score) as '低一名的分数' from SQL_5;
-- 或者写成
select *, lag(score, 1, 0) over (partition by cid order by score) as '低一名的分数' from SQL_5;
-- 同一班级内，成绩比自己高2名的分数是多少
select *, lead(score, 2) over (partition by cid order by score) as '高两名的分数' from SQL_5;

结果

分组内topN

语法

select * from
(
	select *, row_number() over (partition by 分组列 order by 比较列) as rn from table
) as tmp
where rn <= N;

案例

表图

代码

-- 【分组内topN】
-- 求出每个学生成绩最高的三条记录
select * from
(
    select *, row_number() over (partition by sname order by score desc) as rn from SQL_6
) temp
where rn <= 3

结果