Oracle分析函数总结(2) - 排序 - rank,dense_rank,row_number,first,first_value,last,last_value,lag,lead
分析函数的基本概念和语法 -> http://blog.csdn.net/fw0124/article/details/7842039
1) rank(),dense_rank(),row_number()
这几个函数区别是：
a)rank()是跳跃排序,有两个第1名时接下来就是第3名;
b)dense_rank()是连续排序,有两个第1名时接下来仍然跟着第2名;
c)row_number()是连续排序,并且有并列名次时,按照记录集中记录的顺序名次依次递增。
tony@ORCL1> select ename, sal, deptno, 2 rank() over(partition by deptno order by sal) rank, 3 dense_rank() over(partition by deptno order by sal) dense_rank, 4 row_number() over(partition by deptno order by sal) row_number 5 from emp;ENAME SAL DEPTNO RANK DENSE_RANK ROW_NUMBER---------- ---------- ---------- ---------- ---------- ----------MILLER $1300.00 10 1 1 1CLARK $2450.00 10 2 2 2KING $5000.00 10 3 3 3SMITH $800.00 20 1 1 1ADAMS $1100.00 20 2 2 2JONES $2975.00 20 3 3 3SCOTT $3000.00 20 4 4 4FORD $3000.00 20 4 4 5JAMES $950.00 30 1 1 1MARTIN $1250.00 30 2 2 2WARD $1250.00 30 2 2 3TURNER $1500.00 30 4 3 4ALLEN $1600.00 30 5 4 5BLAKE $2850.00 30 6 5 614 rows selected.
2) first(), last()
first,last需要和dense_rank结合使用,返回排在第一和最后的记录(集合)。语法：
aggregate_function KEEP (dense_rank first|last order by ...) [over([partition_clause])]
例如，查询各部门薪水最高和最低的人名。不带over子句, 作为聚合函数使用。
tony@ORCL1> column first format a20tony@ORCL1> column last format a20tony@ORCL1> select deptno, 2 wm_concat(ename) keep (dense_rank first order by sal desc) first, 3 wm_concat(ename) keep (dense_rank last order by sal desc) last 4 from emp group by deptno; DEPTNO FIRST LAST---------- -------------------- -------------------- 10 KING MILLER 20 SCOTT,FORD SMITH 30 BLAKE JAMES
查询各部门薪水最高和最低的人名。带over子句, 作为分析函数使用。
tony@ORCL1> select ename, sal, deptno, 2 wm_concat(ename) keep (dense_rank first order by sal desc) 3 over(partition by deptno) first, 4 wm_concat(ename) keep (dense_rank last order by sal desc) 5 over(partition by deptno) last 6 from emp;ENAME SAL DEPTNO FIRST LAST---------- ---------- ---------- -------------------- ---------CLARK $2450.00 10 KING MILLERKING $5000.00 10 KING MILLERMILLER $1300.00 10 KING MILLERJONES $2975.00 20 FORD,SCOTT SMITHFORD $3000.00 20 FORD,SCOTT SMITHADAMS $1100.00 20 FORD,SCOTT SMITHSMITH $800.00 20 FORD,SCOTT SMITHSCOTT $3000.00 20 FORD,SCOTT SMITHWARD $1250.00 30 BLAKE JAMESTURNER $1500.00 30 BLAKE JAMESALLEN $1600.00 30 BLAKE JAMESJAMES $950.00 30 BLAKE JAMESBLAKE $2850.00 30 BLAKE JAMESMARTIN $1250.00 30 BLAKE JAMES14 rows selected.
3) first_value(), last_value()
first_value()和last_value()返回数据集合中的第一个值和最后一个值。
和first(),last()区别是仅仅返回1个值。
下面的例子，并不能得到期待结果。
原因是没有指定开窗子句，
order by会加上一个默认窗口RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
tony@ORCL1> select ename, sal, deptno, 2 first_value(ename) over (partition by deptno order by sal desc) first, 3 last_value(ename) over (partition by deptno order by sal desc) last 4 from emp;ENAME SAL DEPTNO FIRST LAST---------- ---------- ---------- -------------------- --------------------KING $5000.00 10 KING KINGCLARK $2450.00 10 KING CLARKMILLER $1300.00 10 KING MILLERFORD $3000.00 20 FORD SCOTTSCOTT $3000.00 20 FORD SCOTTJONES $2975.00 20 FORD JONESADAMS $1100.00 20 FORD ADAMSSMITH $800.00 20 FORD SMITHBLAKE $2850.00 30 BLAKE BLAKEALLEN $1600.00 30 BLAKE ALLENTURNER $1500.00 30 BLAKE TURNERMARTIN $1250.00 30 BLAKE WARDWARD $1250.00 30 BLAKE WARDJAMES $950.00 30 BLAKE JAMES14 rows selected.
加上开窗子句，指定窗口为所有行，就可以得到期待结果。
tony@ORCL1> select ename, sal, deptno, 2 first_value(ename) over (partition by deptno order by sal desc 3 rows between unbounded preceding and unbounded following) first, 4 last_value(ename) over (partition by deptno order by sal desc 5 rows between unbounded preceding and unbounded following) last 6 from emp;ENAME SAL DEPTNO FIRST LAST---------- ---------- ---------- -------------------- --------------------KING $5000.00 10 KING MILLERCLARK $2450.00 10 KING MILLERMILLER $1300.00 10 KING MILLERSCOTT $3000.00 20 SCOTT SMITHFORD $3000.00 20 SCOTT SMITHJONES $2975.00 20 SCOTT SMITHADAMS $1100.00 20 SCOTT SMITHSMITH $800.00 20 SCOTT SMITHBLAKE $2850.00 30 BLAKE JAMESALLEN $1600.00 30 BLAKE JAMESTURNER $1500.00 30 BLAKE JAMESMARTIN $1250.00 30 BLAKE JAMESWARD $1250.00 30 BLAKE JAMESJAMES $950.00 30 BLAKE JAMES14 rows selected.
4) lag(), lead()
lag()返回指定行之前的某个偏移位置的数据。
lead()和lag()相反，返回的是指定行之后的某个偏移位置的数据。
语法:
lag(value_expr[,offset[,default]]) over ([partition_clause] order_by_clause)
lead(value_expr[,offset[,default]]) over ([partition_clause] order_by_clause)
a) value_expr是想要返回的数据列；
b) offset是偏移位置，如果省略默认为1；
c) default是当偏移位置超出了组内的数据窗口的时候的返回值，如果省略默认为null。
tony@ORCL1> select ename, sal, deptno, 2 lag(sal, 2) over(partition by deptno order by sal) lag, 3 lead(sal, 2) over(partition by deptno order by sal) lead 4 from emp;ENAME SAL DEPTNO LAG LEAD---------- ---------- ---------- ---------- ----------MILLER $1300.00 10 5000CLARK $2450.00 10KING $5000.00 10 1300SMITH $800.00 20 2975ADAMS $1100.00 20 3000JONES $2975.00 20 800 3000SCOTT $3000.00 20 1100FORD $3000.00 20 2975JAMES $950.00 30 1250MARTIN $1250.00 30 1500WARD $1250.00 30 950 1600TURNER $1500.00 30 1250 2850ALLEN $1600.00 30 1250BLAKE $2850.00 30 150014 rows selected.
