oracle rank聚合函数

语法

使用目的

Purpose
RANK calculates the rank of a value in a group of values. The return type is NUMBER.

就是返回指定值在一个集合中的位置。

使用示例

建立测试表

create table nayi_180517 as 
select decode(level, 6, 5, level) lev from dual
connect by level <= 10;

select*from nayi_180517;

LEV
1
2
3
4
5
5
7
8
9
10

select rank(6666) within group(order by lev desc) rk1, 
       rank(6, 6) within group(order by lev, lev) rk2
  from nayi_180517;

RK1	RK2
1	7

原理

或者说是等效的语句

select rank(6) within group(order by lev) rk2
  from nayi_180517;

with tab as (
select lev, 'a' typ from nayi_180517
union all
select 6, 'b' from dual)

select rk
  from (select rank() over(order by lev) rk, typ from tab) t1
 where t1.typ = 'b';

RK
7

看懂下面两个语句是等效的即可说明真正理解了语法

select rank(6, 6) within group(order by lev + 1, lev + 1) rk
  from nayi_180517;

with tab as (
select lev + 1 lev, lev + 1 lev2, 'a' typ from nayi_180517
union all
select 6, 6, 'b' from dual)

select rk
  from (select rank() over(order by lev, lev2) rk, typ from tab) t1
 where t1.typ = 'b';

相似函数

percent_rank
percentile_cont
percentile_disc
一模一样的套路，不做解释了。