MySQL8新特性窗口函数详解

最新推荐文章于 2024-09-20 00:00:00 发布
最新推荐文章于 2024-09-20 00:00:00 发布
阅读量65 收藏
点赞数
文章标签： java spring boot
原文链接：https://mp.weixin.qq.com/s/O1avwCLI_72BDII6krb5Hg
版权

原创 waynaqua waynblog 2023-06-14 23:02 发表于湖北

本文博主给大家详细讲解一波 MySQL8 的新特性：「窗口函数」，相信大伙看完一定能有所收获🤓。

  • 本文提供的 sql 示例都是基于 MySQL8，由博主亲自执行确保可用

  • 博主github地址：http://github.com/wayn111 ，欢迎大家关注，点个star

简介

MySQL8 窗口函数是一种特殊的函数，它可以在一组查询行上执行类似于聚合的操作，但是不会将查询行折叠为单个输出行，而是为每个查询行生成一个结果。窗口函数可以用来处理复杂的报表统计分析场景，例如计算移动平均值、累计和、排名等。其中博主认为它展现的主要威力在于「它能够让我们在不修改原有语句输出结果的基础上，直接添加新的聚合字段」。

一. 语法解析

窗口函数语法如下：

window_function_name ( [argument1, argument2, ...] ) 
OVER  (
  [ PARTITION BY col1, col2, ... ]
  [ORDER BY col3, col4, ...]
  [ ROWS | RANGE frame_start AND frame_end ]
)

window_function_name

window_function_name 函数可以是聚合函数或者非聚合函数。MySQL8 支持以下几类窗口函数，

  1. 序号函数：用于为窗口内的每一行生成一个序号，例如 ROW_NUMBER()，RANK()，DENSE_RANK() 等。

  2. 分布函数：用于计算窗口内的每一行在整个分区中的相对位置，例如 PERCENT_RANK()，CUME_DIST() 等。

  3. 前后函数：用于获取窗口内的当前行的前后某一行的值，例如 LAG()，LEAD() 等。

  4. 头尾函数：用于获取窗口内的第一行或最后一行的值，例如 FIRST_VALUE()，LAST_VALUE() 等。

  5. 聚合函数：用于计算窗口内的某个字段的聚合值，例如 SUM()，AVG()，MIN()，MAX() 等。

图片

MySQL官网提供

OVER

OVER 关键字很重要，用来标识是否使用窗口函数，语法如下

over_clause:
    {OVER (window_spec) | OVER window_name}

两种形式都定义了窗口函数应该如何处理查询行。它们的区别在于窗口是直接在 OVER() 中定义，还是基于 window_name 在 OVER 字句可以重复使用。

  1. OVER() 常规用法，窗口规范直接出现在 OVER 子句中的括号之间。

  2. OVER window_name 基于 Named Windows，是由查询中其他地方的 WINDOW 子句定义的窗口规范的名称，可以重复使用。本文后续会进行讲解。

PARTITION BY

PARTITION BY子句用来将查询结果划分为不同的分区，窗口函数在每个分区上分别执行，语法如下

partition_clause:
    PARTITION BY expr [, expr] ..

ORDER BY

ORDER BY 子句用来对每个分区内的查询结果进行排序，窗口函数将按照排序后的顺序进行计算，语法如下

order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

frame_clause

frame_clause 是窗口函数的一个可选子句，用来指定每个分区内的数据范围，可以是静态的或动态的。语法如下

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

其中，frame_units表示窗口范围的单位，可以是ROWS或RANGE。ROWS表示基于行数，RANGE表示基于值的大小。frame_extent表示窗口范围的起始位置和结束位置，可以是以下几种形式：

  • CURRENT ROW: 表示当前行。

  • UNBOUNDED PRECEDING: 表示分区中的第一行。

  • UNBOUNDED FOLLOWING: 表示分区中的最后一行。

  • expr PRECEDING: 表示当前行减去expr的值。

  • expr FOLLOWING: 表示当前行加上expr的值。

