|
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000, NULL, 'E')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, 'James',to_date('19781212','YYYYMMDD'), 23000, 32000, 'W')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,'Celia',to_date('19821024','YYYYMMDD'), NULL, 58000, 'E')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000, NULL, 'W')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,'Linda', to_date('19870730','YYYYMMDD'), NULL, 53000, 'E')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,'David', to_date('19901231','YYYYMMDD'), 78000, NULL, 'W')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,'Jode', to_date('19960917','YYYYMMDD'), 21000, 29000, 'E')
3 /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 58000 E
105 Robert 15-JAN-84 31000 W
116 Linda 30-JUL-87 53000 E
117 David 31-DEC-90 78000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> --PERCENT_RANK will compute the cumulative fraction of the ranking that exists for a particular ranking value. This calculation and the one for CUME_DIST are like the values one would see in a histogram. PERCENT_RANK is set to compute so that the first row is zero, and the other values in this
column are computed based on the formula:
SQL>
SQL> --Percent_rank (PR) = (Rank-1)/(Number of rows-1)
SQL>
SQL> --For example, using our Employee table with PERCENT_RANK and CUME_DIST:
SQL>
SQL> SELECT empno, ename, region,
2 RANK() OVER(PARTITION BY region ORDER BY curr_salary)
3 RANK,
4 PERCENT_RANK() OVER(PARTITION BY region ORDER BY
5 curr_salary) PR,
6 CUME_DIST() OVER(PARTITION BY region ORDER BY curr_salary)
7 CD
8 FROM employee;
EMPNO ENAME R RANK PR CD
---------- ---------- - ---------- ---------- ----------
108 Jode E 1 0 .25
116 Linda E 2 .333333333 .5
104 Celia E 3 .666666667 .75
122 Alison E 4 1 1
123 James W 1 0 .333333333
117 David W 2 .5 1
105 Robert W 2 .5 1
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
|