oracle之percent_rank() over()函数和PERCENTILE_CONT() within group()over()函数详解

相关文章推荐

霸气的西装 · 武汉市人民政府门户网站· 1 月前 ·

飘逸的小蝌蚪 · 关于开展全省学生“学宪法 ...· 1 月前 ·

细心的汤圆 · 《学爸》百度云资源1080p免费观看完整版已 ...· 5 月前 ·

纯真的足球 · 常州市天宁区人民政府· 1 年前 ·

沉稳的火车 · 成立100周年，宝马推出了一款能代表未来的概 ...· 1 年前 ·

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;
select *  from emp e; 
按薪水的倒序，取薪水的排名，及一定百分比的薪水值
select e.ename,e.sal,e.deptno,
       percent_rank() over(partition by deptno order by sal desc) p_rank, --以deptno分组，以sal倒序，取出百分比排名
       PERCENTILE_CONT(0) within group(order by sal desc)
        over(partition by deptno) max_sal ,                               --以deptno分组，以sal倒序，取出占0%的薪水
       PERCENTILE_CONT(0.25) within group(order by sal desc)
        over(partition by deptno) max_sal_25,                             --以deptno分组，以sal倒序，取出25%的薪水
       PERCENTILE_CONT(0.5) within group(order by sal desc)
        over(partition by deptno) max_sal_50,                             --以deptno分组，以sal倒序，取出50%的薪水
       PERCENTILE_CONT(0.75) within group(order by sal desc)              --以deptno分组，以sal倒序，取出70%的薪水
        over(partition by deptno) max_sal_75                              
   from emp e;
按薪水的正序，取薪水的排名，及一定百分比的薪水值
以上即全部Oracle分析函数PERCENTILE_CONT解析。