MySQL窗口函数

于 2024-06-22 18:29:04 发布
阅读量612 收藏 23
点赞数 16
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_55909023/article/details/139886303
版权

窗口函数

窗口函数是用于在查询结果集中执行复杂计算的函数。与聚合函数不同，窗口函数不会将多行数据压缩成单行数据，而是在保留行细节的同时进行计算。窗口函数允许在查询结果集中的某一部分（称为窗口或帧）内进行操作，从而实现如累计和、排名、移动平均等复杂计算。

除了常见的聚合函数，窗口函数还有一些特殊的函数。例如，RANK()、DENSE_RANK()、ROW_NUMBER()等函数都是窗口函数特有的，它们可以在数据分析中提供更多的灵活性和便利性。这些函数通常与OVER子句一起使用，以确定窗口的范围和排序。

其基本语法为：

函数名 OVER ([PARTITION BY 列名] [ORDER BY 列名][ACE|EDSC])

其中函数名包括聚合函数和窗口函数专用的函数。

假如我们有一张名为 sales 的表，如下所示：

+---------+------------+--------+----------------+
| sale_id | sale_date  | amount | salesperson_id |
+---------+------------+--------+----------------+
|       1 | 2024-06-01 | 100.00 |              1 |
|       2 | 2024-06-02 | 150.00 |              2 |
|       3 | 2024-06-02 | 200.00 |              1 |
|       4 | 2024-06-03 | 120.00 |              3 |
|       5 | 2024-06-03 | 180.00 |              2 |
|       6 | 2024-06-04 | 220.00 |              1 |
|       7 | 2024-06-04 | 100.00 |              1 |
|       8 | 2024-06-05 | 150.00 |              2 |
+---------+------------+--------+----------------+

RANK()、DENSE_RANK()、ROW_NUMBER()

先来看一下着三个函数的区别。

函数描述示例
RANK()RANK() 函数返回唯一的排名值，但在遇到相同的值时会跳过一些排名。例如，如果有两行具有相同的值，则它们都会被赋予相同的排名，但下一个排名值将会跳过。1, 2, 2, 4
DENSE_RANK()DENSE_RANK() 函数也返回唯一的排名值，但不会跳过任何排名。即使有两行具有相同的值，下一个排名值也不会被跳过。1, 2, 2, 3
ROW_NUMBER()ROW_NUMBER() 函数为结果集中的每一行分配一个唯一的数字。即使有两行具有相同的值，它们也会被赋予不同的数字。1, 2, 3, 4

下面我们逐一的进行介绍：

  • **RANK()：**为相同数值的行分配相同的排名，并跳过下一个排名。

    第一种方式：

    select sale_id, sale_date, amount, 
       rank() over (partition by sale_date order by amount desc) as sales_rank
from sales;

+---------+------------+--------+------------+
| sale_id | sale_date  | amount | sales_rank |
+---------+------------+--------+------------+
|       1 | 2024-06-01 | 100.00 |          1 |
|       3 | 2024-06-02 | 200.00 |          1 |
|       2 | 2024-06-02 | 150.00 |          2 |
|       5 | 2024-06-03 | 180.00 |          1 |
|       4 | 2024-06-03 | 120.00 |          2 |
|       6 | 2024-06-04 | 220.00 |          1 |
|       7 | 2024-06-04 | 100.00 |          2 |
|       8 | 2024-06-05 | 150.00 |          1 |
+---------+------------+--------+------------+
8 rows in set (0.00 sec)

    第二种方式：

    select sale_id, sale_date, amount, 
       rank() over (order by amount desc) as sales_rank
from sales;

