MySql在8.0的版本增加了对开窗函数的支持,终于可以在MySql使用开窗函数了。开窗函数又称OLAP函数(Online Analytical Processing).
- 开窗函数的语法结构:
#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