c 做网站开发实例,论坛优化seo,建设网站需要学习什么,网站运营系统测试环境#xff1a;mysql8.0.18 官方文档#xff1a;https://dev.mysql.com/doc/refman/8.0/en/window-functions.html 一、窗口函数介绍二、语法结构三、自定义窗口1.rows#xff08;重点#xff09;2.range3.默认窗口 四、常用窗口函数示例1.row_number rank mysql8.0.18 官方文档https://dev.mysql.com/doc/refman/8.0/en/window-functions.html 一、窗口函数介绍二、语法结构三、自定义窗口1.rows重点2.range3.默认窗口 四、常用窗口函数示例1.row_number rank dense_rank2.lead lag3.first_value last_value nth_value4.ntile5.cume_dist percent_rank了解 一、窗口函数介绍
开窗函数是mysql8.0中的新特性用于实现和group by分组函数类似的分组聚合功能。区别在于
分组函数对一个集合输出一个标量结果改变了数据的粒度且丢失了非分组字段及非聚合字段的信息。开窗函数分别以每一行为当前行与当前行相关的所有行为窗口对同一个窗口内的数据进行聚合等类似操作结果附加到当前行的后面不改变原始数据粒度不丢失原始数据信息。
二、语法结构
开窗函数|聚合函数 over([分组函数] [排序函数] [自定义窗口]) over是进行开窗里面的分组函数、排序函数、自定义窗口都可以省略。
开窗函数|聚合函数不可省略用于对窗口范围内的所有数据行进行某种指定操作。可以是只适用于开窗函数的非聚合函数(https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html)也可以是适用于group by的聚合函数(https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html)。 分组函数partition by ...根据指定的字段对表分组分组字段可以有多个。省略时表示整个表为一组。 排序函数order by ...排序字段也可以有多个当排序字段为多个时表示先按照第一个字段排序当第一个字段相等确定不了顺序时再按照第二个字段排序以此类推…
三、自定义窗口
这部分可以直接查看文档https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html个人觉得这部分算是开窗函数里最重要的了弄明白了各种情况下窗口的大小其他的就没啥容易混淆的点了。
mysql中的窗口类型有两种rows和range。rows是以物理行距离为基准通过计算与当前行的物理距离计算窗口大小range是以当前行的值为基准通过计算与当前行值的差值计算窗口大小。
窗口大小可通过between 上界 and 下界来指定其中窗口的上下界分别有下面几种取值
unbounded preceding包含当前行及当前行之前的所有记录。n preceding包含当前行及当前行之前的n-1行实际窗口大小n。current row仅包含当前行。unbounded following包含当前行及当前行之后的所有记录。n following包含当前行及当前行之后的n-1行实际窗口大小n。
当窗口下界为current row时可以不使用between and也就是下面几种情况可简写 1between unbounded preceding and current row -- unbounded preceding 2between n preceding and current row -- n preceding 3between current row and current row -- current row 而following的情况不支持简写原因可以参考下怎么理解mysql开窗函数 unbounded following这种简写形式不支持 而unbounded preceding支持觉得有些道理。
1.rows重点
物理范围窗口窗口大小只与当前行的物理距离有关。下面造点测试数据
create table test_rows_range as
select 1 as id, 2020-10-03 as trans_date, 349 as sales
union all
select 2 as id, 2020-10-01 as trans_date, 563 as sales
union all
select 3 as id, 2020-10-02 as trans_date, 716 as sales
union all
select 4 as id, 2020-10-05 as trans_date, 628 as sales
union all
select 5 as id, 2020-10-02 as trans_date, 412 as sales
union all
select 6 as id, 2020-10-02 as trans_date, 857 as sales
union all
select 7 as id, 2020-10-08 as trans_date, 201 as sales
union all
select 8 as id, 2020-10-05 as trans_date, 191 as sales
union all
select 9 as id, 2020-10-06 as trans_date, 675 as sales
union all
select 10 as id, 2020-10-08 as trans_date, 941 as sales;select *,sum(sales) over(order by trans_date rows between 1 preceding and 1 following) as sum1, -- 当前行的前一行、后一行、及当前行共3行作为一个窗口sum(sales) over(order by trans_date rows unbounded preceding) as sum2, -- 当前行及当前行之前的所有行为窗口sum(sales) over(order by trans_date rows current row) as sum3 -- 仅取当前行为窗口
from test_rows_range;output
2.range
逻辑范围窗口业务中一般都会和order by连用否则使用range窗口没啥实际意义。range类型窗口的上下界依然可以沿用rows类型窗口的上下界规则是以当前行order by字段的值为基准对值按照指定的上下界范围进行加减操作以确定逻辑窗口上下界的值。例如当前行的值为3自定义窗口大小为range between 2 preceding and 1 following那么此时逻辑窗口的临界值为[3-2, 31] - [1, 4]所有order by字段值在该范围内的行都属于当前行窗口中的记录。 这里有两个小细节 1因为range是以行的值为基准按照指定的上下界对值进行加减操作以确定窗口上下临界值的范围因此range窗口的order by排序字段只能是数值型或日期时间类型这样支持逻辑意义上加减的字段类型否则像varchar这种类型就会报下面这个错误 3587 - Window unnamed window with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type 2当排序字段为数值型时自定义窗口的格式可以直接沿用rows中列举的上下界例如range n preceding这时窗口的上界值为当前行的值-n。但是如果为时间日期类型时对于n preceding这样的上界就不能使用了因为mysql不知道是在这个时间日期的基础上-n day还是-n hour因此需要用range between interval 1 day preceding and interval 1 day following这种语法格式明确一下否则会报下面异常 3588 - Window unnamed window with RANGE frame has ORDER BY expression of datetime type. Only INTERVAL bound value allowed. 但是对于unbounded preceding这样的上界就不用interval的形式指定很好理解这种上界包括了所有小于当前行的值的记录此时是- day还是- hour已经不重要了。 -- 修改trans_date字段类型为date
alter table test_rows_range modify trans_date date;select *,sum(sales) over(order by trans_date range between interval 1 day preceding and interval 1 day following) as sum1, -- 当前行的日期前一天的日期后一天的日期 的所有行作为一个窗口sum(sales) over(order by trans_date range unbounded preceding) as sum2, -- 所有小于等于当前行日期的行作为窗口sum(sales) over(order by trans_date range current row) as sum3 -- 仅取和当前行日期相等的行作为窗口
from test_rows_range;output
3.默认窗口
如果不显式指定窗口大小则默认窗口大小主要分为over()中有没有order by子句两种情况
没有order by子句默认窗口为每个组内的全部行。有order by子句默认窗口为range unbounded preceding。
select *,sum(sales) over() as sum1, -- 无order by窗口范围为全部行sum(sales) over(order by trans_date) as sum2 -- 有order by窗口范围为当前行及之前的所有行
from test_rows_range;output
四、常用窗口函数示例
这部分可以直接查看文档https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html
1.row_number rank dense_rank
这三个都是排序函数区别在于
row_number()序号不重复不间断。rank()序号可重复可间断。dense_rank()序号可重复不间断。
select *,row_number() over(order by trans_date) as rn,rank() over(order by trans_date) as rk, dense_rank() over(order by trans_date) as drk
from test_rows_range;output
2.lead lag
对指定字段整体上移(lead)或者下移(lag)。
lead(col, n, default)上移。参数col表示移动的字段不可缺省参数n表示移动的距离可缺省缺省值默认值为1参数default表示当出现空值时用来填充的默认值可缺省缺省时用null填充。lag(col, n, default)下移参数含义同上。
select *,lead(sales,1,0) over(order by trans_date) as lead, -- 将sales字段值整体上移1位空值用0填充lag(sales,1,0) over(order by trans_date) as lag1, -- 将sales字段值整体下移1位空值用0填充lag(sales) over(order by trans_date) as lag2 -- 将sales字段值整体下移1位空值不处理
from test_rows_range;output
3.first_value last_value nth_value
下面几个函数的作用是取窗口中指定顺序的字段值。
first_value(col)取窗口中字段col的第一个值。last_value(col)取窗口中字段col的最后一个值。nth_value(col, n)取窗口中第n顺序的值。
select *,first_value(sales) over(order by trans_date) as first, -- 取每个窗口第一个值last_value(sales) over(order by trans_date) as last, -- 取每个窗口最后一个值nth_value(sales,2) over(order by trans_date) as nth -- 取每个窗口第二个值
from test_rows_range;output
4.ntile
将数据分组。
ntile(n)n是指定的组数。分组逻辑是从小到后为每条数据打上一个组号的标签尽可能使每组内的数据相对均匀当每组内的数据不能完全一样时多余的数据优先给组号较小的分组。
select *,ntile(4) over(order by trans_date) as ntile4 -- 数据均匀分为4组
from test_rows_range;output
5.cume_dist percent_rank了解
这两个函数基本不用了解即可下面是两个函数的官方描述。 从文档中可以看到这两个函数都应该与order by放在一起使用返回的结果也都和order by字段的值有关。
cume_dist返回的是窗口中所有小于等于当前行order by字段的值的总行数 / 窗口所在的分组内的总行数。percent_rank返回的是窗口中所有小于当前行order by字段的值的总行数 / 窗口所在的分组内的总行数-1。
select *,cume_dist() over(order by trans_date) as cume_dist,percent_rank() over(order by trans_date) as percent_rank
from test_rows_range;output 解释一下这个输出结果默认窗口range unbounded preceding对于cume_dist列第一行trans_date为’2020-10-01’时窗口内小于等于这一行的总行数为1而这个窗口所在的分组也就是整个表总行数为10因此第一行结果为0.1而对于后面3个连续的0.4是因为窗口类型为range小于等于第二行值’2020-10-02’的总行数为4所以结果为0.4。
对于percent_rank列窗口所在的分组也就是整个表总行数为10所以分母都为10-19。窗口内小于第一行’2020-10-01’的总行数为0所以该列第一个值为0后面以此类推…
PS 文档中没看到直接的描述但在测试中发现了这两个函数有一些特点 1只适用于range类型窗口这并不是说显式指定rows会报错而是mysql忽略指定输出的结果和range类型一致。 2窗口范围自定义无效也就是只能为默认窗口range unbounded preceding像是修改为range between interval 1 day preceding and interval 1 day preceding无效。
select *,cume_dist() over(order by trans_date) as dist_range,cume_dist() over(order by trans_date rows unbounded preceding) as dist_rows,percent_rank() over(order by trans_date) as percent_range,percent_rank() over(order by trans_date rows unbounded preceding) as percent_rows,percent_rank() over(order by trans_date range between interval 1 day preceding and interval 1 day preceding) as percent_range1 -- 自定义窗口无效不影响输出
from test_rows_range;output 可以看到结果均无变化我的理解是这两个函数都是用来计算某行记录在排序后的总体分布情况因此rows类型的窗口因为忽略了重复值的影响所以不合适。而在此需求中更没必要让用户可以自定义指定窗口因为这两个需求的总体思路都是按照当前行值在所有数据中的相对位置 / 所有记录数这样的思路来计算。
