Oracle分析函數PERCENTILE_CONT，percentile函數

Oracle分析函數PERCENTILE_CONT，percentile函數

查詢各部門中薪水分布處於25%、50%、75%位置的人的薪水，percent_rank()是確定排行中的相對位置。

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', 'CLERK', 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, 'BLAKE', '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', 'CLERK', 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', 'CLERK', 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', 'CLERK', 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       1875
CLARK            2450         10         .5       5000       3725       2450       1875
MILLER           1300         10          1       5000       3725       2450       1875
SCOTT            3000         20          0       3000       3000       2975       1100
FORD             3000         20          0       3000       3000       2975       1100
JONES            2975         20         .5       3000       3000       2975       1100
ADAMS            1100         20        .75       3000       3000       2975       1100
SMITH             800         20          1       3000       3000       2975       1100
BLAKE            2850         30          0       2850       1575       1375       1250
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行。


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       3725
CLARK            2450         10         .5       1300       1875       2450       3725
KING             5000         10          1       1300       1875       2450       3725
SMITH             800         20          0        800       1100       2975       3000
ADAMS            1100         20        .25        800       1100       2975       3000
JONES            2975         20         .5        800       1100       2975       3000
SCOTT            3000         20        .75        800       1100       2975       3000
FORD             3000         20        .75        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行。