iis服务器助手广告广告
返回顶部
首页 > 资讯 > 数据库 >MySQL8.0中的窗口函数是什么
  • 331
分享到

MySQL8.0中的窗口函数是什么

2023-06-29 09:06:30 331人浏览 八月长安
摘要

这篇文章给大家分享的是有关MySQL8.0中的窗口函数是什么的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。在以前的Mysql版本中是没有窗口函数的,直到mysql8.0才引入了窗口函数。窗口函数是对查询中的每一条

这篇文章给大家分享的是有关MySQL8.0中的窗口函数是什么的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。

在以前的Mysql版本中是没有窗口函数的,直到mysql8.0才引入了窗口函数。窗口函数是对查询中的每一条记录执行一个计算,并且这个计算结果是用与该条记录相关的多条记录得到的。

1.窗口函数与聚合函数

窗口函数与聚合函数很像,他们都是在一组记录而不是整张表上执行的。但是,一个聚合函数在一组记录执行后只返回一条结果而窗口函却会对改分组内的每行记录都返回一个结果。

2.常见的窗口函数

Mysql8.0中定义的窗口函数主要有以下几种:

函数名参数描述
cume_dist()累计分布值。即分组值小于等于当前值的行数与分组总行数的比值。取值范围为(0,1]。
dense_rank()不间断的组内排序。使用这个函数时,可以出现1,1,2,2这种形式的分组。
first_value()是;first_value(expr)返回分组内截止当前行的第一个值。
lag()是;lag(expr,[N,[default]])从当前行开始往前取第N行,如果N缺失默认为1。若没有没有,则默认返回default。default默认值为NULL
last_value()是;last_value(expr)返回分组内截止当前行的最后一个值。
lead()是;lead(expr,[N,[default]])从当前行开始往后取第N行。函数功能与lag()相反,其余与lag()相同。
nth_value()是;nth_value(expr,N)返回分组内截止当前行的第N行。first_value\last_value\nth_value函数功能相似,只是返回分组内截止当前行的不同行号的数据。
ntile()是;ntile(N)返回当前行在分组内的分桶号。在计算时要先将改分组内的所有数据划分成N个桶,之后返回每个记录所在的分桶号。返回范围从1到N
percent_rank()累计百分比。该函数的计算结果为:小于该条记录值的所有记录的行数/该分组的总行数-1. 所以改记录的返回值为[0,1]
rank()间断的组内排序。其排序结果可能出现如下结果:1,1,3,4,4,6
row_number()当前行在其分组内的序号。不管其排序结果中是否出现重复值,其排序结果都为:1,2,3,4,5

注:‘参数’列说明该函数是否可以加参数。“否”说明该函数的括号内不可以加参数。expr即可以代表字段,也可以代表在字段上的计算,比如sum(col)等。以下相同。

3. over子句

over子句可以指定如何将记录划分分区以供窗口函数处理。如果over()为空,则是将整个查询记录作为一个分组。如果over子句不为空,则其可以指定查询记录划分分组的方式以及记录在分组内部的排序方式。除此之外,over子句也可以和聚合函数一起用。如果聚合函数后出现over子句,那么这些聚合函数也就变成了窗口函数。如果没有over子句,则他们仍然是聚合函数。可以使用over子句的聚合函数主要有以下几种:

avg()、bit_and()、bit_or()、bit_xor()、count()、max()、min()、stddev_pop()、stddev()、std()、stddev_samp()、sum()、var_pop()、variance()、var_samp()

而对于前一部分中介绍的窗口函数来说,over()子句是强制必须要有的。

over子句中常见的语法形式为:

over_clause:

            {OVER (window_spec) | OVER window_name}

其中: 

window_spec:

           [window_name] [partition_clause] [order_clause] [frame_clause]

window_name: 是指在查询语句定义的window子句。如果遇到group by、having子句order by子句,那么window子句要放到having子句和order by子句中间。其语法如下:

WINDOW window_name AS (window_spec)

                [, window_name AS (window_spec)] ...

window_spec:

             [window_name] [partition_clause] [order_clause] [frame_clause]

从语法结构可以看出来window子句其实只是把放在over()括号中的内容单独抽出来。

partition_clause:即parittion by expr子句。用来指定记录分组方式。语法中的expr不仅可以是字段本身,也可以是计算表达式。比如,记录中有个timestramp类型的字段 ts,在MySQL中,partition by ts 和partition by hour(ts)都是有效的。

order_clause: 即 order by expr desc|asc,expr desc|asc。 用来指定分组内的排序方式。

frame_clause: 用来指定当前分组中的子集划分方式。frame可以在依据当前行的位置在每个分组内移动。使用frame来计算流水流水总和(从分区开始到当前行)及滚动平均(rolling averages)。

其语法结构如下:

