MySQL8新特性窗口函数详解

本文博主给大家详细讲解一波 MySQL8 的新特性：「窗口函数」，相信大伙看完一定能有所收获🤓。,MySQL8 窗口函数是一种特殊的函数，它可以在一组查询行上执行类似于聚合的操作，但是不会将查询行折叠为单个输出行，而是为每个查询行生成一个结果。窗口函数可以用来处理复杂的报表统计分析场景，例如计算移动平均值、累计和、排名等。其中博主认为它展现的主要威力在于「它能够让我们在不修改原有语句输出结果的基础上，直接添加新的聚合字段」。,窗口函数语法如下：,window_function_name 函数可以是聚合函数或者非聚合函数。MySQL8 支持以下几类窗口函数，,图片,MySQL官网提供,OVER 关键字很重要，用来标识是否使用窗口函数，语法如下,两种形式都定义了窗口函数应该如何处理查询行。它们的区别在于窗口是直接在 OVER() 中定义，还是基于 window_name 在 OVER 字句可以重复使用。,PARTITION BY子句用来将查询结果划分为不同的分区，窗口函数在每个分区上分别执行，语法如下,ORDER BY 子句用来对每个分区内的查询结果进行排序，窗口函数将按照排序后的顺序进行计算，语法如下,frame_clause 是窗口函数的一个可选子句，用来指定每个分区内的数据范围，可以是静态的或动态的。语法如下,其中，frame_units表示窗口范围的单位，可以是ROWS或RANGE。ROWS表示基于行数，RANGE表示基于值的大小。frame_extent表示窗口范围的起始位置和结束位置，可以是以下几种形式：,例如，如果指定了ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING，则表示窗口范围包括当前行、前两行和后一行。如果指定了RANGE BETWEEN 10 PRECEDING AND CURRENT ROW，则表示窗口范围包括当前行和值在当前行减去10以内的所有行。如果没有指定frame_clause，则默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从分区开始到当前行。,图片,引用自网上,MySQL8的 Named Windows 是指在 WINDOW 子句中定义并命名的窗口，可以在 OVER 子句中通过窗口名来引用。使用 Named Windows 的好处是可以避免在多个OVER子句中重复定义相同的窗口，而只需要在 WINDOW 子句中定义一次，然后在 OVER 子句中引用即可。例如，下面的查询使用了三个相同的窗口：,可以使用Named Windows来简化为：,这样就只需要在 WINDOW 子句中定义一个名为w的窗口，然后在三个OVER子句中引用它。,如果一个 OVER 子句使用了 OVER (window_name …) 而不是 OVER window_name，则可以在引用的窗口名后面添加其他子句来修改窗口。例如，下面的查询定义了一个包含分区的窗口，并在两个 OVER 子句中使用不同的排序来修改窗口：,这样就可以根据不同的排序来获取每个国家的第一年和最后一年。,一个命名窗口的定义本身也可以以一个窗口名开头。这样可以实现窗口之间的引用，但不能形成循环。例如，下面的查询定义了三个命名窗口，其中第二个和第三个都引用了第一个：,这样就可以根据不同的范围来计算每个值的累计和。,下面以一个简单的示例表来说明 MySQL8 窗口函数的用法，提前准备 sql 脚本如下,这是一个销售信息表，包含年份、国家、产品和利润四个字段。让我们基于窗口函数来进行一些统计分析，例如：,计算每个国家每年的总利润，并按照国家和年份排序,输出结果：,可以看到，每个国家每年的总利润都被计算出来了，但是没有折叠为单个输出行，而是为每个查询行生成了一个结果。,在这里就体现出博主说的不修改原有结果的基础上，添加聚合字段的威力。,计算每个国家每种产品的销售排名，并按照国家和排名排序,输出结果：,可以看到，每个国家每种产品的销售排名都被计算出来了，使用了RANK()函数，它会给相同利润的产品分配相同的排名，并跳过之后的排名。细心的朋友可能会发现相同国家产品的销售排名重复之后，下一名会跳名次，如果不想这样可以使用 DENSE_RANK() 函数，,计算每个国家每种产品的累计利润，并按照国家和利润排序,输出结果：,可以看到，每个国家每种产品的累计利润都被计算出来了，使用了SUM()函数，并指定了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW作为窗口范围，表示从分区开始到当前行。,基于Named Window 重写问题三，sql 如下,输出结果：,优点：,缺点：,窗口函数的应用场景很广，可以完成许多数据分析与挖掘任务。MySQL8 支持窗口函数是一个非常棒的特性，大大提高了 MySQL 在数据分析领域的竞争力。希望通过这篇文章可以帮助大家对 MySQL8 的窗口函数有一个初步的认识。,