mysql窗口函数用法（mysql8.0 窗口函数）

环境：MySQL 8.0.32

下文语法中[]包括的内容表示可选项

以下示例均用下表以及下表数据进行演示：

窗口函数会对数据进行分组，每个分组即为一个窗口。和分组聚合不同，窗口函数会对窗口中每一条记录进行应用，不会使记录变少（每一行返回一个结果），其重点是当前行与窗口以及窗口中其他行的联系。

注意： 窗口函数可使用别名

WINDOW_FUNCTION
MySQL8支持以下几类窗口函数：

序号函数：用于为窗口内的每一行生成一个序号，例如 ROW_NUMBER()，RANK()，DENSE_RANK() 等。
分布函数：用于计算窗口内的每一行在整个分区中的相对位置，例如 PERCENT_RANK()，CUME_DIST() 等。
前后函数：用于获取窗口内的当前行的前后某一行的值，例如 LAG()，LEAD() 等。
头尾函数：用于获取窗口内的第一行或最后一行的值，例如 FIRST_VALUE()，LAST_VALUE() 等。
聚合函数：用于计算窗口内的某个字段的聚合值，例如 SUM()，AVG()，MIN()，MAX() 等。

OVER
OVER关键字用于标识是否使用窗口函数，有两种用法：

OVER()，常规用法，窗口规范放在括号中。如窗口规范为空，表示没有窗口划分，默认所有数据为一组。
OVER window_name：由FROM后的WINDOW子句定义窗口规范，可重复使用。下文进阶语法-命名窗口中会进行详细解析。

PARTITION BY
用于将记录划分为不同的分区，窗口函数在每个分区上分别执行。可以使用多个字段进行分区。

ORDER BY
用于将每个分区中的记录进行排序，窗口函数将按照排序后的顺序进行计算。可使用多个字段进行排序，默认ASC（升序）。

FRAME_CLAUSE
框架子句，用于指定每个分区的数据范围。下文进进阶语法-框架中会进行详细解析

命名窗口WINDOW AS

说明：

WINDOW子句中的括号内的部分就是原OVER子句后的窗口定义。使用OVER关键字调用窗口时，可直接引用窗口名，或者可对窗口进行进一步的加工，使用括号括起来并在其中使用其他的窗口规范。

一个命名窗口的定义本身也可以以一个窗口名开头。这样可以实现窗口之间的引用，但不能形成循环。

示例：

框架FRAME_CLAUSE

解析
frame_units（框架单位）指定框架使用的单位，有两种：

ROWS：基于行数，通过起始行和结束行来划定框架的范围，边界是明确的一行。
RANGE：基于值的大小，通过具有相同值的行来划定框架的范围，边界是一个范围，具有相同值的行作为一个整体看待。

frame_extend（框架范围）指定窗口范围的起始位置和结束位置，可通过BETWEEN frame_start AND frame_end子句定义，frame_start和frame_end有以下几种形式：

CURRENT ROW：当前行
UNBOUNDED PRECEDING：分区中第一行
UNBOUNDED FOLLOWING：分区中最后一行
expr PRECEDING：当框架单位是ROWS时，边界是当前行的前expr行。当框架单位是RANGE时，边界是值和"当前行的值减去expr"相等的行，可能有多个，统一视为一个边界。
expr FOLLOWING：当框架单位是ROWS时，边界是当前行的后expr行。当框架单位是RANGE时，边界是值和"当前行的值加上expr"相等的行，可能有多个，统一视为一个边界。

以下为框架范围图示：

注意：

含有ORDER BY子句，框架从组内第一行到当前行

不含ORDER BY子句，框架从组内第1行到最后一行（组内所有行）

如果没有指定frame_clause，则默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从分区开始到与当前行值相同的行。

如果没有定义框架的终点，则默认终点为CURRENT ROW当前行

示例：

结果如下：
在这里插入图片描述
以上示例中：

s1框架单位为ROWS，框架范围为1 PRECEDING，起点边界为当前行的前一行，终点默认为当前行。
s2框架范围为RANGE，框架范围为1 PRECEDING，起点边界为“当前行的值减去1”，终点默认为当前行。但是值差距都比1大，起点都无法触及之前的行，所以每行的框架都只包含当前行。
s3框架范围为RANGE，框架范围为100 PRECEDING，起点边界为“当前行的值减去100”，终点默认为当前行，即取每个分区中值的范围在区间[当前行值-100，当前行值]内的所有行。
- sale_id为1的分组中，第2行的sale_amount为1000，因此框架包含值在[1000-100, 1000]范围内的所有行，即第1，2行，sum求和结果为2000。其他分组同理。
- sale_id为1的分组中，第3行的sale_amount为1200，因此框架包含值在[1200-100, 1200]范围内的所有行，只有第3行，则sum求和结果为其本身的值。

聚合函数在窗口中使用与分组中相同，不做详细解析。以下介绍的专用窗口函数必须搭配OVER关键字使用。

