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)