oracle分析函数中排序,Oracle分析函数小结(2) - 排序 - rank,dense_rank,row_number,first,first_value,last,last_value,la...

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 1

CLARK $2450.00 10 2 2 2

KING $5000.00 10 3 3 3

SMITH $800.00 20 1 1 1

ADAMS $1100.00 20 2 2 2

JONES $2975.00 20 3 3 3

SCOTT $3000.00 20 4 4 4

FORD $3000.00 20 4 4 5

JAMES $950.00 30 1 1 1

MARTIN $1250.00 30 2 2 2

WARD $1250.00 30 2 2 3

TURNER $1500.00 30 4 3 4

ALLEN $1600.00 30 5 4 5

BLAKE $2850.00 30 6 5 6

14 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 a20

tony@ORCL1> column last format a20

tony@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 MILLER

KING $5000.00 10 KING MILLER

MILLER $1300.00 10 KING MILLER

JONES $2975.00 20 FORD,SCOTT SMITH

FORD $3000.00 20 FORD,SCOTT SMITH

ADAMS $1100.00 20 FORD,SCOTT SMITH

SMITH $800.00 20 FORD,SCOTT SMITH

SCOTT $3000.00 20 FORD,SCOTT SMITH

WARD $1250.00 30 BLAKE JAMES

TURNER $1500.00 30 BLAKE JAMES

ALLEN $1600.00 30 BLAKE JAMES

JAMES $950.00 30 BLAKE JAMES

BLAKE $2850.00 30 BLAKE JAMES

MARTIN $1250.00 30 BLAKE JAMES

14 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 KING

CLARK $2450.00 10 KING CLARK

MILLER $1300.00 10 KING MILLER

FORD $3000.00 20 FORD SCOTT

SCOTT $3000.00 20 FORD SCOTT

JONES $2975.00 20 FORD JONES

ADAMS $1100.00 20 FORD ADAMS

SMITH $800.00 20 FORD SMITH

BLAKE $2850.00 30 BLAKE BLAKE

ALLEN $1600.00 30 BLAKE ALLEN

TURNER $1500.00 30 BLAKE TURNER

MARTIN $1250.00 30 BLAKE WARD

WARD $1250.00 30 BLAKE WARD

JAMES $950.00 30 BLAKE JAMES

14 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 MILLER

CLARK $2450.00 10 KING MILLER

MILLER $1300.00 10 KING MILLER

SCOTT $3000.00 20 SCOTT SMITH

FORD $3000.00 20 SCOTT SMITH

JONES $2975.00 20 SCOTT SMITH

ADAMS $1100.00 20 SCOTT SMITH

SMITH $800.00 20 SCOTT SMITH

BLAKE $2850.00 30 BLAKE JAMES

ALLEN $1600.00 30 BLAKE JAMES

TURNER $1500.00 30 BLAKE JAMES

MARTIN $1250.00 30 BLAKE JAMES

WARD $1250.00 30 BLAKE JAMES

JAMES $950.00 30 BLAKE JAMES

14 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 5000

CLARK $2450.00 10

KING $5000.00 10 1300

SMITH $800.00 20 2975

ADAMS $1100.00 20 3000

JONES $2975.00 20 800 3000

SCOTT $3000.00 20 1100

FORD $3000.00 20 2975

JAMES $950.00 30 1250

MARTIN $1250.00 30 1500

WARD $1250.00 30 950 1600

TURNER $1500.00 30 1250 2850

ALLEN $1600.00 30 1250

BLAKE $2850.00 30 1500

14 rows selected.