+---------+------------+--------+------------+
| sale_id | sale_date  | amount | sales_rank |
+---------+------------+--------+------------+
|       6 | 2024-06-04 | 220.00 |          1 |
|       3 | 2024-06-02 | 200.00 |          2 |
|       5 | 2024-06-03 | 180.00 |          3 |
|       2 | 2024-06-02 | 150.00 |          4 |
|       8 | 2024-06-05 | 150.00 |          4 |
|       4 | 2024-06-03 | 120.00 |          6 |
|       1 | 2024-06-01 | 100.00 |          7 |
|       7 | 2024-06-04 | 100.00 |          7 |
+---------+------------+--------+------------+
8 rows in set (0.00 sec)

    两种方式的结果不同，这是因为partition by sale_date，设定了排序的对象范围，才会出现上面的结果。

    同时从这两个例子中也可以看到partition by不是必须的，即使不指定也能正常使用窗口函数。也能看出窗口函数同时具备分组和排序两种功能。

  • **DENSE_RANK()：**为相同数值的行分配相同的排名，但不跳过下一个排名。

    select sale_id, sale_date, amount, 
       dense_rank() over (order by amount desc) as dense_sales_rank
from sales;

+---------+------------+--------+------------------+
| sale_id | sale_date  | amount | dense_sales_rank |
+---------+------------+--------+------------------+
|       6 | 2024-06-04 | 220.00 |                1 |
|       3 | 2024-06-02 | 200.00 |                2 |
|       5 | 2024-06-03 | 180.00 |                3 |
|       2 | 2024-06-02 | 150.00 |                4 |
|       8 | 2024-06-05 | 150.00 |                4 |
|       4 | 2024-06-03 | 120.00 |                5 |
|       1 | 2024-06-01 | 100.00 |                6 |
|       7 | 2024-06-04 | 100.00 |                6 |
+---------+------------+--------+------------------+
8 rows in set (0.00 sec)

  • ROW_NUMBER()： 给查询结果中的每一行分配一个唯一的序号。

    select sale_id, sale_date, amount, 
       row_number() over (order by sale_date) as row_num
from sales;

+---------+------------+--------+---------+
| sale_id | sale_date  | amount | row_num |
+---------+------------+--------+---------+
|       1 | 2024-06-01 | 100.00 |       1 |
|       2 | 2024-06-02 | 150.00 |       2 |
|       3 | 2024-06-02 | 200.00 |       3 |
|       4 | 2024-06-03 | 120.00 |       4 |
|       5 | 2024-06-03 | 180.00 |       5 |
|       6 | 2024-06-04 | 220.00 |       6 |
|       7 | 2024-06-04 | 100.00 |       7 |
|       8 | 2024-06-05 | 150.00 |       8 |
+---------+------------+--------+---------+
8 rows in set (0.00 sec)

常用的聚合函数另外的用法

常用的聚合函数可以帮助我们对窗口内的数据进行汇总和计算，而不需要分组。这些函数能够以更灵活的方式处理数据，例如计算累积总和、计算移动平均等。

以下是一些常用的聚合函数在窗口函数中的应用示例：

  • SUM() OVER()： 计算指定列的累积总和。

    -- 计算累积总和。
select sale_id, sale_date, amount,
       sum(amount) over (order by sale_date) as running_total
from sales;

+---------+------------+--------+---------------+
| sale_id | sale_date  | amount | running_total |
+---------+------------+--------+---------------+
|       1 | 2024-06-01 | 100.00 |        100.00 |
|       2 | 2024-06-02 | 150.00 |        450.00 |
|       3 | 2024-06-02 | 200.00 |        450.00 |
|       4 | 2024-06-03 | 120.00 |        750.00 |
|       5 | 2024-06-03 | 180.00 |        750.00 |
|       6 | 2024-06-04 | 220.00 |       1070.00 |
|       7 | 2024-06-04 | 100.00 |       1070.00 |
|       8 | 2024-06-05 | 150.00 |       1220.00 |
+---------+------------+--------+---------------+
8 rows in set (0.00 sec)

    -- 计算分天累积总和。
select sale_id, sale_date, amount,
       sum(amount) over (partition by sale_date order by sale_date) as running_total
from sales
order by
    sale_date, sale_id;

