oracle 百分位数,Oracle分析函数PERCENTILE_CONT

查询各部门中薪水分布处于25%、50%、75%位置的人的薪水，percent_rank()是确定排行中的相对位置。

查询各部门中薪水分布处于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行。 本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：php中文网