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) 
Top comments (0)