Oracle分析函数总结(2) - 排序 - rank,dense_rank,row_number,first,first_value,last,last_value,lag,lead

最新推荐文章于 2023-10-26 13:27:22 发布

fw0124

最新推荐文章于 2023-10-26 13:27:22 发布

阅读量5.8k

点赞数 1

分类专栏： Oracle 文章标签： oracle function null c

本文链接：https://blog.csdn.net/fw0124/article/details/7843225

版权

Oracle 专栏收录该内容

67 篇文章 2 订阅

订阅专栏

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

fw0124

关注

1
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
Oracle分析函数总结(2) - 排序 - rank,dense_rank,row_number,first,first_value,last,last_value,lag,lead

分析函数的基本概念和语法 -> http://blog.csdn.net/fw0124/article/details/78420391) rank(),dense_rank(),row_number()这几个函数区别是：a)rank()是跳跃排序,有两个第1名时接下来就是第3名;b)dense_rank()是连续排序,有两个第1名时接下来仍然跟着第2名;c)row_n
复制链接

扫一扫

专栏目录