分析函数的基本概念和语法 -> 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个值。<