1. percent_rank() over (order by .....)
返回某列或某列组合后每行的百分比排序
如下:
with cte as
(
SELECT ROWNUM as n
FROM DUAL
CONNECT BY ROWNUM <= 10
)
select n,percent_rank() over (order by n) as prk
from cte
;
(
SELECT ROWNUM as n
FROM DUAL
CONNECT BY ROWNUM <= 10
)
select n,percent_rank() over (order by n) as prk
from cte
;
结果如下:
PRK为每个N值的相对位置的百分比,该结果看起来会有些奇怪.10个数中的第2个数应该是排在20%才对,但却不是一个整数.
其实内部的逻辑为:
将n进行排序,注意第一位是0而不是1:
然后用它们的序号除以最大的序号则得到上述的结果.
2.percent_rank(....) within group ....
用来求某一个数在一个集合中的对应位置百分比:
with cte as
(
SELECT ROWNUM as n
FROM DUAL
CONNECT BY ROWNUM <= 10
)
select percent_rank(8) within group (order by n) as prk
from cte
;
结果:
PRK
0.7
这个是如何得到的呢?
是将 (8-1)/10 和第一个例子有些区别.