MySQL 窗口函数

已于 2023-04-07 14:18:59 修改
阅读量1.9k 收藏 16
点赞数 4
分类专栏： MySQL笔记 文章标签： mysql 数据库 后端
于 2022-06-08 11:56:49 首次发布
本文为CSDN博主「王林」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
本文链接：https://blog.csdn.net/qq_45912025/article/details/125179004
版权

MySQL 窗口函数

一、简介

MysQL从8.o版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。

在这里插入图片描述

二、分类

窗口函数可以分为静态窗口函数和动态窗口函数。

  • 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同;
  • 动态窗口函数的窗口大小会随着记录的不同而变化。

窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数，如下表:
在这里插入图片描述

三、语法结构

窗口函数的基本语法

<分析函数> over ([partition by <列清单>] order by <排序用列清单> [rows between 开始位置 and 结束位置])

窗口函数的语法结构

函数 over ([partition by 字段名 order by 字段名 asc|desc])

或者是:

函数 over 窗口名 ... window 窗口名 as ([partition by 字段名 order by 字段名 asc|desc])
  • over 关键字指定函数窗口的范围：
    • 如果省略后面括号中的内容，则窗口会包含满足where条件的所有记录，窗口函数会基于所有满足where条件的记录进行计算。
    • 如果over关键字后面的括号不为空，则可以使用如下语法设置窗口。
  • 窗口名:为窗口设置一个别名，用来标识窗口。
  • partition by子句: 指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
  • order by子句:指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
  • frame子句:为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

窗口范围说明：

  • rows between unbounded preceding and current row（表示从起点到当前行），常用该窗口来计算累加；
  • range between unbounded preceding and unbounded following（表示本组内第一行到最后一行）；

（一）滑动窗口：rows & range用法

[ between and ]

  • rows: 表示按照行的范围进行定义框架，根据order by子句排序后，取的前n行及后n行的数据计算（与当前行的值无关，只与排序后的行号相关）。常用：rows n perceding表示从当前行到前n行（一共n+1行）
  • range：表示按照值的范围进行定义框架，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内。适用于对日期、时间、数值排序分组
边界可取值（start expr & end expr）说明
current row当前行
n preceding前 n 行，n 为数字， 比如 2 preceding 表示前2行
unbounded preceding开头
n following后n行，n 为数字， 比如 2 following 表示后2行
unbounded following结尾
range取特定日期区间说明
range interval 7-1 day preceding最近7天的值
range between interval 1 day preceding and interval 1 day following前后一天和当天的值

（二）函数执行顺序

在加入窗口函数的基础上SQL的执行顺序也会发生变化，具体的执行顺序如下（window就是窗口函数)：

在这里插入图片描述

四、分类讲解

（一）序号函数

row_number()

例一：

查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息

select 
*,row_number() over(partition by category order by price desc) rnk
from goods g

在这里插入图片描述

例二：

查询 goods 数据表中每个商品分类下价格最高的3种商品信息

select * from (
	select *,
	row_number() over(partition by category order by price desc) as rnk
	from goods
) t
where rnk <= 3;

在这里插入图片描述

rank()函数

使用rank()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息

select 
*,rank() over(partition by category order by price desc) rnk
from goods g

在这里插入图片描述

dense_rank()函数

使用dense_rank()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息

select 
*,dense_rank() over(partition by category order by price desc) rnk
from goods g

在这里插入图片描述

（二）分布函数

percent_rank()

注意：percent_rank() 等级值百分比 = (rank - 1)/ (组内rows - 1)

计算 goods 数据表中名称为"女装/女士精品”的类别下的商品的PERCENT RANK值

写法一：

select 
*,rank() over(partition by category order by price desc) rk,
percent_rank() over(partition by category order by price desc) pk
from goods g

写法二：

select 
*,rank() over w as rk,
percent_rank() over w as pk 
from goods g 
where category_id is not null window w as (partition by category_id order by price desc)

在这里插入图片描述

cume_dist()函数

cume_dist()累积分布值 = (<=当前rank值的行数) / 分组内总行数

CUME_DIST()函数主要用于查询小于或等于某个值的比例。

select 
*,cume_dist() over(partition by category order by price asc) cd
from goods g

在这里插入图片描述

（三）前后函数

lag(expr,n)函数

返回分组字段的前第n个值

函数功能
LAG(expr, n)返回当前行的前n行(本组)的expr值
–lag允许你在每一个分组内, 从当前行向前看n行数据
–n(也叫offset)是从当前行偏移的行数，以获取值。offset必须是一个非负整数。如果offset为零，则LAG()函数计算当前行的值。如果省略 offset，则LAG()函数默认使用n=1, 向前看一个数据。

查询goods数据表中前一个商品价格与当前商品价格的差值。

select *,lag(price,1) OVER (partition BY category ORDER BY price) AS pre_price
FROM goods

