基于MySQL 8.0的窗口函数

最新推荐文章于 2024-08-01 20:30:12 发布
最新推荐文章于 2024-08-01 20:30:12 发布
阅读量417 收藏
点赞数
分类专栏： MYSQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Becky_Jia/article/details/109547444
版权

一、窗口函数语法

window_function_name (window_name/expression)
over(
	[partition_definition]
	[order_definition]
	[frame_definition])

窗口函数的一个重要概念是当前行，当前行属于某个窗口，窗口由 [partition_definition],[order_definition],[frame_definition]共同确定。

  1. partition by： 窗口按照指定字段进行分区，窗口功能在分区内执行，并且在跨越分区边界时重新初始化；
  2. order by ：按照指定字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition 语句结合使用，也可以单独使用；
  3. frame子句：frame是当前分区的一个子集，在分区中再进一步的细分窗口，通常用来作为滑动窗口使用，>>> 某些窗口函数属于静态窗口，frame子句没有作用

二、常见窗口函数

排名函数 rank(), dense_rank(), row_number() 【静态窗口】

※排序函数：rank() ※

#举例：查看所有部门所有员工的薪资排名
select  department_id, employee_id, salary,
	rank() over() as '薪资排名'
from employees;

在这里插入图片描述

以上结果的原因：因为有3列内容，在over子句中没有明确按照哪一列数据进行排序，因此结果都是1

select  department_id, employee_id, salary,
	rank() over(order by salary DESC) as '薪资排名'
from employees;

在这里插入图片描述

  1. 在over子句中对薪资进行排序(降序)，此时会按照薪资从高到低进行排序
  2. 结果是将所有部门的薪资都放在一起比较，下面将不同部门的薪资拆开进行部门内排序。
  3. 同时，薪资为17000的员工有2个，排名都是2，但是下一个14000的排名自动从4开始，而不是3
select  department_id, employee_id, salary,
	rank() over(partition by department_id order by salary DESC) as '薪资排名'
from employees;

在这里插入图片描述

加入partition by分区，将数据结果按照部门进行分区，排序之后，再将所有部门的排序结果拼接在一张表中
20 部门薪资从1开始排序，30部门的薪资也是从 开始排序

※排序函数：dense_rank()

select  department_id, employee_id, salary,
	dense_rank() over(partition by department_id order by salary DESC) as '薪资排名'
from employees; #使用dense_rank()进行排序

在这里插入图片描述

此时的排名已经没有间隔了，连续进行排名

※排序函数：row_number()

select  department_id, employee_id, salary,
		row_number() over(partition by department_id order by salary desc) as '序号'
from employees;

在这里插入图片描述

仅仅返回当前行号，并不能进行准确的排名

练习：查询每个部门的员工总薪资，并排名

select  department_id, SUM(salary) as total_salary,
		rank() over(order by sum(salary) desc) as '部门薪资排名'
from employees
group by department_id;

在这里插入图片描述

执行顺序：先从employee表中，获取每个部门的总薪资，然后再通过窗口函数进行排名
按照以上顺序，在排名之前已经按照部门编号汇总了，所以over子句中，不需要再加入partition by内容，只需要将总计薪资排序即可

聚合函数 sum(), avg() 【滑动窗口】

※聚合函数：sum()

select department_id, employee_id, salary,
	sum(salary) over() as total_salary
from employees;

在这里插入图片描述

以上结果，将所有窗口下所有的薪资进行加和，是一个静态的窗口，所以结果都一样

select department_id, employee_id, salary,
	sum(salary) over(order by salary) as total_salary
from employees;

在这里插入图片描述

加入了order by子句，将salary从低到高进行排序，此时默认累计加和的结果是从分区的第一行到当前行
比如第2行和第3行的结果6500 = 2100(第一行的值) + 2200(第二行的值)+2200(第三行的值)
注意：此处2200计算2次是因为，在order by子句中，只对salary进行了排序，因此不能明确哪一个2200要先被计算，所有都被加总了
如果要明确拆分重复数值，需要在order by子句后添加新的排序字段

select department_id, employee_id, salary,
	sum(salary) over(order by salary, employee_id) as total_salary
from employees;

在这里插入图片描述

以上结果中，因为对salary和employee_id同时排序，因此结果就比较明确是从第一行累计到当前行。
依旧是将所有部门的员工薪资一起累加，下面查看每个部门累加的结果

select department_id, employee_id, salary,
	sum(salary) over(partition by department_id order by salary, employee_id) as total_salary
from employees;
#在over()子句中添加了分区partition by部门编号

在这里插入图片描述

以上结果中，明确地在跨越新的分区时，重新格式化结果，再进行计算

