Mysql8之窗口函数

星星妳睡了吗

已于 2023-08-06 09:58:08 修改

阅读量86

点赞数 2

文章标签：数据库算法大数据

于 2023-08-06 09:45:51 首次发布

本文链接：https://blog.csdn.net/j218255/article/details/132086558

版权

partition by，order by

first_value和last_value

前言

文章内容是关于Mysql8的窗口函数的使用,内容会结合我做到的相关题目或者结合相关业务的分析
来进行写作,可能并不全但后续会进行补充.

数据准备

以一张购物表为例

create table if not exists shop(name varchar(20),buy_date date,cost int);
Truncate table shop;
insert into shop (name,buy_date, cost) values ('nanxi', '2023-02-05', '40');
insert into shop (name,buy_date, cost) values ('jack ', '2022-04-06', '15');
insert into shop (name,buy_date, cost) values ('bome ', '2023-08-04', '70');
insert into shop (name,buy_date, cost) values ('xixi ', '2023-06-07', '66');
insert into shop (name,buy_date, cost) values ('jack ', '2021-02-04', '80');
insert into shop (name,buy_date, cost) values ('bome ', '2022-07-15', '10');
insert into shop (name,buy_date, cost) values ('bolle', '2021-06-25', '130');
insert into shop (name,buy_date, cost) values ('fufu ', '2022-02-21', '70');
insert into shop (name,buy_date, cost) values ('nanxi', '2023-11-16', '89');
insert into shop (name,buy_date, cost) values ('bolle', '2022-07-04', '80');

实例：

聚合函数+over()

首先注意窗口函数的执行是只位于order by 之前的

如果我们使用正常的方法对购物表去统计每个人有几条记录,并输出3列信息再加一列统计信息,此时对于多次出现的人,我们是需要使用gorup by子句去分组统计name列,但是同时我们又想要输出全部行呢,使用group by会使得name相同的变成一组,最终输出一行,并且只能输出

name列和统计列,想要输出表中jack的购物信息,就没有办法

但是如果我们使用窗口函数就不同了

可以看到使用了窗口函数 count(1) over() 之后

多了一列,但是很明显这一列统计的是全部人的个数,但是它已经可以每行显示了,还记得标题吗,窗口函数,窗口指的是什么呢,当我们使用聚合函数 over() 这个格式时,默认窗口就一个,就是整个表,所以我们想要每个人的话还是需要进行分组,但是我们可以在窗口函数里分组

partition by，order by

可以看到我们在over()里加入了partition by 和order by子句,首先partition by 类似于group

by子句,但是partition by 在窗口函数里的作用的进行分窗口的,partition by name就是按照姓名进行分窗口,可以看到表按name分成了6个窗口,而order by子句是对每个窗口进行排序

sum() 叠加求和性

对于聚合函数 sum() over(order by...) 一旦由窗口进行了order by 之后会产生如下效果

可以看到按照name分成多个窗口,每个窗口对cost列进行求和,cnt列不再是相同的了,而是按照cost列叠加逐渐相加

window子句

window子句：
PRECEDIN: 往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED ：起点，

UNBOUNDED PRECEDING 表示从前面的起点，

UNBOUNDED FOLLOWING：表示到后面的终点
例:
select name,buy_date,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加
sum(cost) over(partition by name order buy_date) as sample3,--按name分组，组内数据累加
sum(cost) over(partition by name order by buy_date rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by buy_date rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by buy_datee rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by buy_date rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from shop;

注意:

意思是本行和前面的两行进行求和

sum(salary) over(partition by id order by month rows between 2 PRECEDING and current row)

意思也是本行和前面的两行进行求和

sum(salary) over(partition by id order by month range 2 preceding) Salary

但是 range 和row 并不相同 row就是物理上的两行

比如这两个案例,都是通过id分组按照月份进行排序,如果月份是按顺序的那这两个结果一样

但是如果月份并不连续例如

month Salary

1 100

3 200

5 300

7 200

此时如果在读取month=5这一行那么rows 统计的就是1,3,5三行也就是600

但如果是range,range会按照逻辑进行统计,统计的就是3,4,5三个月,4月没有就是0结果是500

排序问题

row_number(),rank(),dense_rank()

这三个窗口在进行数据分析时经常使用

row_number()从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列

1,2,3,4,5....
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位

1,2,2,4,5
DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

1,2,2,3,4,5....

注意

　在使用排名函数的时候需要注意以下三点：

　　1、排名函数必须有 OVER 子句。

　　2、排名函数必须有包含 ORDER BY 的 OVER 子句。

　　3、分组内从1开始排序。

例：

#注意对于row_number，对于相同的数据会按照表中原来的数据进行排序

这时要注意如果 row_number() over(order by cost desc)这样逆序排序,同时还有一列是

row_number() over(order by cost) 这样结果表就是按cost正序的,谁是最后进行的order by

就是按照谁的顺序,但是此时问题也出现了,虽然顺序是按最后的排序了,但是之前的排序如果有相同数据就会出现乱序

可以看到70，80两对,顺序就是按照原来的顺序,并没有逆序

此时按照多列进行排序解决整个问题

对于一次正序,一次逆序,多用于求中位数的情况
当行为偶数时两列相差1为中位数,奇数时相等的为中位数

注意对于排序返回值是BIGINT是无符号的,所以不可以直接相减

相减要使用强制转换,换成范围更大的DECIMAL

abs( CAST(rk1 AS DECIMAL) - CAST(rk2 AS DECIMAL))=1