oracle rank聚合函数

桃扇骨 2021-09-26 17:16 378阅读 0赞

#### 语法 ####

![这里写图片描述][70]

#### 使用目的 ####

> 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;

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

#### 原理 ####

或者说是等效的语句

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';

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

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  
一模一样的套路，不做解释了。

[70]: /images/20210923/ffcdac3560d440df82ab279e00b966c1.png