frame_clause:      frame_units frame_extentframe_units:        {ROWS | RANGE}frame_extent:     {frame_start | frame_between}    frame_between:              BETWEEN frame_start AND frame_end    frame_start, frame_end:         { CURRENT ROW           | UNBOUNDED PRECEDING           | UNBOUNDED FOLLOWING           |expr PRECEDING           | expr FOLLOWING       }

其中:

frame_units用来指示当前行和frame的关系

ROWS: 用来定义frame的开始行和结束行(偏移量依据的是位置);RANGE: 定义frame的区间。(偏移量的基准为当前行的值)

frame_entent用来指示frame的开始行和结束行。一种是通过指定start和end(frame_start,frame_end。frame_end可以不指定,没有明确给出的话当前行默认为结束行),另一种使用between(frame_between)。frame_between的语法很简单。下面来看frame_start和frame_end。

current row:和rows一起用时,边界就是当前行。和range一起用时,边界是当前行的对等点(个人理解,这里所说的对等点应为与当前行的值相等的所有记录)。

unbounded precceding:使用它时,每个分区的第一行即为边界。

unbounded following:使用它时,每个分区的第一行即为边界。

expr preceding\expr following: 可以由expr个性化的设置向上(preceding)向下(following)的偏移量。

4.代码示例

表结构如下: 

MySQL8.0中的窗口函数是什么

4.1 row_number\dense_rank\ rank

select order_date,sum(quantity) as quantity,rank()over(ORDER BY sum(quantity) desc) as rank_result,dense_rank()over(ORDER BY sum(quantity) desc) as dense_result,row_number()over(ORDER BY sum(quantity) desc) as row_resultfrom spm_order group by order_date-- 限定一部分数据,没有实际意义,能展示出这三个函数的区别就可以了having quantity>=98order by quantity desc

运行结果如下:

MySQL8.0中的窗口函数是什么

 从上面结果看出:

  • rank()函数一旦遇到重复值,序号会断。比如2个7之后下个出现的序号是9。

  • dense_rank()函数中即使有重复值,但是序号是连续的。2个7之后下个出现的序号是8。

  • row_number()不会出现相同的序号。

4.2 cume_dist\percent_rank

select order_date,num,cume_dist()over(order by num asc) as cume_result,percent_rank()over(order by num asc) as percent_resultfrom (select order_date,count(1) as numfrom spm_order group by order_datehaving num>=27)aorder by num asc

 代码运行结果如下

MySQL8.0中的窗口函数是什么

 分析如下:

  • cume_dist():首先总的记录有10条。当num=27时,num小于等于27的值共有5个,所以其cume_dist()值为0.5;当num=28时,小于等于28的值共有7个,所以cume_dist()值为0.7; 以此类推。

  • percent_rank().当num=27时,num小于27的记录数为0,所以percent_rank()为0;当num=28时,num<28的记录数共有5个,所以percent_rank()的值为5/9; 而当num=29时,其cume_dist()=7/9;以此类推,直到最大值36对应的值为1.

  • 这两个函数的作用有点像计算中位数。

4.3 first_value\last_value\nth_value

