oracle数据库的percentile,Oracle分析函数PERCENTILE_CONT

查询各部门中薪水分布处于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行。