cume

SQL> create table cume (   2  id integer,   3  value number(8,2),    4  name varchar2(30));

Table created.

SQL> SQL> select  * from cume;

        ID      VALUE NAME ---------- ---------- ------------------------------------------------------------          1        123 t1          2        234 t2          2        234 t21          3        345 t3          4        456 t4          5        567 t5          6        567 t6

7 rows selected.

SQL> SQL> select id, value, cume_dist() over (order by value desc) as cume_dist, percent_rank() over (order by value desc) as percent_rank from cume;

        ID      VALUE  CUME_DIST PERCENT_RANK ---------- ---------- ---------- ------------          5        567 .285714286            0          6        567 .285714286            0          4        456 .428571429   .333333333          3        345 .571428571           .5          2        234 .857142857   .666666667          2        234 .857142857   .666666667          1        123          1            1

7 rows selected. 数字看着不是很整齐，我们处理下 SQL> delete from cume where id = 2;

2 rows deleted.

SQL> select id, value, cume_dist() over (order by value desc) as cume_dist, percent_rank() over (order by value desc) as percent_rank from cume;

        ID      VALUE  CUME_DIST PERCENT_RANK ---------- ---------- ---------- ------------          5        567         .4            0          6        567         .4            0          4        456         .6           .5          3        345         .8          .75          1        123          1            1

SQL>  从上面的例子我们可以看出 cume_dist () 函数统计的范围是 0< cume_dist ()