mysql 窗口函数_mysql窗口函数小结

MySQL从8.0开始支持窗口函数，其他一些商业或开源数据库早已支持窗口函数，如Oracle、DB2、 PostgreSQL、SQL Server等。所谓窗口函数，是指对查询的每一行，利用和这一行相关的行构成一 个窗口，对这个窗口进行计算，得到一个值作为结果。

窗口函数的基本用法：

函数名 OVER 子句

over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口范围。①window_name：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；②PARTITION BY 子句：窗口按照某些字段进行分组，窗口函数在不同的分组上分别执行；③ORDER BY子句：按照某些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；④FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

窗口函数与聚合函数的区别

1.聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。

2. 对于聚合函数来说，不能将聚合前后的数据展示在一起，而窗口函数就可以做到这一点。

3.聚合函数也可以用作窗口函数

按照功能划分，可以把MySQL支持的非聚合窗口函数分为4类。

新增测试数据：

CREATE 

排序函数：

row_number：计算当前行在分区中的序号

按照科目进行分组，并给每一条数据都加上行号

SELECT name,sub,grade , row_number() OVER (PARTITION BY sub ORDER BY grade DESC ) 
 AS grade_order  FROM stu_grade_info;

rank：计算当前行在分区中的排名，有间隔

按照科目进行分组，将学生对应分数的由高到底排列有间隙排列，

SELECT name,sub,grade , rank() OVER (PARTITION BY sub ORDER BY grade DESC ) 
  AS grade_order FROM stu_grade_info

dense_rank：计算当前行在分区中的排名，无间隔

按照科目进行分组，将学生对应分数的由高到底排列无间隙排列

SELECT 

重复使用的窗口使用window语句命名。

SELECT name,sub,grade , rank() OVER w AS grade_order_rk,  dense_rank() OVER w 
  AS grade_order_drk  FROM stu_grade_info  
  WINDOW w AS (PARTITION BY sub ORDER BY grade DESC );

分布函数：

percent_rank：

1.计算给定行的百分比排名 计算结果：（相对位置-1）/（总行数-1）]；

2.对于重复值取第一行

3.计算结果：(rank - 1) / (rows - 1)

cume_dist：

1.计算某个值在一组有序的数据中累计的分布值

2.计算结果：相对位置/总行数，返回值为(0,1]；

3.对于重复值取最后一行

查看分数所占的百分比和累积分布值

select name,sub,grade,percent_rank() over(partition by sub order by grade desc) 
  as 'percent_rank',cume_dist() over(partition by sub order by grade desc) 
  as 'cume_dist' from stu_grade_info;

偏移函数：

插入数据：

create table sql27_car_sale(
	id int not null auto_increment primary key,
	name varchar(30) not null comment '名称',
	month varchar(7) comment '月份',
	volume int comment '销量'
)engine=InnoDB;
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-01', 44202);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-02', 25460);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-03', 38914);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-04', 30449);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-05', 40570);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-06', 41653);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-07', 33484);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-08', 38946);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-09', 44579);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-10', 43974);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-11', 46241);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2018-12', 40484);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-01', 59507);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-02', 34379);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-03', 49762);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-04', 30739);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-05', 35752);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-06', 34330);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-07', 36921);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-08', 32135);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-09', 48546);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-10', 40608);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-11', 52691);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2019-12', 65138);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2020-01', 35898);
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2020-02', 3303 );
insert into sql27_car_sale(name, month, volume) values('大众朗逸', '2020-03', 24698);

计算大众朗逸汽车销量的同比、环比。

SELECT
	Z.MONTH,
	Z.volume,
	Z.last_volume,
	ifnull(( Z.volume - Z.last_volume )/ Z.last_volume * 100, 0 ) AS mm,
	Z.last_year_volume,
	ifnull(( Z.volume - Z.last_year_volume )/ Z.last_year_volume * 100, 0 ) AS yy 
FROM
	(	SELECT NAME,MONTH,volume,
		lag( volume, 1, 0 ) over ( ORDER BY MONTH ASC ) AS last_volume,
		lag( volume, 12, 0 ) over ( ORDER BY MONTH ASC ) AS last_year_volume 
	FROM sql27_car_sale  
	) AS Z;

分组函数：

ntile函数的说明

1. ntile(n)表示将数据分为n组，组的编号范围1-n。

2. 如果分区中的总记录数能被n整除，则每组的记录的相等（平分）。

3. 如果分区中的总记录数不能被n整除，每组的记录数相差为1。

将大众朗逸汽车的销量数据分成5组，并显示每行数据的所属组编号。

select name,month,volume,ntile(5) over(order by volume desc) as 'ntile'
from sql27_car_sale

聚合参数：

将学生成绩按照课程分组，列出每科最高分，最低分，平均分

select name,sub,grade,  max(grade) over w grade_max,	min(grade) over w grade_min,
AVG(grade) over w grade_avg from stu_grade_info window w as(PARTITION  by sub )