AntDB-Oracle Compatibility Developer’s Manual P3–56

PERCENT_RANK

Calculates the percentage ranking of the given row. This function has no arguments, but requires empty parentheses. The return value ranges between 0 and 1 (inclusive). The PERCENT_RANK of the first row in any set is 0.

Example:

Calculate the percentage ranking of each employee within the same position.

select empno,job, sal, percent_rank()  over (partition by job order by sal)  from emp;
 EMPNO |    JOB    |  SAL  | PERCENT_RANK() OVER (PARTITION BY JOB ORDER BY SAL)  
-------+-----------+-------+-------------------------------------------------------
  7902 | ANALYST   |  8000 |                                                    0
  7788 | ANALYST   |  8000 |                                                    0
  7469 | CLERK     |  4800 |                                                    0
  7900 | CLERK     |  5950 |                                                 0.25
  7934 | CLERK     |  6300 |                                                  0.5
  7369 | CLERK     |  6800 |                                                 0.75
  7876 | CLERK     |  8100 |                                                    1
  7782 | MANAGER   |  7450 |                                                    0
  7566 | MANAGER   |  7975 |                                                  0.5
  7698 | MANAGER   | 11850 |                                                    1
  7839 | PRESIDENT | 10000 |                                                    0
  7521 | SALESMAN  |  6250 |                                                    0
  7654 | SALESMAN  |  6250 |                                                    0
  7844 | SALESMAN  |  6500 |                                   0.6666666666666666
  7499 | SALESMAN  |  7600 |                                                    1
(15 rows)

REGR_SLOPE

Calculate the slope.

Example:

create table test(id int, num int);
insert into test values(1, 298),(2, 368),(3, 458),(4, 328),(5, 108),(6, 667),(7, 123),(8, 555);
select count(*),regr_slope(t.num,test.num) from 
(select id-1 as id, num from test) as t,test where t.id=test.id ;
 COUNT(*) | REGR_SLOPE(T.NUM,TEST.NUM)  
----------+-----------------------------
       28 |         -0.8425517064826854
(1 row)