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

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          6 14 rows selected.

2) first(), last()
first,last需要和dense_rank结合使用,返回排在第一和最后的记录(集合)。语法：
aggregate_function KEEP (dense_rank first|last order by ...) [over([partition_clause])]

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

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                JAMES 14 rows selected.

3) first_value(), last_value()
first_value()和last_value()返回数据集合中的第一个值和最后一个值。

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                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 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                JAMES 14 rows selected.

lag()返回指定行之前的某个偏移位置的数据。

lag(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       1500 14 rows selected.
                    <li class="tool-item tool-active is-like "><a href="javascript:;"><svg class="icon" aria-hidden="true">
</svg><span class="name">点赞</span>
<span class="count">4</span>
</a></li>
<li class="tool-item tool-active is-collection "><a href="javascript:;" data-report-click="{&quot;mod&quot;:&quot;popu_824&quot;}"><svg class="icon" aria-hidden="true">
</svg><span class="name">收藏</span></a></li>
<li class="tool-item tool-active is-share"><a href="javascript:;"><svg class="icon" aria-hidden="true">
</svg>分享</a></li>
<!--打赏开始-->
<!--打赏结束-->
<li class="tool-item tool-more">
<a>
<svg t="1575545411852" class="icon" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5717" xmlns:xlink="http://www.w3.org/1999/xlink" width="200" height="200"><defs><style type="text/css"></style></defs><path d="M179.176 499.222m-113.245 0a113.245 113.245 0 1 0 226.49 0 113.245 113.245 0 1 0-226.49 0Z" p-id="5718"></path><path d="M509.684 499.222m-113.245 0a113.245 113.245 0 1 0 226.49 0 113.245 113.245 0 1 0-226.49 0Z" p-id="5719"></path><path d="M846.175 499.222m-113.245 0a113.245 113.245 0 1 0 226.49 0 113.245 113.245 0 1 0-226.49 0Z" p-id="5720"></path></svg>
</a>
<ul class="more-box">
<li class="item"><a class="article-report">文章举报</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div class="person-messagebox">
<div class="left-message"><a href="https://blog.csdn.net/fw0124">
<img src="https://g.csdnimg.cn/static/user-reg-year/1x/10.png" class="user-years">
</a></div>
<div class="middle-message">
<div class="title"><span class="tit"><a href="https://blog.csdn.net/fw0124" data-report-click="{&quot;mod&quot;:&quot;popu_379&quot;}" target="_blank">fw0124</a></span>
</div>
<div class="text"><span>发布了115 篇原创文章</span> · <span>获赞 125</span> · <span>访问量 176万+</span></div>
</div>
<div class="right-message">
<a href="https://im.csdn.net/im/main.html?userName=fw0124" target="_blank" class="btn btn-sm btn-red-hollow bt-button personal-letter">私信
</a>
<a class="btn btn-sm  bt-button personal-watch" data-report-click="{&quot;mod&quot;:&quot;popu_379&quot;}">关注</a>
</div>
</div>
</div>
</article>


©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客