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

MySql在8.0的版本增加了对开窗函数的支持，终于可以在MySql使用开窗函数了。开窗函数又称OLAP函数（Online Analytical Processing）.

1. 开窗函数的语法结构：

#Key word :Partiton 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