运维开发网

MySQL8.0中窗口函数的示例代码

运维开发网 https://www.qedev.com 2022-08-08 20:56 出处:网络
本文主要介绍了MySQL8.0中的窗口函数的示例代码,文中通过示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

本文主要介绍了MySQL8.0中的窗口函数的示例代码,文中通过示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

MySQL之前的版本没有窗口功能,直到MySQL8.0才引入窗口功能。window函数对查询中的每条记录执行一次计算,计算结果是通过使用与该记录相关的多条记录获得的。


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默认值为NULLlast_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到Npercent_rank()否累计百分比。该函数的计算结果为:小于该条记录值的所有记录的行数/该分组的总行数-1. 所以改记录的返回值为[0,1]rank()否间断的组内排序。其排序结果可能出现如下结果:1,1,3,4,4,6row_number()否当前行在其分组内的序号。不管其排序结果中是否出现重复值,其排序结果都为:1,2,3,4,5

注:lsquo参数rsquo指示函数是否可以添加参数。ldquoRdquo说明这个函数的括号里不能加参数。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 _子句:

{ OVER(windows _ spec)| OVER window _ name }

其中包括:

窗口规格:

[窗口名称][分区条款][顺序条款][框架条款]

Window_name:指查询语句中定义的Window子句。在group by和having子句order by子句的情况下,窗口子句应该放在having子句和order by子句之间。其语法如下:

window_name AS (window_spec)

[,窗口名称为(窗口规格)]...

但是

窗口规格:

[窗口名称][分区条款][顺序条款][框架条款]

从语法结构上,我们可以看到window子句实际上只是提取了over()括号中的内容。

partition _ clause:partition by expr子句。用于指定记录的分组方式。语法中的Expr不仅可以是字段本身,也可以是计算表达式。例如,记录中有一个timestramp类型的ts字段。在MySQL中,按ts分区和按小时分区(ts)都有效。

Order_clause:即order by exprdesc | ASC,exprdesc | ASC。用于指定组内的排序方法。

Frame_clause:用于指定当前分组中的子集划分方法。该帧可以根据当前行的位置在每个分组内移动。使用frame计算总流水(从分区开始到当前行)和滚动平均值。

其语法结构如下:

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用于指示当前行和帧之间的关系。

ROWS:用于定义帧的起始行和结束行(偏移量基于位置);范围:定义帧的间隔。(偏移量基于当前行的值)

frame_entent用于指示帧的起始行和结束行。一种是通过指定开始和结束(frame_start,frame_end。可以不指定frame_end,如果不显式给出,当前行默认为结束行),另一个使用between(frame_between)。frame_between的语法非常简单。我们来看看frame_start和frame_end。

当前行:与行一起使用时,边界是当前行。与range一起使用时,边界是当前行的对等体(个人认为这里所说的对等体应该是与当前行具有相同值的所有记录)。

无界预处理:使用时,每个分区的第一行是边界。

无界以下:使用时,每个分区的第一行为边界。

Exppreceding \ exprfollowing:可以通过expr个性化设置上(前)下(后)的偏移量。


4.代码示例

表格结构如下:



4.1 row_number\dense_rank\rankselect 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 quantitygt;=98order by quantity desc

运行结果如下:


从以上结果可以看出:

rank()函数一旦遇到重复值,序号会断。比如2个7之后下个出现的序号是9。dense_rank()函数中即使有重复值,但是序号是连续的。2个7之后下个出现的序号是8。row_number()不会出现相同的序号。


4.2 cume_dist\percent_rankselect 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 numgt;=27)aorder by num asc

代码运行如下


分析如下:

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时,numlt;28的记录数共有5个,所以percent_rank()的值为5/9; 而当num=29时,其cume_dist()=7/9;以此类推,直到最大值36对应的值为1.这两个函数的作用有点像计算中位数。


4.3 first_value\last_value\nth_valueselect 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

代码运行结果如下(注意这三个函数的计算结果是截止到当前行的)



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

代码运行如下:


从结果分析:

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


4.5 lag\leadselect 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

代码运行如下:


请注意,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

代码运行如下:



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

代码运行如下:


当frame_clause不存在时,默认框架与order by子句的存在相关:

如果有order by子句,则默认的frame是从当前分区第一行到当前行。即在此种情况下,默认的frame为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW如果没有order by子句,则默认的frame是指该分区。如果此时也没有partition by子句,则相当于全部数据。


4.8window子句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

代码运行如下:



4.9rows和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

代码运行如下:


对上述代码的分析:

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

我们先来看看month_row的区别。month_row列的计算结果是分区中按month_1升序排序的当前行之前的两行(由between和定义)的总和。因此,行之间和行之后定义的偏移量是基于它们在分区中的排列位置。

再看看month_range。通过分析其实验结果可以发现,month_range列的计算是month_1=当前行-1,month_1=当前行-2(-1,-2由between an子句决定。前面代表负数,后面代表正数)所有列的总和。再看rn _ range,rn _ range列的计算结果是month_1= current row -2的分区中所有列的总和。因此,范围之间和之后定义的偏移量基于当前行的值。

本文关于MySQL8.0中窗口函数的示例代码到此为止,更多关于MySQL8.0中窗口函数的内容

0

精彩评论

暂无评论...
验证码 换一张
取 消