目录
1.带空值的排列
2.Top/Bottom N查询
3.First/Last排名查询
4.按层次查询
一、带空值的排列
解决排列的数据中含有空值的。NULL为最大值,这是我们不想见到的!
SQL> select deptno,ename,sum(sal) sal_sum,
sum(sum(sal)) over (partition by deptno ) total,
rank () over (partition by deptno order by sum(sal) desc) rank
from emp group by deptno,ename;
DEPTNO ENAME SAL_SUM TOTAL RANK
------ ---------- ---------- ---------- ----------
10 KING 5000 8564.59 1
10 CLARK 2450 8564.59 2
10 MILLER 1114.59 8564.59 3
20 SCOTT 3000 9953.8 1
20 FORD 3000 9953.8 1
20 JONES 2053.8 9953.8 3
20 ADAMS 1100 9953.8 4
20 SMITH 800 9953.8 5
30 JAMES 8450 1
30 BLAKE 2850 8450 2
30 ALLEN 1600 8450 3
30 TURNER 1500 8450 4
30 WARD 1250 8450 5
30 MARTIN 1250 8450 5
14 rows selected
下面修改后:
SQL> select deptno,ename,sum(sal) sal_sum,
sum(sum(sal)) over (partition by deptno ) total,
rank () over (partition by deptno order by sum(sal) desc NULLS LAST ) rank
from emp group by deptno,ename;
DEPTNO ENAME SAL_SUM TOTAL RANK
------ ---------- ---------- ---------- ----------
10 KING 5000 8564.59 1
10 CLARK 2450 8564.59 2
10 MILLER 1114.59 8564.59 3
20 SCOTT 3000 9953.8 1
20 FORD 3000 9953.8 1
20 JONES 2053.8 9953.8 3
20 ADAMS 1100 9953.8 4
20 SMITH 800 9953.8 5
30 BLAKE 2850 8450 1
30 ALLEN 1600 8450 2
30 TURNER 1500 8450 3
30 WARD 1250 8450 4
30 MARTIN 1250 8450 4
30 JAMES 8450 6
14 rows selected
NULLS LAST/FIRST告诉Oracle让空值排名最后后第一。
注意是NULLS,不是NULL。
二、Top/Bottom N查询:
在日常生活中我们经常会遇到这样的查询,找出工资前五名的人员。
SQL> select t.*, rownum from (select * from emp order by sal desc) t where rownum<5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROWNUM
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7900 JAMES CLERK 7698 1981-12-03 30 1
7839 KING PRESIDENT 1981-11-17 5000.00 10 2
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20 3
7902 FORD ANALYST 7566 1981-12-03 3000.00 20 4
以上为子查询对sal进行排序,SAL空值用前面的方法解决
SQL> select t.*, rownum from (select * from emp order by sal desc NULLS LAST) t where rownum<5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROWNUM
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7839 KING PRESIDENT 1981-11-17 5000.00 10 1
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20 2
7902 FORD ANALYST 7566 1981-12-03 3000.00 20 3
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30 4
以上为前4名的排列结果
使用分析函数操作如下:
SQL> select * from (select t.*,rank() over (order by sal desc NULLS LAST) rank from emp t) a where rownum<5 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RANK
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
7839 KING PRESIDENT 1981-11-17 5000.00 10 1
7902 FORD ANALYST 7566 1981-12-03 3000.00 20 2
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20 2
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30 4
以上为为分析函数操作结果
三、First/Last排名查询:
找出按部门分组工资总计,最高工资是多少,与最低工资是多少.
SQL> select deptno, sum(sal),
min(sal) keep(dense_rank first order by sal desc nulls last ) first,
min(sal) keep(dense_rank last order by sal desc ) last
from emp group by deptno order by deptno;
DEPTNO SUM(SAL) FIRST LAST
------ ---------- ---------- ----------
10 8564.59 5000 1114.59
20 9953.8 3000 800
30 8450 2850 1250
SQL> select max(sal),min(sal) from emp;
MAX(SAL) MIN(SAL)
---------- ----------
5000 800
这里有几个看起来比较疑惑的地方:
①为什么这里要用min函数
②Keep这个东西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同,能换成rank吗?
首先解答一下第一个问题:min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢?
select deptno, sum(sal),
keep(dense_rank first order by sal desc nulls last ) first,
keep(dense_rank last order by sal desc ) last
from emp group by deptno order by deptno
keep(dense_rank first order by sal desc nulls last ) first,
*
ORA-00907: missing right parenthesis
接下来看看第2个问题:keep是干什么用的?从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据,这就是keep的作用。告诉Oracle只保留符合keep条件的记录。
那么什么才是符合条件的记录呢?这就是第3个问题了。dense_rank是告诉Oracle排列的策略,first/last则告诉最终筛选的条件。
第4个问题:如果我们把dense_rank换成rank呢?
select deptno, sum(sal),
max(sal) keep(rank first order by sal desc nulls last ) first,
max(sal) keep(rank last order by sal desc ) last
from emp group by deptno order by deptno
max(sal) keep(rank first order by sal desc nulls last ) first,
*
ORA-02000: missing DENSE_RANK keyword
四、按层次查询:
统计出工资前1/5之的人员的名单.使用NTILE分析函数,把所有工资分为5份,为1的哪一份就是我们想要的结果:
SQL> select empno,ename,sum(sal),
ntile(5) over (order by sum(sal) desc nulls last) til
from emp
group by empno,ename;
EMPNO ENAME SUM(SAL) TIL
----- ---------- ---------- ----------
7839 KING 5000 1
7788 SCOTT 3000 1
7902 FORD 3000 1
7698 BLAKE 2850 2
7782 CLARK 2450 2
7566 JONES 2053.8 2
7499 ALLEN 1600 3
7844 TURNER 1500 3
7521 WARD 1250 3
7654 MARTIN 1250 4
7934 MILLER 1114.59 4
7876 ADAMS 1100 4
7369 SMITH 800 5
7900 JAMES 5
14 rows selected
如果需要找出前25%的用户资料,哪就只需要NTILE(4)就可以了!