在这里插入图片描述

lead(expr,n)函数

lead(expr,n)函数返回当前行的后n行的expr的值。

select 
*,lead(price,1) over(partition by category order by price desc) after_rk
from goods g

在这里插入图片描述

（四）首尾函数

first_value(expr)函数

first_value(expr)函数返回本组内第一个expr的值

select 
*,first_value(price) over(partition by category order by price desc) frk
from goods g

在这里插入图片描述

last_value(expr)函数

last_value(expr)函数可以返回本组内最后一个expr的值

语法结构：
在 over 子句中，

  • partition by subject 将按照学科进行分区
  • order by grade desc 将每个分区内的行按照成绩逆序排列。
  • range between unbounded preceding and unbounded following 定义了每个分区中与当前行相关的窗口框架，这里是从分区的第一行到最后一行。
  • last_value(grade) 返回每行在其关联的分区的第一行的成绩（grade）。因为是按照 grade 逆序排序，于是每行中添加了一列显示最差的成绩。

要查看在每个科目中每个学生按照成绩从高到低的排序号和每个科目中的最好成绩

select 
  *,
  last_value(grade) over (
  	partition by subject
    order by grade desc
    range between unbounded preceding and unbounded following
  ) last_grade
from student_grade;

在这里插入图片描述

（五）其他函数

nth_value(expr,n)函数

nth_value(expr,n)函数返回分组内第n个expr的值。

例一：

查询goods数据表中排名第1和第2的价格信息

select 
	*,
	nth_value (price,1) over(
		partition by category
		order by price desc 
		range between unbounded preceding and unbounded following 
		) first_rk,
	nth_value (price,2) over(
		partition by category
		order by price desc 
		range between unbounded preceding and unbounded following 
		) second_rk
from goods g

在这里插入图片描述

ntile(n)函数

ntile(n)函数将分区中的有序数据分为n个桶，记录桶编号

描述：

  • ntile() 函数将当前行所在的分区内的所有行尽可能平均的分成指定数量的区间，并返回当前行所在的区间编号。
  • 每个区间， MySQL 称之为一个排名桶。 ntile() 根据指定排序为每个桶指设定排名。

语法：

ntile(buckets)
over (
  [partition by partition_column_list]
  [order by order_column_list]
)

参数:

  • buckets：必需的。桶的数量。桶的数量最大为此分区内的行的数量。
  • partition_column_list：参与分区的列的列表。
  • order_column_list：参与排序的列的列表。

例一：

将qoods表中的商品按照价格分为3组

select 
	*,
	ntile(3) over(
		partition by category
		order by price desc 
		range between unbounded preceding and unbounded following 
		) nt
from goods g

在这里插入图片描述

五、总结

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。