select sales_name,year_date,num,first_value(num)over(PARTITION by sales_name order by year_date asc) as first_result,last_value(num)over(PARTITION by sales_name order by year_date asc) as last_result,nth_value(num,2)over(PARTITION by sales_name order by year_date asc) as nth_resultfrom (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_order where sales_name in ('杨健','楚杰','洪光')group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a

代码运行结果如下(要注意,这三个函数计算结果都是截止当前行

MySQL8.0中的窗口函数是什么

4.4 ntile()

select sales_name,year_date,num,ntile(8)over(order by num asc) as n_binfrom (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_order where sales_name in ('杨健','楚杰','洪光')group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a

代码运行结果如下:

MySQL8.0中的窗口函数是什么

 从结果上进行分析:

  • 首先,分桶号从1到N,都会出现;

  • 其次,关于每个桶应该有多少条记录。可以假设有N个桶,m个球(球数为总记录数),标号从1到N,依次往1号桶到N号桶里投球,每次只投1个球。循环往复,直到m个球全都投入到N个桶中。最后每个桶里有多少球,现在每个桶里就有多少条记录。

4.5 lag\lead

select sales_name,year_date,num,lag(num,2)over(PARTITION by sales_name order by year_date asc) as lag_result,lead(num,2)over(PARTITION BY sales_name order by year_date asc) as lead_resultfrom (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_order where sales_name in ('杨健','楚杰','洪光')group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a

代码运行结果如下:

MySQL8.0中的窗口函数是什么

注意,lag()和lead()函数中出现的字段可以与over()子句中order by中出现的字段不一致。在代码lag(num,2)中2代表的想要取数的那一行相比当前行的偏移量(lead中也类似)。

4.6 聚合函数

select sales_name,year_date,num,sum(num)over(PARTITION by sales_name) as sum_order,avg(num)over(PARTITION by sales_name) as mean_orderfrom (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_order where sales_name in ('杨健','楚杰','洪光')group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a

代码运行结果如下:

MySQL8.0中的窗口函数是什么

4.7 order by子句

select sales_name,year_date,num,sum(num)over(partition by sales_name) as count_1,count(num)over(partition by sales_name order by num) as count_2from (select sales_name,year(order_date)as year_date,count(1) as numfrom spm_order where sales_name in ('杨健','楚杰','洪光')group by year(order_date),sales_nameorder by sales_name asc,year_date asc)a

代码运行结果如下:

MySQL8.0中的窗口函数是什么

当frame_clause不存在的时候,默认的frame与order by子句是否存在有关:

  • 如果有order by子句,则默认的frame是从当前分区第一行到当前行。即在此种情况下,默认的frame为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  • 如果没有order by子句,则默认的frame是指该分区。如果此时也没有partition by子句,则相当于全部数据。

4.8 window子句

select sales_name,year(order_date) as year_1,count(1) as num,sum(count(1)) over w as sales_order,sum(count(1)) over (w_1) as year_order,rank()over(w order by count(1) desc) as rank_order-- 三种写法都是符合语法规范的from spm_order where sales_name in ('杨健','楚杰','洪光')group by sales_name,year(order_date)window w as (PARTITION by sales_name),       w_1 as (PARTITION by year(order_date))order by sales_order

代码运行结果如下:

MySQL8.0中的窗口函数是什么

4.9 rows和range

rows和range是不能单独使用的,但是因为实在不理解这两个用法上的区别,所以就进行了单独的验证。

select sales_name,month_1,rn_1,num,sum(num)over(order by month_1 rows between 2 preceding and 1 preceding) as month_row,sum(num)over(order by month_1 range between 2 preceding and 1 preceding) as month_range,sum(num)over(order by rn_1 range between 2 preceding and 1 preceding) as rn_rangefrom (SELECT sales_name,month(order_date) as month_1,count(1) as num,-- 由于rank()over()返回的是unsigned,当相减结果为负时(between子句会用到减法)会报错,所以这里转成signed类型cast(rank()over(order by month(order_date)) as signed) as rn_1from spm_orderwhere sales_name in ('洪光','范彩')group by sales_name,month(order_date))aorder by month_1 asc

代码运行结果如下:

MySQL8.0中的窗口函数是什么

对以上代码分析:

首先,在这里我新建了一个rn_1列。rn_1列和month_1的区别在于,month_1的数据是连续的,而rn_1列是有中断的(两个1之后出现的是3,我是故意要创建一个中断的序列,来分析一下range的作用范围) 

先来看month_row的区别,month_row列的计算结果为当前行在分区中按month_1升序排序之后排在其前面的两行(between and限定的)的sum求和值。所以rows后面的between and限定的偏移量是基于他们在分区中的排列位置的。

再来看month_range,通过分析其实验结果可以发现,month_range列的计算为分区内month_1=当前行-1和month_1=当前行-2(-1,-2是由between an子句决定的。preceding代表负,following代表正)所有列的sum求和值。再来看rn_range, rn_range列的计算结果为分区内month_1=当前行-2的所有里列的sum求和值。所以,rang后面的between and限定的偏移量依据的是当前行的数值。

感谢各位的阅读!关于“MySQL8.0中的窗口函数是什么”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!

您可能感兴趣的文档:

--结束END--

本文标题: MySQL8.0中的窗口函数是什么

本文链接: https://www.lsjlt.com/news/324098.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑,方便收藏和打印~

下载Word文档
猜你喜欢
  • MySQL8.0中的窗口函数是什么
    这篇文章给大家分享的是有关MySQL8.0中的窗口函数是什么的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。在以前的MySQL版本中是没有窗口函数的,直到MySQL8.0才引入了窗口函数。窗口函数是对查询中的每一条...
    99+
    2023-06-29
  • MySQL8.0中的窗口函数的示例代码
    目录1.窗口函数与聚合函数2.常见的窗口函数3. over子句4.代码示例4.1 row_number\dense_rank\ rank4.2 cume_dist\perc...
    99+
    2024-04-02
  • Spark SQL中的窗口函数是什么
    在Spark SQL中,窗口函数是一种特殊的函数,可以用来在特定的窗口或分区中计算结果。窗口函数通常用于处理类似排名、聚合、排序等需...
    99+
    2024-04-09
    Spark
  • 什么是SQL窗口函数
    本篇内容主要讲解“什么是SQL窗口函数”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“什么是SQL窗口函数”吧!窗口函数(Window Function) 是 SQL2003 标准中定义的一项新特...
    99+
    2023-06-15
  • SQL窗口函数是什么
    这篇文章主要介绍了SQL窗口函数是什么,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。窗口函数形如:表达式 OVER (PARTITION BY 分组字段 ORDER BY 排...
    99+
    2023-06-29
  • Mysql8.0使用窗口函数解决排序问题
    MySQL窗口函数简介 MySQL从8.0开始支持窗口函数,这个功能在大多商业数据库和部分开源数据库中早已支持,有的也叫分析函数。 什么叫窗口? 窗口的概念非常重要,它可以理解为记录集合,窗口函数也...
    99+
    2024-04-02
  • oracle窗口函数的使用方法是什么
    在Oracle数据库中,窗口函数是一种用于执行聚合、排序、排名等操作的特殊函数。窗口函数不会合并行,而是在查询结果的每一行上执行计算...
    99+
    2024-04-09
    oracle
  • oracle窗口函数的执行流程是什么
    Oracle窗口函数的执行流程大致如下: 首先,确定窗口函数所应用的数据集,即确定数据集的排序顺序和分区方式。 接着,对数据...
    99+
    2024-04-09
    oracle
  • SQL中的开窗函数是什么
    本篇内容主要讲解“SQL中的开窗函数是什么”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“SQL中的开窗函数是什么”吧!OVER的定义OVER用于为行定义一个窗口...
    99+
    2024-04-02
  • ApacheBeam中的窗口是什么
    Apache Beam中的窗口是一种用于控制数据处理时间范围的抽象概念。窗口将数据流分割成有限且有序的数据块,这些数据块可以在指定的...
    99+
    2024-03-06
    ApacheBeam
  • SQL窗口函数之取值窗口函数的使用
    目录案例分析1.环比分析2.同比分析3.复合增长率4.不同产品最高和最低销售额示例表和脚本关于窗口函数的基础,请看文章SQL窗口函数 取值窗口函数可以用于返回窗口内指定位置的数据行。...
    99+
    2024-04-02
  • SQL窗口函数之排名窗口函数的使用
    目录案例分析案例使用的示例表1.环比分析2.同比分析3.复合增长率4.不同产品最高和最低销售额示例表和脚本关于窗口函数的基础,请看文章SQL窗口函数 取值窗口函数可以用于返回窗口内指...
    99+
    2024-04-02
  • SQL窗口函数之排名窗口函数怎么使用
    这篇文章主要介绍“SQL窗口函数之排名窗口函数怎么使用”的相关知识,小编通过实际案例向大家展示操作过程,操作方法简单快捷,实用性强,希望这篇“SQL窗口函数之排名窗口函数怎么使用”文章能帮助大家解决问题。取...
    99+
    2024-04-02
  • SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)
    目录案例分析1.移动平均值2.累计求和(ROW)3.累计求和(RANGE)示例表和脚本关于窗口函数的基础,请看文章SQL窗口函数 许多常见的聚合函数也可以作为窗口函数使用,包括AVG...
    99+
    2024-04-02
  • wpf子窗口调用主窗口的方法是什么
    在WPF中,子窗口可以通过以下几种方式调用主窗口的方法:1. 通过子窗口的Owner属性获取到主窗口的实例,然后直接调用主窗口的方法...
    99+
    2023-08-16
    wpf
  • Hive中怎么执行复杂的窗口函数
    在Hive中执行复杂的窗口函数通常涉及使用窗口函数和子查询的组合。以下是一些步骤,可以帮助您执行复杂的窗口函数: 使用窗口函数:...
    99+
    2024-03-14
    Hive
  • java中什么是函数式接口
    这篇文章给大家介绍java中什么是函数式接口,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。java基本数据类型有哪些Java的基本数据类型分为:1、整数类型,用来表示整数的数据类型。2、浮点类型,用来表示小数的数据类型...
    99+
    2023-06-14
  • SQL窗口函数怎么使用
    本文小编为大家详细介绍“SQL窗口函数怎么使用”,内容详细,步骤清晰,细节处理妥当,希望这篇“SQL窗口函数怎么使用”文章能帮助大家解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知识吧。什么是窗口函数S...
    99+
    2024-04-02
  • PHP函数的Path接口是什么?
    PHP是一种广泛使用的编程语言,被广泛用于web开发。PHP的功能强大,可以在短时间内创建功能丰富的网站。其中一个强大的功能是Path接口。在本文中,我们将探讨PHP函数的Path接口是什么,以及如何使用它。 Path接口是什么? 首先,让...
    99+
    2023-07-08
    函数 path 接口
  • MySQL 8.0窗口函数怎么运行
    今天就跟大家聊聊有关MySQL 8.0窗口函数怎么运行,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。# ROW_NUMBER() ...
    99+
    2024-04-02
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作