mysql 开窗函数 累加_开窗函数在MySql中的使用

MySql在8.0的版本增加了对开窗函数的支持，终于可以在MySql使用开窗函数了。开窗函数又称OLAP函数(Online Analytical Processing).开窗函数的语法结构：

#Key word :Partiton by & order by over ([PARTITION by ]

Order by )

开窗函数大体分为两种：

a. 能够作为开窗函数的聚合函数：(sum,avg,count,max,min)

b. 专用开窗函数：(Rank,Dense_Rank,Row_Number)

2. 实操练习

1)建立数据表，插入数据

#a. 在本地DataBase新建一个Table,用于存储练习数据

Create Table Product

(product_id CHAR(4) not full,

product_name VARCHAR(100) not full ,

product_type VARCHAR(32) not full ,

sale_price INTEGER ,

purchase_price INTEGER ,

regist_date Date,

primary KEY (product_id));

# b.插入练习数据

# 插入数据

insert into Product values ('0001','T恤衫','衣服',1000,500,'2009-09-20');

insert into Product values ('0002','打孔器','办公用品',500,320,'2009-09-11');

insert into Product values ('0003','运动T恤衫','衣服',400,280,null);

insert into Product values ('0004','菜刀','厨房用具',3000,2800,'2009-01-20');

insert into Product values ('0005','高压锅','厨房用具',6800,5000,'2009-01-15');

insert into Product values ('0006','叉子','厨房用具',500,null,'2009-09-20');

insert into Product values ('0007','擦菜板','厨房用具',880,790,'2008-04-08');

insert into Product values ('0008','圆珠笔','办公用品',100,null,'2009-11-11');

2)专用窗口函数使用

a) 使用方法

Rank 函数是记录排序顺序的函数，不同于Group by 函数，开窗函数的使用不会减少数据表的函数，类别也不会只有一种。

# 将表中8种商品，根据product_type,按照sale_price进行排序

select product_id,product_name,product_type,sale_price,

rank() over (PARTITION by product_type

order by sale_price asc ) as '排序'

from product展示结果

语句中，PARTITION By 指定排序的对象范围(横向上对表进行分组)，order by 指定了按照哪一列，何种顺序进行排列(纵向定义排序规则)。

如果不使用PARTITION By，效果如何？

# 将PARTITION By 函数组置为沉默项

select product_id,product_name,product_type,sale_price,

rank() over

-- PARTITION by product_type

(order by sale_price asc) as '排序'

from product

结果如下：1)数据展示直接按照sale_price 排序 2)价格并列时，排序结果一致，并将排序计数值+1 ;

b)函数区别

上面提到专用开窗函数有三种：Rank,Dense_Rank,Row_Number，这三种有什么区别呢？

#下面将3种函数排序结果分被定义为 排序1，排序2，排序3，进行结果输出：

select product_id,product_name,product_type,sale_price,

rank() over (order by sale_price asc) as '排序1',

dense_rank() over (order by sale_price asc) as '排序2',

row_number() over (order by sale_price asc) as '排序3'

from product

将排序1和排序2 相比，可以看出，dense_rank 函数在计算并列结果时，并列值不占位，出现连续两个3号位时，后面计数结果仍为4；将排序2和排序3相比，可以看出，row-number 无论sale_price 值是否一致，都会计算一个唯一值，并不会计算连续值。

c) 聚合函数作开窗函数

可以将(sum,avg,count,max,min)等聚合函数引用至开窗函数中：

#sum 聚合函数的使用

select product_id,product_name,product_type,sale_price,

sum(sale_price) over (order by product_id) as '排序1'

from product

可以看出以上结果时将sale_price 进行累加，在排序1中计算出上面结果的累计和，表中数据按照order by product_id 进行顺序排列；同理AVG 函数则是将计算累加值的平均值。

d) 保证排序结果的顺序

根据上面结果进行排序时，有时排序结果时无序的，可以使用双order by 进行顺序排列：

# 对计算结果再次进行排列

select product_id,product_name,product_type,sale_price,

rank() over (order by sale_price ) as '排序1'

from product

order by 排序1

好了，可以愉快的去牛客网手撕代码了~~

上图是牛客网真题，要求按照salary 进行排序并计算排序结果：

SELECT emp_no, salaries, DENSE_RANK() OVER(ORDER BY salary DESC) AS rank

WHERE to_date = '9999-01-01' ORDER BY salary DESC, emp_no ASC