Oracle analytic function-percent_rank

聚合语法:PERCENT_RANK (expression1, … expression_n) WITHIN GROUP (ORDER BY expression_order1, …expression_order_n)
分析语法:PERCENT_RANK () OVER ([ query_partition_clause ] order_by_clause)

percent_rank和分析函数cume_dist类似,也是计算累计分布的函数。不同的地方是percent_rank计算的时候是:(相对位置-1)/总行数。对于重复行,计算的时候取重复行的第一行的位置。函数返回值为[0,1]之间。

原始数据

SQL> select first_name || ' ' || last_name as Name, salary
  2    from employees
  3   where department_id = 30 order by salary;

NAME                                                                                             SALARY
-------------------------------------------------------------------------------------------- ----------
Karen Colmenares                                                                                   2500
Guy Himuro                                                                                         2600
Alexander Khoo                                                                                     2600
Sigal Tobias                                                                                       2600
Shelli Baida                                                                                       2900
Den Raphaely                                                                                      11000
已选择6行。

作为聚合函数使用,当使用percent_rank计算薪水值为2600的累计分布的时候,2600为重复行,
计算的时候取2600出现的第一行即所在的集合的第二行。计算公式为2-1/6=0.1666667。

SQL> select percent_rank(2600.00) within group(order by salary) as percent_rank_sal_2600
  2    from employees
  3   where department_id = 30;

PERCENT_RANK_SAL_2600
---------------------
           .166666667

作为分析函数使用,计算每个人在部门按照薪水排序中的相对位置。

SQL> select first_name,last_name,salary,percent_rank() over(order by salary) as percent_rank_sal_analytic
  2  from employees
  3  where department_id = 30;

FIRST_NAME                               LAST_NAME                               SALARY PERCENT_RANK_SAL_ANALYTIC
---------------------------------------- -------------------------------------- ---------- -------------------------
Karen                                    Colmenares                                2500                         0
Guy                                      Himuro                                    2600                        .2
Alexander                                Khoo                                      2600                        .2
Sigal                                    Tobias                                    2600                        .2
Shelli                                   Baida                                     2900                        .8
Den                                      Raphaely                                 11000                         1
已选择6行。
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s