例如，如果指定了ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING，则表示窗口范围包括当前行、前两行和后一行。如果指定了RANGE BETWEEN 10 PRECEDING AND CURRENT ROW，则表示窗口范围包括当前行和值在当前行减去10以内的所有行。如果没有指定frame_clause，则默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从分区开始到当前行。

图片

引用自网上

二. Named Windows

MySQL8的 Named Windows 是指在 WINDOW 子句中定义并命名的窗口，可以在 OVER 子句中通过窗口名来引用。使用 Named Windows 的好处是可以避免在多个OVER子句中重复定义相同的窗口，而只需要在 WINDOW 子句中定义一次，然后在 OVER 子句中引用即可。例如，下面的查询使用了三个相同的窗口：

SELECT
  val,
  ROW_NUMBER () OVER (ORDER BY val) AS 'row_number',
  RANK () OVER (ORDER BY val) AS 'rank',
  DENSE_RANK () OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;

可以使用Named Windows来简化为：

SELECT
  val,
  ROW_NUMBER () OVER w AS 'row_number',
  RANK () OVER w AS 'rank',
  DENSE_RANK () OVER w AS 'dense_rank'
FROM numbers WINDOW w AS (ORDER BY val);

这样就只需要在 WINDOW 子句中定义一个名为w的窗口，然后在三个OVER子句中引用它。

如果一个 OVER 子句使用了 OVER (window_name ...) 而不是 OVER window_name，则可以在引用的窗口名后面添加其他子句来修改窗口。例如，下面的查询定义了一个包含分区的窗口，并在两个 OVER 子句中使用不同的排序来修改窗口：

SELECT
  DISTINCT year, country,
  FIRST_VALUE (year) OVER (w ORDER BY year ASC) AS first,
  FIRST_VALUE (year) OVER (w ORDER BY year DESC) AS last
FROM sales WINDOW w AS (PARTITION BY country);

这样就可以根据不同的排序来获取每个国家的第一年和最后一年。

一个命名窗口的定义本身也可以以一个窗口名开头。这样可以实现窗口之间的引用，但不能形成循环。例如，下面的查询定义了三个命名窗口，其中第二个和第三个都引用了第一个：

SELECT
  val,
  SUM(val) OVER w1 AS sum_w1,
  SUM(val) OVER w2 AS sum_w2,
  SUM(val) OVER w3 AS sum_w3
