MySQL8.0新特性学习笔记（二）：窗口函数

springdk2009

墨衍会员 · AI 创作全网分发码龄17年

关注

原创最新推荐文章于 2026-04-04 10:26:59 发布 668 阅读 AI 写同款· GEO 优化 ›

时间：2020-12-09 10:17:06

MySQL8.0引入的窗口函数，可以比较方便的实现一些分析和统计功能，这些功能不用窗口函数也能实现，不过实现的sql可能会比较复杂。

一，简介

什么是窗口函数

窗口函数引入的其实不只是几个函数，而是一套完整的语法，窗口函数是此语法中的一部分。

语法：

窗口函数 over 窗口表达式。

over是窗口函数语法的关键字。

从语法上来看，窗口函数的使用实际上分为两部分：窗口函数和窗口。

窗口函数在sql中往往使用在查询结果中，返回一列值，就像查询一个字段一样。

窗口

从数据结构上来看，要使用窗口函数，首先我们要有一个窗口，所谓窗口，个人理解是一个数据集，数据集的内容可以类比group by分组之后的一组数据，只不过在使用了窗口函数的sql中，每一行记录都有一个自己的窗口，每行记录所对应的窗口，内容类比group by分组后该行所在的那组。当然这里不是用group by关键字分组了，是在窗口表达式里用专门的语法分组。

窗口函数

定义好了窗口，我们就可以使用窗口函数来对窗口里的数据进行汇总或分析，得到我们想要的那个数据。

MySQL8.0新增了一大堆窗口函数：

序号函数：row_number() / rank() / dense_rank()
分布函数：percent_rank() / cume_dist()
前后函数：lead() / lag()
头尾函数：first_val() / last_val()
其他函数：nth_value() / nfile()

另外，MySQL原有的一些聚合函数也可以用在窗口函数的语法中，当做窗口函数使用，比如sum(),avg(),max(),min(),count()等，其实这也很好理解，这些聚合函数本来就是基于分组来操作的，和窗口函数基于窗口来操作其实差不多。

窗口函数和聚合函数相同点和不同点

窗口函数和聚合函数都是基于分组的，窗口函数在窗口表达式中分组，聚合函数在group by语法中分组。

聚合函数会导致返回的行数被汇总和减少，每组一行。窗口函数不影响返回值的行数，原来有几行，用了窗口函数也还是几行，多一个字段而已。

聚合函数可以作为窗口函数在窗口函数的语法中使用。

二，窗口函数的两种写法

1，over关键字后直接定义窗口：

selectrow_number() over (partition by user_no order by amount desc),order_idfrom order_tab;

2，over关键字后使用窗口别名：

select row_number() over w,order_idfrom order_tabWINDOW w AS (partition by user_no order by amount desc);

此时w就是窗口的别名。

三，窗口表达式语法

下面具体看一下窗口表达式的语法。

窗口表达式中可以写三个子句：

1，PARTITION BY子句

PARTITION BY子句是用来分组的，从字面上来看，应该叫分区。理解上可以类比group by。

某行记录所在的分区就是这行对应的窗口。一个窗口可以包含多行，就像group by分的组一样。

使用的语法是：

PARTITION BY <expression>[{,<expression>…}]

其中的expression表达式，可以是列名，也可以是某个计算结果。如果是列名，就代表简单的按照列的值分区。

可以支持按照多个表达式或列来分区。

根据每行记录的值，可以确定每行属于哪个分区，也就是哪个窗口。

2，ORDER BY子句

order by子句是排序用的，决定了属于同一个窗口的行记录的排序方式。语法很简单：

ORDER BY <expression> [ASC|DESC] [{,<expression>…}]

和MySQL中原有的order by关键字意思差不多。原有的order by关键字是所有查询结果一起排序，窗口函数的order by子句是对某个窗口中的行记录进行排序。

3，frame子句

frame子句定义了窗口中的一个子窗口，子集。这个稍微麻烦点，语法是：

frame_unit {<frame_start>|<frame_between>}

frame_unit有两种选择，ROWS和RANGE，ROWS代表按起止位置标识子集，RANGE代表按起止值标识子集。

frame_start标识子窗口的开始位置，结束位置默认是当前行。

frame_between是用BETWEEN AND关键字来标识子窗口的起止位置。

可以标识位置或值的语法有：

CURRENT ROW。当前行。
UNBOUNDED PRECEDING。分区中的第一行。
UNBOUNDED FOLLOWING。分区中的最后一行。
expr PRECEDING。当前行减去expr的值。
expr FOLLOWING。当前行加上expr的值。

举例：

rows BETWEEN 1 PRECEDING AND 1 FOLLOWING

上面的表达式代表窗口范围前一行到后一行，一共三行记录。

rows UNBOUNDED FOLLOWING

上面的表达式代表窗口范围是当前行到分区中的最后一行。

rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

上面的表达式代表窗口范围是当前分区中所有行，整个frame子句都不写的时候也是这个效果。

四，窗口函数介绍

1，序号函数：row_number()，rank()，dense_rank()

序号函数的作用是显示分区中每行的行号。

三者的区别在于，当排序字段相同时，三个函数的处理结果不同：

row_number()：排序字段相同时，行号随机排，下一行行号正常加1。显然此函数得到的行号是连续的。
rank()：排序字段相同时，行号相同，下一行行号按照排名确定。这个逻辑比较像是现实中的排名规则，当出现并列冠军时，第三个人排名第三，没有亚军。显然此函数得到的行号是不连续的。
dense_rank()：排序字段相同时，行号相同，下一行行号顺序加1。也就是出现冠军时，第三个人排名是第二。显然此函数得到的行号是连续的。