mysql8.0的窗口函数的使用详解

原创 已于 2025-06-23 15:24:54 修改 · 1.6k 阅读 · 26
标签
#mysql
于 2025-06-23 14:35:04 首次发布

文章目录

1. mysql8.0 的重要更新

建议使用8.0.17及之后的版本，更新的内容比较多，挑几个重要的使用上的更新，其他请参考官网

  • 新增函数索引：之前我们知道，如果在查询中加入了函数计算，索引不生效，就是所谓的索引列上无计算，但在MySQL 8.0.13开始支持在索引中使用函数(表达式)的值。
    • 创建函数索引： create index nameIdx on t3( (UPPER(name)) ); 创建一个name列的大写的函数索引
    • 原理：函数索引基于虚拟列功能实现，在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引
  • 新增降序索引：MySQL在语法上很早就已经支持降序索引，但实际上创建的仍然是升序索引。mysql8.0开始真正支持降序索引
    • 创建降序索引： create index nameAgeIdx on t3( name ,age desc); 创建一个name列升序，age降序的索引
    • 对比：mysql 5.7中通过explain查看也会使用索引，但是Extra字段里有filesort文件排序，但mysql8.0中的Extra字段里没有filesort文件排序，充分利用了降序索引
  • innodb存储引擎 select for update 跳过锁等待：对于select … for share(8.0新增加查询共享锁的语法)或 select … for update， 在语句后面添加NOWAIT、SKIP LOCKED语法可以跳过锁等待，或者跳过锁定
    • 在5.7及之前的版本： select…for update，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时
    • 在8.0版本： select…for update NOWAIT 通过添加NOWAIT、SKIP LOCKED语法，能够立即返回。如果查询的行已经加锁，那么NOWAIT会立即报错返回，而SKIP LOCKED也会立即返回，只是返回的结果中不包含被锁定的行。
    • 应用场景比如查询余票记录，如果某些记录已经被锁定，用SKIP LOCKED可以跳过被锁定的记录，只返回没有锁定的记录，提高系统性能
  • group by 不再隐式排序 : mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。
  • .frm等元数据存储变动 : MySQL 8.0删除了之前版本的元数据文件，例如表结构.frm等文件，全部集中放入mysql.ibd文件里。
  • 窗口函数：从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数即便分组也不会将多行查询结果合并为一行，而是将结果放回多行当中。下文会对窗口函数做详细的使用介绍

参考文档
mysql8.0 添加弃用和删除的特性
mysql8.0 添加弃用和删除的参数
mysql8.0 InnoDB架构

 

2. 窗口函数的组成部分

窗口函数不会改变原有sql的查询结果，更像是在原有查询的基础上增加一个列，用于计算部分逻辑。作为执行计算的函数，窗口函数主要由三部分组成，如下：

2.1 窗口函数本身

  • ①：排名函数

    • ROW_NUMBER()：计算行号。为每一行分配一个唯一的整数，从1开始递增。
    • RANK()：计算排名，排名可能不连续。对每一行进行排名，当遇到相同值时，排名会跳过一些数字。
    • DENSE_RANK()：计算排名，排名是连续的。对每一行进行排名，但不会跳过排名数字，即使遇到相同值。
       

  • ①：聚合函数

    • MAX(列名)：计算窗口中的最大值。
    • MIN(列名)：计算窗口中的最小值。
    • SUM(列名)：对窗口中的数据求和。
    • AVG(列名)：对窗口中的数据求平均值。
    • COUNT(列名)：计算窗口中的记录数。
    • STDDEV(列名)：计算总体标准差。使用公式 S = sqrt(sum((x_i - x_bar)^2) / N)，其中 N 是样本中的数据点数量。
    • STDDEV_SAMP(列名)：计算样本标准差。使用公式 S = sqrt(sum((x_i - x_bar)^2) / (N - 1))，其中 N 是样本中的数据点数量。
       

  • ①：偏移函数

    • LAG(列名,行数)：取当前行往前（朝分区头部方向）第N行数据的值。
    • LEAD(列名,行数)：取当前行往后（朝分区尾部方向）第N行数据的值。
       

  • ①：首尾函数

    • FIRST_VALUE(列名)：取窗口内第一行的值。
    • LAST_VALUE(列名)：取窗口内最后一行的值。
    • NTH_VALUE(列名,N)：取窗口内第N行的值。
       

  • ①：分布函数

    • PERCENT_RANK()：计算行的百分比排名。输出百分比格式。
    • CUME_DIST()：计算行相对于所有其他行的累积分布。
    • NTILE(N)：将数据顺序切分成N等份，返回数据所在等份的编号（从1到N）。

 