FROM numbers
WINDOW
  w1 AS (ORDER BY val),
  w2 AS (w1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  w3 AS (w2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

这样就可以根据不同的范围来计算每个值的累计和。

三. SQL 示例

下面以一个简单的示例表来说明 MySQL8 窗口函数的用法，提前准备 sql 脚本如下

CREATE TABLE `sales` (
  `id` int NOT NULL,
  `year` int DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  `product` varchar(20) DEFAULT NULL,
  `profit` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (1, 2000, 'Finland', 'Computer', 1500);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (2, 2000, 'Finland', 'Phone', 100);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (3, 2001, 'Finland', 'Phone', 10);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (4, 2001, 'India', 'Calculator', 75);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (5, 2000, 'India', 'Calculator', 75);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (6, 2000, 'India', 'Computer', 1200);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (7, 2000, 'USA', 'Calculator', 75);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (8, 2000, 'USA', 'Computer', 1500);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (9, 2001, 'USA', 'Calculator', 50);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (12, 2002, 'USA', 'Computer', 1200);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (13, 2001, 'USA', 'TV', 150);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (14, 2002, 'USA', 'TV', 100);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (15, 2001, 'USA', 'Computer', 1500);

这是一个销售信息表，包含年份、国家、产品和利润四个字段。让我们基于窗口函数来进行一些统计分析，例如：

问题一

计算每个国家每年的总利润，并按照国家和年份排序

SELECT year, country, 
SUM(profit) OVER (PARTITION BY country, year) AS total_profit
FROM sales
ORDER BY country, year;

输出结果：

+------+---------+--------------+
| year | country | total_profit |
+------+---------+--------------+
| 2000 | Finland | 1600         |
| 2000 | Finland | 1600         |
| 2001 | Finland | 10           |
| 2000 | India   | 1275         |
| 2000 | India   | 1275         |
| 2001 | India   | 75           |
| 2000 | USA     | 1575         |
| 2000 | USA     | 1575         |
| 2001 | USA     | 1700         |
| 2001 | USA     | 1700         |
| 2001 | USA     | 1700         |
| 2002 | USA     | 1300         |
| 2002 | USA     | 1300         |
+------+---------+--------------+

可以看到，每个国家每年的总利润都被计算出来了，但是没有折叠为单个输出行，而是为每个查询行生成了一个结果。

❝

在这里就体现出博主说的不修改原有结果的基础上，添加聚合字段的威力。

❞

问题二

计算每个国家每种产品的销售排名，并按照国家和排名排序

SELECT country, product, profit, 
RANK() OVER (PARTITION BY country ORDER BY profit DESC) AS rank1
FROM sales
ORDER BY country, rank1;

输出结果：

+---------+------------+--------+-------+
| country | product    | profit | rank1 |
+---------+------------+--------+-------+
| Finland | Computer   |   1500 |     1 |
| Finland | Phone      |    100 |     2 |
| Finland | Phone      |     10 |     3 |
| India   | Computer   |   1200 |     1 |
| India   | Calculator |     75 |     2 |
| India   | Calculator |     75 |     2 |
| USA     | Computer   |   1500 |     1 |
| USA     | Computer   |   1500 |     1 |
| USA     | Computer   |   1200 |     3 |
| USA     | TV         |    150 |     4 |
| USA     | TV         |    100 |     5 |
| USA     | Calculator |     75 |     6 |
| USA     | Calculator |     50 |     7 |
+---------+------------+--------+-------+

可以看到，每个国家每种产品的销售排名都被计算出来了，使用了RANK()函数，它会给相同利润的产品分配相同的排名，并跳过之后的排名。细心的朋友可能会发现相同国家产品的销售排名重复之后，下一名会跳名次，如果不想这样可以使用 DENSE_RANK() 函数，

mysql> SELECT country, product, profit, 
DENSE_RANK() OVER (PARTITION BY country ORDER BY profit DESC) AS rank1
FROM sales
ORDER BY country, rank1;
+---------+------------+--------+-------+
| country | product    | profit | rank1 |
+---------+------------+--------+-------+
| Finland | Computer   |   1500 |     1 |
| Finland | Phone      |    100 |     2 |
| Finland | Phone      |     10 |     3 |
| India   | Computer   |   1200 |     1 |
| India   | Calculator |     75 |     2 |
| India   | Calculator |     75 |     2 |
| USA     | Computer   |   1500 |     1 |
| USA     | Computer   |   1500 |     1 |
| USA     | Computer   |   1200 |     2 |
| USA     | TV         |    150 |     3 |
| USA     | TV         |    100 |     4 |
| USA     | Calculator |     75 |     5 |
| USA     | Calculator |     50 |     6 |
+---------+------------+--------+-------+

问题三

计算每个国家每种产品的累计利润，并按照国家和利润排序

SELECT country, product, profit, 
SUM(profit) OVER (PARTITION BY country ORDER BY profit 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_profit
FROM sales
ORDER BY country, profit;

输出结果：

+---------+------------+--------+-------------------+
| country | product    | profit | cumulative_profit |
+---------+------------+--------+-------------------+
| Finland | Phone      |     10 | 10                |
| Finland | Phone      |    100 | 110               |
| Finland | Computer   |   1500 | 1610              |
| India   | Calculator |     75 | 75                |
| India   | Calculator |     75 | 150               |
| India   | Computer   |   1200 | 1350              |
| USA     | Calculator |     50 | 50                |
| USA     | Calculator |     75 | 125               |
| USA     | TV         |    100 | 225               |
| USA     | TV         |    150 | 375               |
| USA     | Computer   |   1200 | 1575              |
| USA     | Computer   |   1500 | 3075              |
| USA     | Computer   |   1500 | 4575              |
+---------+------------+--------+-------------------+

可以看到，每个国家每种产品的累计利润都被计算出来了，使用了SUM()函数，并指定了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW作为窗口范围，表示从分区开始到当前行。

问题四

基于Named Window 重写问题三，sql 如下

SELECT country, product, profit, 
SUM(profit) OVER w1 AS cumulative_profit
FROM sales
WINDOW
  w1 as (PARTITION BY country ORDER BY profit 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY country, profit
;

输出结果：

+---------+------------+--------+-------------------+
| country | product    | profit | cumulative_profit |
+---------+------------+--------+-------------------+
| Finland | Phone      |     10 | 10                |
| Finland | Phone      |    100 | 110               |
| Finland | Computer   |   1500 | 1610              |
| India   | Calculator |     75 | 75                |
| India   | Calculator |     75 | 150               |
| India   | Computer   |   1200 | 1350              |
| USA     | Calculator |     50 | 50                |
| USA     | Calculator |     75 | 125               |
| USA     | TV         |    100 | 225               |
| USA     | TV         |    150 | 375               |
| USA     | Computer   |   1200 | 1575              |
| USA     | Computer   |   1500 | 3075              |
| USA     | Computer   |   1500 | 4575              |
+---------+------------+--------+-------------------+

四. 窗口函数优缺点

优点：

  • 窗口函数可以在不改变原表行数的情况下，对每个分区内的查询行进行聚合、排序、排名等操作，提高了数据分析的灵活性和效率。

  • 窗口函数可以使用滑动窗口来处理动态的数据范围，例如计算移动平均值、累计和等。

  • 窗口函数可以与普通聚合函数、子查询等结合使用，实现更复杂的查询逻辑。

缺点：

  • 窗口函数的语法较为复杂，需要注意OVER子句中的各个参数的含义和作用。

  • 窗口函数的执行效率可能不如普通聚合函数，因为它需要对每个分区内的每个查询行进行计算，而不是折叠为单个输出行。

  • 窗口函数只能在SELECT列表和ORDER BY子句中使用，不能用于WHERE、GROUP BY、HAVING等子句中。

五、总结

窗口函数的应用场景很广，可以完成许多数据分析与挖掘任务。MySQL8 支持窗口函数是一个非常棒的特性，大大提高了 MySQL 在数据分析领域的竞争力。希望通过这篇文章可以帮助大家对 MySQL8 的窗口函数有一个初步的认识。

·END·

因公众号更改推送规则，关注公众号主页点击右上角"设为星标"第一时间获取博主精彩技术干货

竹林幽深
关注
MySQL8数据库窗口函数
qq_30505673的博客
09-16 2073
对于窗口函数，比如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窗口函数
while(True): print('adorable')
02-12 1246
window
MySQL8新特性 窗口函数介绍、使用、实战
codechatter的博客
08-23 755
MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。窗口函数可以分为静态窗口函数和动态窗口函数。静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；动态窗口函数的窗口大小会随着记录的不同而变化。提示：使用 select version()；查看 MySQL 版本是否支持语法。MySQL官方网站窗口函数的网址为。
MYSQL8窗口函数
识途老码
04-12 775
MYSQL8窗口函数
MySQL8之窗口函数
zjjcchina的博客
04-02 676
作为一名数据分析师，日常工作中经常会遇到类似这样的需求： 怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？累计求和如何计算？ 对于这样的需求，使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点，需要在单表中满足某些条件的结果集内部做一些函数操作，不是简单的表连接，也不是简单的聚合可以实现的，通常费了大半天时间写出来一堆长长的晦涩难懂的SQL，且性能低下，难以维护。要解决此类问题，最方便的就是使用窗口函数。 1.窗口函数初识 MySQL从8.0开始支持窗口函数，这
MySQL8窗口函数
qq_44134480的博客
06-27 365
在日常开发工作中，经常会遇到下面这些需求去医院看病，怎样知道上次就医距现在的时间？环比如何计算？怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？如果用传统SQL来解决这些问题，理论上是可以的，但逻辑会相当复杂。这类需求都有一个共同的特点，为了的到结果，都需要在某个结果集内做一些特定的函数操作。为了很方便的解决这一类问题，MySQL8.0中引入了窗口函数。窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。
MySQL8.0窗口函数入门实践及总结
09-08
MySQL8.0窗口函数是数据库查询中的一个重要特性，它使得在处理复杂的数据分析和统计时变得更加便捷。在MySQL8.0之前，如果要进行类似的数据排名或统计，通常需要借助子查询或自连接等复杂操作，而窗口函数的引入极大...
mysql8.0新特性 之 窗口函数 使用示例及详解
DuTianTian_csdn的博客
04-07 1559
什么是窗口函数 MySQL从8.0开始支持窗口函数，这个功能在大多数据库中早已支持，有的也叫分析函数。 窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。简单的说窗口函数就是对于查询的...
MySQL 8.0的关系数据库新特性详解
09-09
5. **窗口函数**：MySQL 8.0引入了窗口函数，弥补了之前缺乏类似rank()函数的不足。窗口函数允许在不使用GROUP BY的情况下对数据进行聚合操作，如计算排名、移动平均等。在窗口函数中，每个行都有一个与之相关的...
MariaDB与MySQL：公共表表达式与窗口函数详解
"《MariaDB与MySQL：公共表表达式与窗口函数详解》是一本由Daniel Bartholomew编写的深入指南，它着重介绍了在MariaDB 10.2和MySQL 8.0版本中引入的两种强大的查询工具——公共表表达式(CTEs)和窗口函数。...
MySQL详解
12-14
2. PostgreSQL：是一个功能强大的对象关系型数据库，支持更多高级特性，如窗口函数、物化视图等，许多企业如苹果已转向使用。 总结来说，MySQL因其开源、高效、灵活的特性和丰富的生态系统，成为了众多Web开发者的...
掌握MySQL8.0窗口函数：新特性和常用查询技巧
MySQL查询语句及MySQL 8.0新特性窗口函数详解 MySQL是一种广泛使用的开源关系型数据库管理系统，其强大的查询能力使得数据处理和管理变得高效。本文将深入探讨MySQL的基础查询语句以及MySQL 8.0版本引入的重要新...
mysql8 窗口函数
太白_猿的博客
12-11 173
mysql开窗函数
MySQL操作实战（二）：窗口函数
热门推荐
陆
02-21 5万+
窗口：记录集合 窗口函数：在满足某些条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同，窗口大小都是固定的，称为静态窗口；有的函数则相反，不同的记录对应着不同的窗口，称为滑动窗口。 1. 窗口函数和普通聚合函数的区别： ①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。 ②聚合函数也可以用于窗口函数。 2. 窗口函数的基...
mysql8窗口函数(一)
weixin_30613727的博客
09-04 204
NO.1 row_number() #返回分区内的当前行数。行数范围从1到分区行数。 row_number() OVER (PARTITION BY COL1 ORDER BY COL2)表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的) 以下是测试表格，订单信息表，包含订单号，用户id,金额，创建时间字段。 ...
mysql 窗口函数（一）
lv_yishi的博客
11-19 1206
基于mysql8.0的窗口函数使用
MySQL 8.0新特性深度解析：窗口函数与JSON数据处理的10大技巧
最新发布
java专栏
09-20 721
嗨，亲爱的小伙伴们！👋 欢迎来到 MySQL 8.0 的奇妙世界，这里充满了新鲜和惊喜。MySQL 8.0 是一个数据库界的超级英雄，它带着一系列令人兴奋的新特性，比如窗口函数和 JSON 数据处理，来拯救我们的数据处理任务。🚀窗口函数是 MySQL 8.0 的一大亮点，它们可以让我们对数据进行复杂的分析，而不需要复杂的子查询或多步处理。🌟窗口函数是一种特殊的函数，它允许你对一组行执行计算，而不仅仅是单一的行。这组行被称为“窗口”，窗口的大小和顺序由OVER()子句定义。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值