※聚合函数：avg()

select department_id, employee_id, salary,
	avg(salary) over(partition by department_id order by salary, employee_id) as avg_salary
from employees;

在这里插入图片描述

以上结果中，部门编号20的9500是(6000+13000)/2 = 9500
从所在分区的第一行到当前行的平均值
下面，添加frame子句查看结果

select department_id, employee_id, salary,
	avg(salary) over(partition by department_id order by salary, employee_id 
	rows between unbounded preceding and current row) as avg_salary
from employees;

在这里插入图片描述

添加rows between and 子句，从起始行到当前行，与不添加frame子句结果一致

select department_id, employee_id, salary,
	avg(salary) over(partition by department_id order by salary, employee_id 
	rows between 2 preceding and 1 following ) as avg_salary
from employees;

在这里插入图片描述

以上结果中，滑动平均的区域是，向前2行，向后1行，累计4行结果的平均值，这个范围是分区中的一个子窗口
移动平均，一般会在季节性明显，波动较大的时候，会选择计算移动平均结果（前3天的移动平均，前一个月的移动平均）

select department_id, employee_id, salary,
	avg(salary) over(partition by department_id order by salary, employee_id 
	rows between 2 preceding and current row ) as avg_salary
from employees;  #向前2行加上当前行 --前3天的移动平均

案例：查看部门的平均薪资，并按照平均薪资降序排列

#使用rank()over()函数查询
select department_id, avg(salary) as '平均薪资',
		rank() over(order by avg(salary) DESC) as '部门薪资排名'
from employees
group by department_id;

在这里插入图片描述

#在over()子句中进行分区分组，最后通过order by子句进行排序
select DISTINCT department_id,
		avg(salary) over(partition by department_id) as 'avg_salary'
from employees
order by avg_salary desc;

在这里插入图片描述

结果与第一种方法保持一致
前后函数：lag(), lead() 【静态窗口】

向前函数：lag()

select department_id, employee_id, salary,
		lag(salary, 1, 0) over() as '前一个薪资'
from employees;

在这里插入图片描述

以上结果中，lag(salary, 1,0)表示向前取一个结果，如果前面对应内容，则返回0

select department_id, employee_id, salary,
		lag(salary, 1) over(partition by department_id order by salary desc) as '前一个薪资'
from employees;

在这里插入图片描述

以上结果中，lag(salary，1)，没有定义查询不到时的返回值，对应得到的为null值
over()子句中依旧按照部门进行分区，并且按照薪资进行了排序

向后函数：lead()

select department_id, employee_id, salary,
		lead(salary, 3) over(partition by department_id order by salary desc) as '后三个薪资'
from employees;

在这里插入图片描述

一般前后函数会用来查询用户上一次登录时间，然后判断用户使用的频率等信息。

#举例：计算一个用户连续（user_name, orderdate(默认每天只购买一次)）
select user_name, 
FROM (
	select *, lead(orderdate, 2) over(PARTITION by user_name) as 'order3_date' from ord) as a
where datediff(order3_date,orderdate)=2;
#lead(orderdate,2) 获取第三次购买的日期
#datediff(order3_date, orderdate)=2 判断第三次购买和第一次购买之间间隔2天

#以上过程，如果不使用窗口函数需要进行多次连接
select user_name
from ord join ord o1 
on ord.user_name = o1.user_name and ord.orderdate = data_add(o1.orderdate, INTERVAL 1 day)
join ord  o2
on ord.user_name = o2.user_name and ord.orderdate = data_add(o2.orderdate, INTERVAL 2 day)

头尾函数：first_value(), last_value() 【滑动窗口】

头部函数：first_value()

select department_id, employee_id, salary,
	first_value(salary) over(partition by department_id order by salary desc) as '最高薪资'
from employees;

在这里插入图片描述

select department_id, employee_id, salary,
	first_value(salary) over(	order by salary desc rows between 2 preceding and 1 following) as '区域最高薪资'
from employees;
#向前取2行，向下取1行的小范围内的第一个值

在这里插入图片描述

尾部函数：last_value()

select department_id, employee_id, salary,
	last_value(salary) over(order by salary desc rows between 2 preceding and 1 following) as '区域最低薪资'
from employees;

在这里插入图片描述

select department_id, employee_id, salary,
	last_value(salary) over(order by salary desc rows between 2 preceding and 1 preceding) as '区域最低薪资'
from employees;
#当前行向前2行至向前1行区域内的最后一个值

在这里插入图片描述
分布函数：percent_rank(), cume_dist() 【静态窗口】

分布函数：percent_rank()

