窗口函数 PERCENT_RANK

最新推荐文章于 2024-08-30 09:34:42 发布

炉子吗尅_那撸多

最新推荐文章于 2024-08-30 09:34:42 发布

阅读量1.3k

点赞数

本文链接：https://blog.csdn.net/java_luzimakei/article/details/109490508

版权

说明

–PERCENT_RANK ：分组内当前行的RANK值-1/分组内总行数-1

查询语句

select 
  dept,
  userid,
  sal,
  percent_rank() over (order by sal) as rn1, --分组内
  rank() over (order by sal) as rn11, --分组内的rank值
  sum(1) over (partition by null) as rn12, --分组内总行数
  percent_rank() over (partition by dept order by sal) as rn2,
  rank() over (partition by dept order by sal) as rn21,
  sum(1) over (partition by dept) as rn22 
from cookie.cookie3;

查询结果

结果说明

–PERCENT_RANK ：分组内当前行的RANK值-1/分组内总行数-1

rn1 == (rn11-1) / (rn12-1)

rn2 == (rn21-1) / (rn22-1)

rn1: rn1 = (rn11-1) / (rn12-1) 
       第一行,(1-1)/(5-1)=0/4=0
       第二行,(2-1)/(5-1)=1/4=0.25
       第四行,(4-1)/(5-1)=3/4=0.75
rn2: 按照dept分组，
     dept=d1的总行数为3
     第一行，(1-1)/(3-1)=0
     第三行，(3-1)/(3-1)=1