王林-wlin
关注
  • 4
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL窗口函数
weixin_48352526的博客
04-20 206
第5行：由于整数除整数得到的还是整数，而增长率是一个小数，所以要对其中一个数据类型进行修改，cast（gmv as double）就是讲gmv改成double类型的数据。{range|rows} between frame_start and frame_end：从指定行开始到指定行结束。{range|rows} frame_start：range、rows二选一，从指定行开始到当前行结束。lag(expression,n)：返回当前行的前n行。row_number()：序号不重复，序号连续。
Mysql 窗口函数
iijik55的博客
06-06 104
创建表:CREATE TABLE goods( );插入数据:INSERT INTO goods(category_id,category,NAME,price,stock,upper_time) VALUES (1, ‘女装/女士精品’, ‘T恤’, 39.90, 1000, ‘2020-11-10 00:00:00’), (1, ‘女装/女士精品’, ‘连衣裙’, 79.90, 2500, ‘2020-11-10 00:00:00’), (1, ‘女装/女士精品’, ‘卫衣’, 89.90, 1500,
MySQL学习-窗口函数over（）
认真学编程的原始人的博客
10-20 586
窗口函数是OVER(),其中对应子句有PARTITION BY 以及 ORDER BY子句,所以形式有:
MySQL窗口函数OVER()
热门推荐
奋斗的龙猫的博客
10-05 2万+
首先在讲解OVER函数之前，我们先来看一下这一片文章： https://www.jianshu.com/p/e5c5bfb1e28b
MySQL窗口函数（MySQL Window Functions）
Vincent的博客
05-17 1万+
百分比排序，返回当前行在组内的百分比位置，返回值范围为[0, 1]，可以用 当前行排序/(行数-1) 计算得出，但与rank不同，这里排序是从0开始而不是从1（相当于rank-1），因此第1行的百分比位置是0%，相当于：（rank-1）/(rows-1)。没有参数，返回当前行在组内的位置编号，从1开始，order by子句会影响行的编号顺序，如果没有order by，那么行的编号是不确定的。另外，即使行完全相同，它们的编号也是不同的，这点和后面的 rank() 和 dense_rank() 不同。
MySQL 的窗口函数 OVER
01-04 1513
ORDER BY 对 PARTITION BY 窗口中的数据进行排序，当 PARTITION BY 与 ORDER BY 列名不同时，聚合函数是根据排序进行逐个聚合计算的，当碰到 ORDER BY 相同的两个值时，同时计算两个值，并两行数据一致；2）当与 FROM 后面的 ORDER BY 字段不同时，FROM 子句的 ORDER BY 会覆盖 OVER() 中的 ORDER BY，FROM 子句中 ORDER BY 后值相同的才会按照 OVER() 子句中的 ORDER BY 排序；
mysql 之分析函数 over参考 （partition by ...order by ...）
ahmcwt的专栏
12-01 9341
mysql 之分析函数 over 转自:https://www.cnblogs.com/moyijian/p/9905803.html 一：分析函数over Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。 1、分析函数和聚合函数的不同之处： 分析函数和聚合函数很多是同名的，意思也一样，只是聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每
32-视频33MySQL窗口函数
09-13
MySQL窗口函数是数据库查询中的一个重要概念，它在SQL语句中提供了一种处理分组数据的新方式，使得在不使用子查询或者自连接的情况下，也能实现复杂的数据分析和计算。在本节视频“32-视频33MySQL窗口函数”中，我们...
MySQL窗口函数：展开数据的魔法.md
08-15
MySQL窗口函数：展开数据的魔法.md
mysql-窗口函数概述
最新发布
12-21
mysql中窗口函数的概述以及一些数据练习，可以更好的对窗口函数理解。 什么是窗口函数：可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行...
Mysql8.0使用窗口函数解决排序问题
09-08
MySQL 8.0 引入了窗口函数，这是一种强大的数据处理工具，能够在满足特定条件的记录集合（窗口）上执行分析操作，而不仅仅是简单的聚合。窗口函数与传统的聚合函数（如 SUM, AVG, COUNT 等）不同，它们在处理数据时...
MySQL8.0窗口函数入门实践及总结
09-08
MySQL8.0窗口函数是数据库查询中的一个重要特性，它使得在处理复杂的数据分析和统计时变得更加便捷。在MySQL8.0之前，如果要进行类似的数据排名或统计，通常需要借助子查询或自连接等复杂操作，而窗口函数的引入极大...
MySQL窗口函数OVER和GROUP BY语句学习
weixin_47064606的博客
03-22 2646
MySQL窗口函数使用以及对比
SQL语句中OVER函数的用法
weixin_30512043的博客
03-11 993
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。其参数：over（partition by columnname1 order by columnname2）含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。例如：employees表中，有两个部门的记录：department_id...
Mysql_窗口函数_OVER()的简单使用
feizuiku0116的博客
08-21 3071
一.OVER() 描述 OVER()的意思就是所有的数据都在窗口中 实例 OVER() 意思是所有的数据都在窗口中 # 计算所有员工的平均工资 SELECT *, avg(salary) OVER () FROM employee; OVER()用于将当前行与一个聚合值进行比较 # 计算员工与平均薪水之差 SELECT *, salary - avg(salary) OVER () as diffent FROM employee; OVER()和CO
MySQL------窗口函数
CaiJin1217的博客
02-22 1138
怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？累计求和如何计算？连续统计N天登陆的用户？以上类型问题均使用窗口函数可以解决，这类需求都有一个共同的特点，需要在单表中满足某些条件的结果集内部做一些函数操作，对分组统计结果中的每一条记录进行计算的场景下, 使用窗口函数更好, 注意, 是每一条!!因为MySQL的普通聚合函数的结果(如 group> by)是每一组只有一条记录!!!
mysql 窗口函数
07-27
MySQL 窗口函数是一种在查询结果集中进行计算和排序的功能。它们能够对查询结果集中的每一行进行计算，而不依赖于单独的聚合函数或分组操作。以下是一些常见的 MySQL 窗口函数： 1. ROW_NUMBER(): 为结果集中的每一行分配一个唯一的整数值。 2. RANK(): 计算结果集中每一行的排名，如果出现相同的值，将会有相同的排名。 3. DENSE_RANK(): 与 RANK() 类似，但是在出现相同值时不会跳过排名。 4. LAG(): 获取当前行之前指定偏移量的行的值。 5. LEAD(): 获取当前行之后指定偏移量的行的值。 6. FIRST_VALUE(): 返回结果集中第一行的指定列的值。 7. LAST_VALUE(): 返回结果集中最后一行的指定列的值。 8. NTILE(n): 将结果集划分为 n 个等大小的桶，并为每个桶分配一个编号。 这些是只是一些常见的 MySQL 窗口函数，还有其他更多的函数可用。你可以在 MySQL 官方文档中找到更多关于窗口函数的详细信息和用法示例。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值