select department_id, employee_id, salary,
	rank() over(partition by department_id  order by salary desc ) as '排名',
	percent_rank() over(partition by department_id  order by salary desc ) as '排名%'
from employees;

在这里插入图片描述

percent_rank() 返回的是当前的（rank-1)/(当前窗口的行数-1）
可以查看TOP 20%, TOP 50% 的情况

分布函数：cume_dist()

select department_id, employee_id, salary,
	rank() over(partition by department_id  order by salary desc ) as '排名',
	cume_dist() over(partition by department_id  order by salary desc ) as '排名cume'
from employees;

在这里插入图片描述

cume_dist()返回的是当前的 rank值/当前窗口的总行数，与percent_rank()存在差异

其他函数：nth_value() 【滑动窗口】，ntile() 【静态窗口】

其他函数：nth_value()

select department_id, employee_id, salary,
	nth_value(salary, 2) over(partition by department_id) as nth
from employees;

在这里插入图片描述

nth_value(salary, 2) 返回当前区的第2行salary结果

select department_id, employee_id, salary,
	nth_value(salary, 2) over(partition by department_id order by salary DESC) as nth
from employees;

在这里插入图片描述

在添加了order by子句之后，变成一个滑动窗口，从分区的第一行到当前行，因此在每个分区的第一行返回结果为null值

其他函数：ntile()

select department_id, employee_id, salary,
	ntile(6) over() #将数据分组，分成6组
from employees;

在这里插入图片描述

select department_id, employee_id, salary,
	ntile(3) over(partition by department_id)
from employees;

在这里插入图片描述

在分区之后，再进行分组
当数据量比较大的时候，可以使用ntile函数，来查看一部分数据，ntile(10)=5选择中间部分的数值进行查看

frame子句

range between and （基于值确定范围）
rows between and （基于行数确定范围）

select department_id, employee_id, salary,
	sum(salary) over(partition by department_id order by salary desc range between 500 preceding and 500 following) as 'newsal'
from employees;

在这里插入图片描述

以上使用了range来确定滑动的窗口，range between 500 preceding and 500 following, 表示当前行的值减去500到加上500之间的范围
2900-500 = 2400,2900+500 = 3400，要求对部门编号是30，薪资范围在2400-3400之间的薪资进行加和：2500+2600+2800+2900+3100=13900

大贝祺的海
关注
专栏目录
MySQL8.0------窗口函数
R418520的博客
08-22 189
MySQL8.0窗口函数概述 MYSQL 8.0 之后，加入了窗口函数功能，简化了数据分析工作中查询语句的书写 在没有窗口函数之前，我们需要通过定义临时变量和大量的子查询才能完成的工作，使用窗口函数会更加简洁高效 优点：简单/快速/多功能性 1、聚合函数 over() 注意事项： 可以使用<window_function> OVER()，对全部查询结果进行 聚合计算在WHERE条件执行之后，才会执行窗口函数 ..
MySql8.0窗口函数
季风的博客
12-17 671
一、基本概念 MySQL从8.0开始支持窗口函数，这个功能在大多商业数据库和部分开源数据库中早已支持，有的也叫分析函数。 概念： 窗口的概念可以理解为记录集合；窗口函数也就是在满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种是静态窗口；有的函数则是不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。 窗口函数与聚合函数： 聚合函数是将多条记录聚合为一条； 窗口函数是每条记录都会执行，有几条记录执行完还是几条； 聚合函数也可以
Mysql 窗口函数
热门推荐
菜菜的大数据开发の路
03-04 3万+
一, MySQl 8.0 窗口函数 窗口函数适用场景: 对分组统计结果中的每一条记录进行计算的场景下, 使用窗口函数更好; 可以跟Hive的对比着看: 点我, 特么的花了一晚上整理, 没想到跟Hive 的基本一致, 还不因为好久没复习博客了, 淦 注意: mysql 因为没有array数据结构, 无法像Hive一样 行列进行转换; 1.1 窗口函数分类 MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是
MySQL高阶(九)——窗口函数
最新发布
weixin_58305115的博客
08-01 1430
- 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。 - 窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。最重要的是，窗口函数具有多种功能，比如：求差值、求占比、求排名、累计值计算等等。
mysql8.0 中的窗口函数
不忘初欣丶的博客
03-26 708
mysql中的窗口函数
mysql8.0的窗口函数
归来仍少年
11-10 349
mysql8.0的窗口函数 1.窗口：记录集合 2.静态窗口：窗口大小都是固定的 3.滑动窗口：不同的记录对应着不同的窗口 MySQL支持的窗口函数类型： 序号函数:row_number()/rank()/dense_rank() 分布函数:percent_rank()/cume_dist() 前后函数:lag()/lead() 头尾函数:first_val()/last_val() 其他函数:nth_value()/nfile() 基本语法： 函数名（[expr]） over子句 子句类型: par
MYSQL8.0窗口函数
cristianoxm的博客
10-03 1371
Mysql8.0的窗口函数。不同的是，。比如在MYSQL的最新版本MYSQL8已经。但是在就旧版本中还不支持这些函数，只能自己实现。实现方法主要。
【译】MySQL 8.0 函数索引
jmysql的博客
04-29 1829
2022Java毕业设计项目全套，进来白嫖_哔哩哔哩_bilibili 前言 与数百个不同的客户打交道时，我经常在查询方面遇到类似的问题。 在尝试优化数据库环境时，一个非常常见的问题是索引使用。 无法使用索引的查询通常是长时间运行的查询，会消耗更多内存或触发更多磁盘 iops。 一个非常常见的情况是查询对涉及某种函数表达式的列使用过滤条件。无法使用该列上的索引。 从 MySQL 8.0.13 开始支持函数索引。在本文中，我将展示它们是什么以及它们是如何工作的。 众所周知的问题 如前所述，关于索引
mysql8.0官方文档离线版
05-27
这份“mysql8.0官方文档离线版”包含了关于MySQL 8.0的所有详细信息，包括安装、配置、优化、备份、恢复、安全性、SQL语法、存储引擎、触发器、视图、索引、事务处理等各个方面的内容。以下是对这些知识点的详细阐述...
mysql8.0.zip
12-28
MySQL 8.0 是世界上最流行的开源关系型数据库管理系统之一。 特性和改进： JSON 支持：MySQL 8.0 引入了对 JSON 格式...Window Functions：MySQL 8.0 引入了窗口函数，可以执行类似于分析函数的计算，如排名、排序等。
【MySQL】MySQL 8.0 新特性之 - 窗口函数（Window Functions）
迪曼奥特迦-博客
02-04 1920
窗口可以理解为记录集合，窗口函数就是在满足某种条件的记录集合上执行的特殊函数。每条记录都要在此窗口内执行函数。静态窗口：每条记录都要在此窗口内执行函数，窗口大小都是固定的。不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。窗口函数也称为 OLAP（Online Anallytical Processing）函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能。窗口函数对一组查询行执行类似聚合的操作。然而，聚合操作将查询行分组为单个结果行，而。
【MySQL 8.0新特性】窗口函数
沉迷写代码的程序猿的博客
11-14 1536
什么是窗口函数？窗口函数是MySQL8.0新增的，窗口函数又称开窗函数，属于MySQL的一大特点。非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。窗口函数有什么用？窗口函数可以对数据库数据进行实时分析处理，比如市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数分为哪几种？序号函数。
MySQL8数据库窗口函数
qq_30505673的博客
09-16 2057
对于窗口函数，比如row_number()，rank()，dense_rank()，NTILE()，PERCENT_RANK()等等，现在MySQL8版本已经支持了。 row_number()(分组)排序编号 row_number()over(partition by user_no order by create_date desc) as row_num，按照用户分组，按照create_da...
MYSQL8窗口函数
识途老码
04-12 761
MYSQL8窗口函数
MySQL8之窗口函数
zjjcchina的博客
04-02 661
作为一名数据分析师，日常工作中经常会遇到类似这样的需求： 怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？累计求和如何计算？ 对于这样的需求，使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点，需要在单表中满足某些条件的结果集内部做一些函数操作，不是简单的表连接，也不是简单的聚合可以实现的，通常费了大半天时间写出来一堆长长的晦涩难懂的SQL，且性能低下，难以维护。要解决此类问题，最方便的就是使用窗口函数。 1.窗口函数初识 MySQL从8.0开始支持窗口函数，这
MySQL8窗口函数
qq_44134480的博客
06-27 345
在日常开发工作中，经常会遇到下面这些需求去医院看病，怎样知道上次就医距现在的时间？环比如何计算？怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？如果用传统SQL来解决这些问题，理论上是可以的，但逻辑会相当复杂。这类需求都有一个共同的特点，为了的到结果，都需要在某个结果集内做一些特定的函数操作。为了很方便的解决这一类问题，MySQL8.0中引入了窗口函数。窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。
MySQL 8.0的窗口函数革新：提升数据分析效率
MySQL 8.0的新特性之一是引入了窗口函数（Window Functions），这是一个重要的增强功能，它使得在SQL查询中能够进行复杂的数据分析和处理变得更加容易。窗口函数允许开发者在单个查询中定义一个范围，称为窗口，对...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值