一、开窗函数(排序相关)
1、窗口函数相关介绍:
ROW_NUMBER(): 返回类型BIGINT ,根据具体的分组和排序,为每行数据生成一个起始值等于1的唯一序列数。
RANK(): 返回类型BIGINT,对组内的数据惊喜排名,如果名次相同,则排序也相同,但是下一个名次的排名序号会出现不连续。
**DENSE_RANK() :**返回类型BIGINT ,dense是稠密的意思,dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。当出现名次相同时,则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。
PERCENT_RANK(): 返回类型DOUBLE,计算给定行的百分比排名。可以用来计算超过百分之多少的人,排名计算公式为:(分组当前行的rank值-1)/(分组内总行数-1)
**CUME_DIST()😗*返回类型DOUBLE,计算某个窗口或分区中某个值的累计分布,假定升序排序,则使用以下公式确定累计分布,小于等于当前值X的行数/窗口或partition分区内总行数。其中,x等于order by 子句中指定的列的当前行中的值。
NTILE(): 返回值类型INT,已排序行划分为大小尽可能相等的指定数量的排名的组,并返回给定行所在的组的排名,如果切片不均匀,默认增加第一个切片的分布,不支持 rows between。
2、应用例子
准备数据
row_number() ,rank(),dense_rank(),parcent_rank() 使用。
select *,
ROW_NUMBER() over(partition by dealer_id order by sales desc) rk01,
RANK() over(partition by dealer_id order by sales desc) rk02,
DENSE_RANK() over(partition by dealer_id order by sales desc) rk03,
PERCENT_RANK() over(partition by dealer_id order by sales desc) rk04
from q1_sales;
结果数据:
cume_dist() order by 默认是升序
select *
cume_dist() over(partiton by dealer_id order by seles ) rk05,
cume_dist() over(partition by dealer_id order by sales desc) rk06
from q1_sales;
结果为:
select *,
NTILE(2) over(partition by dealer_id order by sales ) rk07,
NTILE(3) over(partition by dealer_id order by sales ) rk08,
NTILE(4) over(partition by dealer_id order by sales ) rk09
from q1_sales;
结果为:
**
如果分组内要多个字段排序如果是降序要分别注明。不注明默认都是升序。
注意:例如实现 name_1,name_2,name_3 降序操作为。
row_number() over(partition by age order by name_1 desc,name_1 desc,name_1 desc) as rn
order by name1,name2 desc 意思是 按照name1 升序,name2降序