2.2 over子句

over子句定义了窗口函数的计算范围。它由三部分组成

  • PARTITION BY：将结果集分成多个独立的分区，每个分区都是一个独立的数据集，窗口函数在每个分区上独立计算。如果省略，整个数据集被视为一个单一的分区。
  • ORDER BY：在每个分区内对行进行排序。
    • 对于排名函数，ORDER BY 子句决定了行的排名顺序。
    • 对于聚合函数，ORDER BY 子句决定了窗口的计算顺序。
  • 窗口框架：窗口框架用于定义在当前分区内，哪些行会被包含在窗口函数的计算中。它必须与ORDER BY子句一起使用（如果没有ORDER BY，则整个分区被视为一个框架），下面介绍一下窗口框架的特性

    • 语法：{ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end。其中 frame_start 和 frame_end 可以是以下之一

      • UNBOUNDED PRECEDING：分区的第一行
      • UNBOUNDED FOLLOWING：分区的最后一行
      • CURRENT ROW：当前行
      • n PRECEDING：当前行之前的n行（对于ROWS）或值（对于RANGE）
      • n FOLLOWING：当前行之后的n行（对于ROWS）或值（对于RANGE）
      • 注意：n PRECEDING 和 n FOLLOWING中的n必须是非负整数常量

    • 默认窗口框架

      • 如果指定了ORDER BY而没有指定窗口框架，则默认框架为：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这意味着窗口函数会计算从分区开始到当前行（包括当前行）的所有行，但是注意，这里使用的是RANGE，所以它会包括所有与当前行在ORDER BY列上具有相同值的行（即并列的行）
      • 如果没有指定ORDER BY，也没有指定窗口框架，则默认框架为：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，这意味着整个分区都会被包含在窗口内。

    • ROWS 、RANGE 和 GROUPS 的区别
      假设有如下一张表table，则用该表来解释这三者的区别

      id value
      1 10
      2 20
      3 20
      4 30

      • ROWS：基于物理行（行号）移动或后退一行，用行数来指定偏移量，当order by字段出现相同的值时，不考虑并列，每行独立计算。使用 ROWS 的查询sql如下：

        SELECT 
    id, value,
    SUM(value) OVER (ORDER BY value ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows
FROM table;

        使用 ROWS ，每条数据与前后移动一行的值进行sum，结果如下：

        id value sum_rows ​​rows 计算过程​​
        1 10 30 10(当前) + 20(后1行) = 30
        2 20 50 10(前1行) + 20(当前) + 20(后1行) = 50
        3 20 70 20(前1行) + 20(当前) + 30(后1行) = 70
        4 30 50 20(前1行) + 30(当前) = 50

      • RANGE：基于逻辑值（ORDER BY列的值）来增加或减少对应的值，用值来指定偏移量（需要与ORDER BY列类型兼容），当order by字段出现重复的值时，并列的行也加入计算范围，使用 RANGE 的查询sql如下：

        SELECT 
    id, value,
    SUM(value) OVER (ORDER BY value RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING) AS sum_range
FROM table;

        使用 RANGE ，每条数据与 前后 ±10的数据范围所涵盖的记录进行sum，当出现并列的重复值时，列入计算范围，结果如下：

        id value sum_range ​​range 计算过程​​
        1 10 50 值范围 [0,20]所涵盖的行 → id1(10), id2(20), id3(20) → 10+20+20=50
        2 20 80 值范围 [10,30]：id1(10), id2(20), id3(20), id4(30) → 10+20+20+30=80
        3 20 80 值范围 [10,30]：id1(10), id2(20), id3(20), id4(30) → 10+20+20+30=80
        4 30 70 值范围 [20,40]：id2(20), id3(20), id4(30) → 20+20+30=70

      • GROUPS（MySQL 8.0.14+）：GROUPS介于ROWS和RANGE之间，它按组（一组在ORDER BY列上值相同的行）进行偏移，并列的行会被视为同一组，使用 GROUPS的查询sql如下：

        SELECT 
    value,
    SUM(value) OVER (ORDER BY value GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_groups
FROM table;

结果：
value | sum_groups
10    | 10+20 = 30   -- 当前组（10），前一组没有，后一组（20）所以包括10和20（整个组）
20
最低0.47元/天 解锁文章
MySQL8.0窗口函数入门实践及总结
09-08
主要给大家介绍了关于MySQL8.0窗口函数入门实践及总结的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者使用MySQL8.0具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧
MySQL窗口函数及其分析查询
weixin_41540084的博客
09-06 1618
MySQL从版本8.0开始，支持窗口函数。窗口函数允许您以新的，更简单的方式解决查询问题，并具有更好的性能。 假设我们有一个sales表，按员工和财政年度存储销售额，如下所示： CREATE TABLE sales( sales_employee VARCHAR(50) NOT NULL, fiscal_year INT NOT NULL, sale DECIMAL(14,...
MySQL8新特性：窗口函数（最新超详细）（内含使用场景及示例）
weixin_44512005的博客
06-12 4564
本文将介绍MySQL8的新特性——窗口函数，从概念、语法、示例各方面详细解析窗口函数，以及解析面试中窗口函数常考的题目和知识点
MySQL8.0 窗口函数完整版教程（从零入门+全场景实战）
最新发布
当你有能力站在巅峰时
05-23 341
窗口函数是 MySQL8.0 核心分析能力，通过 PARTITION BY 分组、ORDER BY 排序、ROWS 自定义窗口，实现排名、偏移、累计、滚动统计。保留明细行、SQL简洁高效，彻底替代5.7复杂子查询，是报表统计、数据分析、分组TOPN场景的最优解。
MySQL的窗口函数介绍
weixin_50055999的博客
07-03 2031
MySQL窗口函数是8.0版本的重要特性，允许在不减少行数的情况下进行数据分析。主要包括三类函数：排名函数（RANK、DENSE_RANK、ROW_NUMBER）、分析函数（LEAD/LAG、FIRST_VALUE/LAST_VALUE）和聚合函数。窗口定义包含PARTITION BY分组、ORDER BY排序和窗口框架设置。高级用法包括多窗口定义、百分比计算、分组TopN查询等。使用窗口函数时需注意大数据集的性能问题。窗口函数将复杂的数据分析计算高效地转移到数据库层完成。
【MySQL】MySQL 8.0 新特性之 - 窗口函数（Window Functions）
迪曼奥特迦-博客
02-04 2550
窗口可以理解为记录集合，窗口函数就是在满足某种条件的记录集合上执行的特殊函数。每条记录都要在此窗口内执行函数。静态窗口：每条记录都要在此窗口内执行函数，窗口大小都是固定的。不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。窗口函数也称为 OLAP（Online Anallytical Processing）函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能。窗口函数对一组查询行执行类似聚合的操作。然而，聚合操作将查询行分组为单个结果行，而。
mysql8.0 中的窗口函数
不忘初欣丶的博客
03-26 977
mysql中的窗口函数
mysql8.0新特性 之 窗口函数 使用示例及详解
DuTianTian_csdn的博客
04-07 1843
什么是窗口函数 MySQL从8.0开始支持窗口函数，这个功能在大多数据库中早已支持，有的也叫分析函数。 窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。简单的说窗口函数就是对于查询的...
MySQL 8.0 窗口函数的语法及使用详解（非常细）
Hello World
04-24 2450
MySQL 官方文档：https://dev.mysql.com/doc/refman/8.0/en/window-functions.html 概述 窗口函数常用的三大功能：分区、排序、计算。 窗口函数 返回结果 ROW_NUMBER() 当前行在其分区内的序号，例如：1,2,3,4… RANK() 当前行在其分区内的排名 (有间隔)，例如：1,2,2,4… DENSE_RANK() 当前行在其分区内的排名 (无间隔)，例如：1,2,2,3… PERCENT_RANK() 当前值在其分区内的百分
MySQL窗口函数
HandsomeBoy
08-30 708
MySQL窗口函数提供了强大的数据分析能力，允许你在不减少行数的情况下执行复杂计算。掌握窗口函数可以大大简化许多常见的分析查询，如排名、移动平均、累计求和等。通过合理使用PARTITION BY、ORDER BY和窗口框架，你可以创建高效且功能强大的数据分析查询。
MySQL 窗口函数
m0_74825108的博客
01-20 2352
MySQL窗口函数是一种强大的工具，用于在查询中执行复杂的统计分析，而不需要改变表的结构或数据。MySQL从8.0版本开始支持窗口函数，这些函数也被称为分析函数，因为它们能够处理相对复杂的报表统计分析场景。窗口的意思是将数据进行分组，每个分组即是一个窗口，这和使用聚合函数时的group by分组类似，但与聚合函数不同的地方是： 聚合函数（例如：sum/avg/min/max）会针对每个分组（窗口）聚合出一个结果（每一组返回一个结果）。
Mysql时间数据分段累加求和案例之子查询与SUM窗口函数
数据科学汇集
07-30 3724
Mysql时间段数分段据累加求和 简介 时间段数据的分段可以根据义务逻辑来划分，累加求和在mysql8.0及以上可以通过sum结合窗口函数来实现，低版本用子查询实现。 需求 对原始数据按照时间点3点前、6点前、12点前对数值的累计得到如下结果： 时间段 累计 3 6 6 17 12 39 原始数据附录如下： id inserttime num 1 2020-07-30 00:00:00 1 2 2020-07-30 01:00:00 2 3 2020-07-30 02:00:00 3
Mysql数据库基本操作（八-2）窗口函数
weixin_42445051的博客
01-21 620
首先介绍所有窗口函数的用法，然后分块来分别介绍各个窗口函数的作用。 用法： window_function ( expr ) OVER( PARTITION BY ORDER BY ...frame_clause) 1，序号函数 序号函数有三个，分别为ROW_NUMBER()，RANK()，DENSE_RANK()，接下来解释其用法与作用： 2，分布函数 3，前后函数 4，头尾函数 5，其他函数 ...
SQL利用窗口函数实现轻量级报表设计_实战技巧
2401_83716389的博客
04-17 213
窗口函数怎么写才不报错：PARTITION BY 和 ORDER BY 的位置陷阱很多人一写 ROW_NUMBER() 或 SUM() OVER() 就遇到 Window function is missing ORDER BY 或 GROUP BY clause is required，根本原因不是语法写错，而是没理解窗口函数的执行阶段——它在 GROUP BY 之后、HAVING 之前运行，但又不能依赖未出现在 SELECT 或 GROUP BY 中的列做排序。
MySQL 8.0窗口函数
wll的专栏
05-23 1073
MySQL 8.0窗口函数 一、窗口函数的使用场景 作为IT人士，日常工作中经常会遇到类似这样的需求： 医院看病，怎样知道上次就医距现在的时间？环比如何计算？怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？ 对于这样的需求，使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点，需要在单表中满足某些条件的记录集内部做一些函数操作...
SQL中如何使用窗口函数实现Top N推荐系统
2301_77355362的博客
04-09 182
窗口函数怎么写才能拿到每个用户的Top N推荐直接用 ROW_NUMBER() 配合 PARTITION BY user_id 是最稳的路，别碰 RANK() 或 DENSE_RANK() —— 推荐场景里重复分数会导致条数失控，比如两个并列第1，RANK() 就直接跳到第3，你设 WHERE rn 可能只返回3条。关键原因不是函数本身，而是没做前置裁剪——你真需要对每个用户在 100 万候选里排 Top 10？
SQL如何获取分组内的众数_使用窗口函数与频率统计
Shorasul的博客
04-04 236
常见错误是写成 GROUP BY group_col, value_col 后直接 ORDER BY COUNT(*) DESC LIMIT 1，这会报错或只返回全局第一条，根本没按组切分。你不能写 SELECT group_col, MODE() WITHIN GROUP (ORDER BY value_col) FROM t GROUP BY group_col —— 这语法合法，但语义是“整个表的众数”，不是“每组众数”。它本身不支持跨组，所以不能替代窗口函数。
别再写复杂子查询了！MySQL窗口函数RANK()解决TopN排名难题
life6的博客
02-14 165
本文深入解析MySQL 8.0窗口函数RANK()的应用，帮助开发者高效解决TopN排名难题。通过对比传统子查询方案，展示窗口函数在保留原始数据、灵活定义计算范围等方面的优势，并提供电商销售排名、教育成绩分析等实战案例，显著提升查询性能和代码可读性。
一文读懂MySQL 8.0新特性之二：窗口函数，让你的SQL分析更轻松！
一如老师
12-14 861
窗口函数默认会对整个分区进行计算，你可使用ROWS或RANGE：基于行偏移：基于值范围AVG(价格) OVER (ORDER BY 日期代表对当前行、前一行和后一行的价格取平均。MySQL 8.0窗口函数让你在SQL中实现高级数据分析与排名、累计计算，减少子查询和手工逻辑，从而大幅提升查询表达力和效率。
MySQL 8.0窗口函数详解
weixin_45668002的博客
05-20 1572
推荐场景不推荐场景需要保留原始数据行的分析需求简单求和/计数等基础聚合处理排名、移动平均等复杂逻辑单次查询需要多层嵌套子查询分析数据分布（如累计百分比）对超大数据集且无合适索引时。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值