无参数，返回当前行在分组内的序号，排序带间隙（序号不连续），值相同，序号相同。
可使用ORDER BY子句指定按某列进行排序。如无ORDER BY子句，则所有行的序号都为1。

无参数，返回当前行在分组内的排序，排序不带间隙（序号连续），值相同，序号相同。

示例：

结果如下：
在这里插入图片描述

百分比排序，返回当前行在分组内的百分比位置，返回值范围为[0, 1]，第1行的百分比位置是0%。
计算公式：(rank-1)/(rows-1)，rank为RANK函数返回的序号，rows为组内总行数。
示例：

结果如下：
在这里插入图片描述
以上示例中：sale_id为1的分组中，第3行RANK排序为3，组内总行数为3，所以PRECENT_RANK为(3-1)/(3-1)=1。其他同理。

累积分布（cumulative distribution），返回分组中值的累积分布，数据分布从0到1。
计算公式：(值小于等于当前行的行数)/分组内总行数。
示例：

结果如下：
在这里插入图片描述
以上示例中：

sale_id为1的分组中，小于等于第1行值的行为第1、2行，所以2/3=0.666…。第2行同理。
sale_id为2的分组中，小于等于第4行值的行只有第4行，所以1/2=0.5。而小于等于第5行的值的行有第4、5行，所以2/2=1。其他分组同理。

LAG(expr [, offset [, default]])：返回当前框架内当前行之前的某一行的值。参数expr为要检索的列或表达式，参数offset为要返回的行数，如果不指定，默认为1，即上一行。参数default为当指定的行数超出结果集范围时，返回的默认值。
LEAD(expr [, offset [, default]])：返回当前框架内当前行之后的某一行的值。参数expr为要检索的列或表达式，参数offset为要返回的行数，如果不指定，默认为1，即下一行。参数default为当指定的行数超出结果集范围时，返回的默认值。
示例1：

结果如下：
在这里插入图片描述

示例2：

结果如下：
在这里插入图片描述

窗口函数可以在不改变原表行数的情况下，对每个分区内的查询行进行聚合、排序、排名等操作，提高了数据分析的灵活性和效率。
窗口函数可以使用滑动窗口来处理动态的数据范围。
窗口函数可以与普通聚合函数、子查询等结合使用，实现更复杂的查询逻辑。
窗口函数的语法较为复杂，需要注意OVER子句中的各个参数的含义和作用。
窗口函数的执行效率可能不如普通聚合函数，因为它需要对每个分区内的每个查询行进行计算，而不是折叠为单个输出行。但一般来说，窗口函数的性能优于使用子查询或连接的方法，因为窗口函数只需要扫描一次数据，而子查询或连接可能需要多次扫描或连接。
窗口函数只能在SELECT列表和ORDER BY子句中使用，不能用于WHERE、GROUP BY、HAVING等子句中。

选择合适的窗口函数，避免使用复杂或重复的窗口函数。
使用Named Windows来定义和引用窗口，避免在多个OVER子句中重复定义相同的窗口。
尽量减少分区和排序的代价，使用索引或物化视图来加速分区和排序。
尽量减少窗口的大小，使用合适的frame_clause来限制窗口内的数据范围。
尽量使用并行处理来加速窗口函数的计算，利用多核或分布式系统来提高效率。

1.序号函数:row_number(),rank(),dense_rank()的区别
ROW_NUMBER，顺序排序——1、2、3
RANK，并列排序，跳过重复序号——1、1、3
DENSE_RANK，并列排序，不跳过重复序号——1、1、2

2.求连续登录3天的用户
解题思路：
①同一用户不可能在同一时间登录，不可能出现相同的登录时间，所以可以使用窗口函数根据用户进行分组，再根据登录时间进行排序，窗口函数可使用任一排序函数。
②如果连续登录，那么登录时间减去序号所获得的日期相同。
③统计获得的相同日期的个数，等于3的即为所求

解题过程（使用以上数据集）：
根据sale_id分区，根据sale_date排序，DATA_SUB函数在sale_date日期上减去序号的天数获得data_flag。
感谢评论区指出源数据已去重的问题，如果对源数据进行去重可在窗口函数对应语句中使用嵌套查询：(SELECT DISTINCT , FROM ) tmp_sales

在这里插入图片描述
最终答案（使用以上数据集）：
根据sale_id分区排序后，分组统计登录时间减去序号获得的相同日期的个数，等于3即为连续登录3天的用户。

在这里插入图片描述

如有问题和建议，可私信或评论，非常感谢。

到此这篇mysql窗口函数用法（mysql8.0 窗口函数）的文章就介绍到这了,更多相关内容请继续浏览下面的相关推荐文章，希望大家都能在编程的领域有一番成就！

上一篇：汽车报文格式（常用报文格式）

下一篇： prjab公式（prd算法公式）

版权声明：
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！

转载请注明出处，原文链接：https://www.xkablog.com/haskellbc/80355.html

命名窗口WINDOW AS

框架FRAME_CLAUSE

相关文章：