+---------+------------+--------+----------------------+
| sale_id | sale_date  | amount | daily_cumulative_sum |
+---------+------------+--------+----------------------+
|       1 | 2024-06-01 | 100.00 |               100.00 |
|       2 | 2024-06-02 | 150.00 |               150.00 |
|       3 | 2024-06-02 | 200.00 |               350.00 |
|       4 | 2024-06-03 | 120.00 |               120.00 |
|       5 | 2024-06-03 | 180.00 |               300.00 |
|       6 | 2024-06-04 | 220.00 |               220.00 |
|       7 | 2024-06-04 | 100.00 |               320.00 |
|       8 | 2024-06-05 | 150.00 |               150.00 |
+---------+------------+--------+----------------------+
8 rows in set (0.00 sec)

    巧妙的利用partition by会有出色的效果

  • AVG() OVER()： 计算指定列的移动平均值。

    select sale_id, sale_date, amount,
       avg(amount) over (order by sale_date rows between 1 preceding and current row) as moving_avg
from sales;

+---------+------------+--------+------------+
| sale_id | sale_date  | amount | moving_avg |
+---------+------------+--------+------------+
|       1 | 2024-06-01 | 100.00 | 100.000000 |
|       2 | 2024-06-02 | 150.00 | 125.000000 |
|       3 | 2024-06-02 | 200.00 | 175.000000 |
|       4 | 2024-06-03 | 120.00 | 160.000000 |
|       5 | 2024-06-03 | 180.00 | 150.000000 |
|       6 | 2024-06-04 | 220.00 | 200.000000 |
|       7 | 2024-06-04 | 100.00 | 160.000000 |
|       8 | 2024-06-05 | 150.00 | 125.000000 |
+---------+------------+--------+------------+
8 rows in set (0.00 sec)

    这里计算每笔销售及其前一笔销售的平均销售额，这被称为移动平均值。

