Oracle analysis function PERCENTILE_CONT, percentile Function

Oracle analysis function PERCENTILE_CONT, percentile Function

Query the salaries of people with the salary distribution of 25%, 50%, and 75% in each department. percent_rank () is the relative position in the ranking.

Create table EMP
(
Empno number (4) not null,
ENAME VARCHAR2 (10 ),
JOB VARCHAR2 (9 ),
Mgr number (4 ),
Hiredate date,
Sal number (7,2 ),
Comm number (7, 2 ),
Deptno number (2)
);
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7369, 'Smith ', 'cler', 7902, to_date ('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 20 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7499, 'allen ', 'salesman', 7698, to_date ('20-02-1981 ', 'dd-mm-yyyy'), 1600.00, 300.00, 30 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7521, 'ward ', 'salesman', 7698, to_date ('22-02-1981 ', 'dd-mm-yyyy'), 1250.00, 500.00, 30 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7566, 'Jones ', 'manager', 7839, to_date ('02-04-1981', 'dd-mm-yyyy '), 2975.00, null, 20 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7654, 'martin ', 'salesman', 7698, to_date ('28-09-1981 ', 'dd-mm-yyyy'), 1250.00, 1400.00, 30 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7698, 'bucke', 'manager', 7839, to_date ('01-05-1981 ', 'dd-mm-yyyy'), 2850.00, null, 30 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7782, 'clark', 'manager', 7839, to_date ('09-06-1981 ', 'dd-mm-yyyy'), 2450.00, null, 10 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7788, 'Scott ', 'analyst', 7566, to_date ('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7839, 'King', 'President ', null, to_date ('17-11-1981', 'dd-mm-yyyy '), 5000.00, null, 10 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7844, 'turner ', 'salesman', 7698, to_date ('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7876, 'adams', 'cler', 7788, to_date ('23-05-1987 ', 'dd-mm-yyyy'), 1100.00, null, 20 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7900, 'James ', 'cler', 7698, to_date ('03-12-1981', 'dd-mm-yyyy '), 950.00, null, 30 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7902, 'Ford ', 'analyst', 7566, to_date ('03-12-1981', 'dd-mm-yyyy '), 3000.00, null, 20 );
Insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values (7934, 'miller ', 'cler', 7782, to_date ('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10 );
Commit;

SQL> select e. ename, e. sal, e. deptno,

Percent_rank () over (partition by deptno order by sal desc) p_rank,
PERCENTILE_CONT (0) within group (order by sal desc)
Over (partition by deptno) max_sal,
PERCENTILE_CONT (0.25) within group (order by sal desc)
Over (partition by deptno) max_sal_25,
PERCENTILE_CONT (0.5) within group (order by sal desc)
Over (partition by deptno) max_sal_50,
PERCENTILE_CONT (0.75) within group (order by sal desc)
Over (partition by deptno) max_sal_75
From emp e;
Ename sal deptno P_RANK MAX_SAL MAX_SAL_25 MAX_SAL_50 MAX_SAL_75
--------------------------------------------------------------------------------
KING 5000 10 0 5000 3725 2450
CLARK 2450 5000 3725 2450 1875
MILLER 1300 10 1 5000 3725 2450
SCOTT 3000 20 0 3000 3000 2975
FORD 3000 20 0 3000 3000 2975
JONES 2975 20. 5 3000 3000 2975 1100
ADAMS 1100 20. 75 3000 3000 2975
SMITH 800 20 1 3000 3000 2975
BLAKE 2850 30 0 2850 1575 1375
ALLEN 1600 30. 2 2850 1575 1375 1250
TURNER 1500 30. 4 2850 1575 1375 1250
WARD 1250 30. 6 2850 1575 1375 1250
MARTIN 1250 30. 6 2850 1575 1375 1250
JAMES 950 30 1 2850 1575 1375 1250
14 rows have been selected.


SQL> select e. ename, e. sal, e. deptno,
Percent_rank () over (partition by deptno order by sal) p_rank,
PERCENTILE_CONT (0) within group (order by sal)
Over (partition by deptno) max_sal,
PERCENTILE_CONT (0.25) within group (order by sal)
Over (partition by deptno) max_sal_25,
PERCENTILE_CONT (0.5) within group (order by sal)
Over (partition by deptno) max_sal_50,
PERCENTILE_CONT (0.75) within group (order by sal)
Over (partition by deptno) max_sal_75
From emp e;
Ename sal deptno P_RANK MAX_SAL MAX_SAL_25 MAX_SAL_50 MAX_SAL_75
--------------------------------------------------------------------------------
MILLER 1300 10 0 1300 1875 2450
CLARK 2450 1300 1875 2450 3725
KING 5000 10 1 1300 1875 2450
SMITH 800 20 0 800 1100 2975
ADAMS 1100 20. 25 800 1100 2975
JONES 2975 20. 5 800 1100 2975 3000
SCOTT 3000 20. 75 800 1100 2975
FORD 3000 800 1100 2975 3000
JAMES 950 30 0 950 1250 1375 1575
MARTIN 1250 30. 2 950 1250 1375 1575
WARD 1250 30. 2 950 1250 1375 1575
TURNER 1500 30. 6 950 1250 1375 1575
ALLEN 1600 30. 8 950 1250 1375 1575
BLAKE 2850 30 1 950 1250 1375 1575
14 rows have been selected.