MySql高级用法- 窗口函数

目录

一、窗口函数概念

二、示例数据创建

三、聚合函数搭配使用

1、Max()聚合函数

2、Sum()聚合函数

四、专用窗口函数搭配使用

4-1 序号专用函数

1、row_number() 当前行组内编号

2、rank() 带间隙排序

3、dense_rank() 不带间隙排序

4-2 分布专用函数

1、percent_rank() 百分比排序

2、cume_dist() 累积分布

4-3 头尾专用函数

1、first_value() 取组内第一个值

2、last_value() 取组内最后一个值

4-4 前后专用函数

1、lag() 取组内当前行前面的值

2、lead() 取组内当前行后面的值

4-5 其它专用函数

1、nth_value() 取框架内的第几个值

2、ntile() 组内再次分N组

        窗口函数是从mysql8.0开始才有的。

一、窗口函数概念

        窗口函数指在sql查询当中对数据进行分组计算或聚合运算，每一组数据相当于一个窗口。

与group by分组不同的是：

•   group by是对每一个分组数据进行计算，返回每个分组的结果。（每一组返回一个结果）
•   窗口函数是对每一个分组数据的每一条数据进行计算，返回每个分组的结果。（每一行返回一个结果）

注意： 窗口函数不会缺少数据，会返回每一行。

           group by会返回每一组数据的第一条数据，会缺少行。

            看见与over关键字进行搭配，就是窗口函数。

二、示例数据创建

create table windows(
id smallint unsigned not null auto_increment primary key,
name varchar(32),
val smallint);
 
insert into windows values
(null,'windows1',1),
(null,'windows1',2),
(null,'windows1',2),
(null,'windows1',3),
(null,'windows1',3),
(null,'windows1',3),
(null,'windows2',100),
(null,'windows2',200),
(null,'windows2',300),
(null,'windows2',400),
(null,'windows2',500);
 
select * from windows;

三、聚合函数搭配使用

 以下举两个常用的聚合函数例子：

1、Max()聚合函数

其中 PARTITION BY 关键字用于分组

SELECT 
		w.id,
		w.name,
		MAX(w.val) over(PARTITION BY w.name) AS maxVal
FROM
	windows w

结果返回的maxVal就是按照分组计算出来的每个分组的最大值。

注意：over单独使用，没有参数时，会把整个数据当成一组。

SELECT 
		w.id,
		w.name,
		w.val,
		MAX(w.val) over() AS maxVal
FROM
	windows w

2、Sum()聚合函数

对每一组内的数据进行相加

SELECT 
		w.id,
		w.name,
		w.val,
		SUM(w.val) over(PARTITION BY w.name) AS sumVal
FROM
	windows w

四、专用窗口函数搭配使用

4-1 序号专用函数

1、row_number() 当前行组内编号

        返回当前行在分组内的位置编号。

SELECT 
		w.id,
		w.name,
		w.val,
		ROW_NUMBER() over(PARTITION BY w.name) AS code
FROM
	windows w

  如果over里面加入oreder by子句，会影响输出结果。

SELECT 
		w.id,
		w.name,
		w.val,
		ROW_NUMBER() over(PARTITION BY w.name ORDER BY w.val DESC) AS code