GaGa，
关注
mysql窗口函数同比环比_MySQL利用窗口函数解决排名问题
weixin_33654339的博客
02-08 912
从MySQL8.0开始，MySQL增加了对窗口函数的支持。本文主要介绍如何利用窗口函数row_number(),dense_rank()和rank()解决相关排名问题。说明：本文中的SQL代码均测试于MySQL8.0.13环境。在总体上进行排名：# 创建学生成绩表：create table grade(id int,name varchar(10),score int (10))engine my...
MySQL之rank() over(order by)
Super乐De博客
04-01 4944
一、准备工作 创建一个表palyers： CREATE TABLE `players` ( `pid` int(2) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `age` int(2) NOT NULL, PRIMARY KEY (`pid`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `play
mysql—排序函数rank() over()、dense_rank() over()、row_num() over()
weixin_44262119的博客
01-12 3902
转载文章 原文链接：mysql—排序函数rank() over()、dense_rank() over()、row_num() over() - beyondChan - 博客园 (cnblogs.com) 1.rank() over() 查出指定条件后进行排名。特点是，加入是对学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。 rank()over(partitionbysubjectorderbyscoredesc) rank 2.dense...
MySQL中的序号开窗函数（RANK OVER）如何使用
fuxinxinxin1的博客
06-02 2948
2.2 DENSE_RANK（）OVER（列名 ORDER BY 排序方式（DESC/ASC））dense_rank() 使用排序时，列中出现相同数值时，会并列序号，但是不会像rank函数去出现不连续序号，dense_rank()会在下一个排序依然是1-n的顺序。2.1 RANK（）OVER（列名 ORDER BY 排序方式（DESC/ASC）） rank函数在使用中可以把数据进行排序，当出现排序列数值相同时进行并列排序，1-n可能出现不连续现象， 如图。当出现并列的数值时，下一个排序不会1-n的顺序。
窗口函数的知识
weixin_46801232的博客
09-18 611
窗口函数的知识： 一、<窗口函数>的位置，可以放以下两种函数： 1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。 2） 聚合函数，如sum. avg, count, max, min等 二、用法 因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。 简单来说，窗口函数有以下功能： 1）同时具有分组和排序的功能 2）不减少原表的行数 3）语法如下： <窗口函数>
32-视频33MySQL窗口函数
最新发布
09-13
MySQL窗口函数是数据库查询中的一个重要概念，它在SQL语句中提供了一种处理分组数据的新方式，使得在不使用子查询或者自连接的情况下，也能实现复杂的数据分析和计算。在本节视频“32-视频33MySQL窗口函数”中，我们...
Mysql 窗口函数rank
qq_22945381的博客
09-16 752
窗口函数 MySQL8.0之后支持窗口函数。窗口指的是记录集合，窗口函数是指在某种条件的记录集合上执行的特殊函数。静态窗口是指不同的记录对应的窗口大小是固定的，而滑动窗口是指随着记录的不同窗口的大小是动态变化的。 窗口函数分类 1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。 2） 聚合函数，如sum. avg, count, max, min等 注意事项 窗口函数原则上只能写在select子句中，因为窗口函数是对where或者group by子句
MySQL rank() over、dense_rank() over、row_number() over 用法介绍
热门推荐
大话家的博客
10-20 7万+
rank() over(业务逻辑) 作用：查出指定条件后的进行排名。特点是，加入是对学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。
mysql 之 rank() over()
有教无类的专栏
10-11 1万+
SET @rn=0; SET @dense_rank=0; SET @last_deptno=-1; SET @last_sal=-1; SET @ADD = 0; SELECT deptno, empno, ename, sal, IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS
mysql 之分析函数 over参考 （partition by ...order by ...）
ahmcwt的专栏
12-01 9642
mysql 之分析函数 over 转自:https://www.cnblogs.com/moyijian/p/9905803.html 一：分析函数over Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。 1、分析函数和聚合函数的不同之处： 分析函数和聚合函数很多是同名的，意思也一样，只是聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每
【数据库】row_number()/rank() over(PARTITION BY xxx ORDER BY的MySQL5.7实现
E_vens
10-29 5797
row_number()/rank() over(PARTITION BY class ORDER BY的MySQL5.7实现
mysql之rank() over(order by)、rank() over(partition by order by)
元气满满晨
09-03 2万+
一、准备工作 创建一个表palyers： CREATE TABLE `players` ( `pid` int(2) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `age` int(2) NOT NULL, PRIMARY KEY (`pid`), UNIQUE KEY `name` (`name`) ) ENGI...
MYSQL（排序）：Rank() over()的用法
Bunnyccy的博客
09-09 1429
参考：https://www.cnblogs.com/mycoding/archive/2010/05/29/1747065.html 创建一个test表，并插入6条数据。 CREATE TABLE test ( a INT, b INT, c CHAR ) INSERT INTO test VALUES(1,3,‘E’) INSERT INTO test VALUES(2,4,‘A’) INSERT INTO test VALUES(3,2,‘D’) INSERT INTO test VALUES(3,
Rank() over()的用法
weixin_30245867的博客
05-29 1268
Rank() over()的用法 创建一个test表，并插入6条数据。 CREATE TABLE test ( a INT, b INT, c CHAR ) INSERT INTO test VALUES(1,3,'E') INSERT INTO test VALUES(2,4,'A') INSERT INTO te...
mysql rankover函数_mysql窗口函数rank() over、dense_rank() over、row_number() over 使用心得...
weixin_35064481的博客
02-09 1386
一枚小产品的学习记录在做sql练习题时了解到的新函数，做个简要的记录，若有不严谨的地方，请指正。mysql 8.0以上版本才支持窗口函数以下是个人总结row_number()over(partition by字段1 order by 字段2) 的结果是每一行记录生成一个序号，依次排序且排序的序号不会重复rank()over(partition by字段1 order by 字段2) 的结果会考虑...
mysql 窗口函数
07-27
MySQL 窗口函数是一种在查询结果集中进行计算和排序的功能。它们能够对查询结果集中的每一行进行计算，而不依赖于单独的聚合函数或分组操作。以下是一些常见的 MySQL 窗口函数： 1. ROW_NUMBER(): 为结果集中的每一...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值