【参考链接】https://zhuanlan.zhihu.com/p/98655285
mysql8.0+ 开窗函数
开窗函数又称OLAP函数(Online Analytical Processing)
1、开窗函数的语法结构:
#Key word :Partiton by & order by
<开窗函数> over ([PARTITION by <列清单>]
Order by <排序用列清单>)
开窗函数大体分为两种:
a. 能够作为开窗函数的聚合函数:(sum, avg, count, max, min)
b. 专用开窗函数:(Rank, Dense_Rank, Row_Number)
【1】实现分组排名效果
【2】两个题目,一是选出分数最高的客户,二是选出每个用户的最高分数
2、专用窗口函数使用
a) 使用方法
Rank 函数是记录排序顺序的函数,partition by 用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,它和聚合函数、groupby不同的地方在于它能够返回一个分组中的多条记录,而聚合函数一般只有一个反映统计值的记录。
-- 查看表
SELECT * FROM product;
-- 将表中8种商品,根据product_type,按照sale_price进行 序号编排
# 语句中,PARTITION By 指定排序的对象范围(横向上对表进行分组),
# order 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
-- 如果不使用PARTITION By,效果如何?
# 将PARTITION By 函数组置为沉默项
select product_id,product_name,product_type,sale_price,
rank() over (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 '累加'
from product
可以看出以上结果时将sale_price 进行累加,在排序1中计算出上面结果的累计和,表中数据按照order by product_id 进行顺序排列;同理AVG 函数则是将计算累加值的平均值。
Tips:聚合函数作开窗函数,就不是排序那么简单了,可能累加、可能累计平均
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 desc
-- 按 product_type 统计总金额,及其各产品金额占比
-- 按 product_type 统计金额由小到大进行累计,
SELECT product_id, product_name, product_type, sale_price
, sum(sale_price) over(PARTITION BY product_type) as typeSum
, sale_price / sum(sale_price) over(PARTITION BY product_type) as ratioInType
, sum(sale_price) over(PARTITION BY product_type ORDER BY sale_price) as orderCumsum
FROM product ORDER BY product_type, sale_price;