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()是连续排序,并且有并列名次时,按照记录集中记录的顺序名次依次递增。

  1. tony@ORCL1> select ename, sal, deptno,
  2. 2 rank() over(partition by deptno order by sal) rank,
  3. 3 dense_rank() over(partition by deptno order by sal) dense_rank,
  4. 4 row_number() over(partition by deptno order by sal) row_number
  5. 5 from emp;
  6. ENAME SAL DEPTNO RANK DENSE_RANK ROW_NUMBER
  7. ---------- ---------- ---------- ---------- ---------- ----------
  8. MILLER $1300.00 10 1 1 1
  9. CLARK $2450.00 10 2 2 2
  10. KING $5000.00 10 3 3 3
  11. SMITH $800.00 20 1 1 1
  12. ADAMS $1100.00 20 2 2 2
  13. JONES $2975.00 20 3 3 3
  14. SCOTT $3000.00 20 4 4 4
  15. FORD $3000.00 20 4 4 5
  16. JAMES $950.00 30 1 1 1
  17. MARTIN $1250.00 30 2 2 2
  18. WARD $1250.00 30 2 2 3
  19. TURNER $1500.00 30 4 3 4
  20. ALLEN $1600.00 30 5 4 5
  21. BLAKE $2850.00 30 6 5 6
  22. 14 rows selected.


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

例如,查询各部门薪水最高和最低的人名。不带over子句, 作为聚合函数使用。

  1. tony@ORCL1> column first format a20
  2. tony@ORCL1> column last format a20
  3. tony@ORCL1> select deptno,
  4. 2 wm_concat(ename) keep (dense_rank first order by sal desc) first,
  5. 3 wm_concat(ename) keep (dense_rank last order by sal desc) last
  6. 4 from emp group by deptno;
  7. DEPTNO FIRST LAST
  8. ---------- -------------------- --------------------
  9. 10 KING MILLER
  10. 20 SCOTT,FORD SMITH
  11. 30 BLAKE JAMES


查询各部门薪水最高和最低的人名。带over子句, 作为分析函数使用。

  1. tony@ORCL1> select ename, sal, deptno,
  2. 2 wm_concat(ename) keep (dense_rank first order by sal desc)
  3. 3 over(partition by deptno) first,
  4. 4 wm_concat(ename) keep (dense_rank last order by sal desc)
  5. 5 over(partition by deptno) last
  6. 6 from emp;
  7. ENAME SAL DEPTNO FIRST LAST
  8. ---------- ---------- ---------- -------------------- ---------
  9. CLARK $2450.00 10 KING MILLER
  10. KING $5000.00 10 KING MILLER
  11. MILLER $1300.00 10 KING MILLER
  12. JONES $2975.00 20 FORD,SCOTT SMITH
  13. FORD $3000.00 20 FORD,SCOTT SMITH
  14. ADAMS $1100.00 20 FORD,SCOTT SMITH
  15. SMITH $800.00 20 FORD,SCOTT SMITH
  16. SCOTT $3000.00 20 FORD,SCOTT SMITH
  17. WARD $1250.00 30 BLAKE JAMES
  18. TURNER $1500.00 30 BLAKE JAMES
  19. ALLEN $1600.00 30 BLAKE JAMES
  20. JAMES $950.00 30 BLAKE JAMES
  21. BLAKE $2850.00 30 BLAKE JAMES
  22. MARTIN $1250.00 30 BLAKE JAMES
  23. 14 rows selected.


3) first_value(), last_value()
first_value()和last_value()返回数据集合中的第一个值和最后一个值。
和first(),last()区别是仅仅返回1个值。

下面的例子,并不能得到期待结果。
原因是没有指定开窗子句,
order by会加上一个默认窗口RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  1. tony@ORCL1> select ename, sal, deptno,
  2. 2 first_value(ename) over (partition by deptno order by sal desc) first,
  3. 3 last_value(ename) over (partition by deptno order by sal desc) last
  4. 4 from emp;
  5. ENAME SAL DEPTNO FIRST LAST
  6. ---------- ---------- ---------- -------------------- --------------------
  7. KING $5000.00 10 KING KING
  8. CLARK $2450.00 10 KING CLARK
  9. MILLER $1300.00 10 KING MILLER
  10. FORD $3000.00 20 FORD SCOTT
  11. SCOTT $3000.00 20 FORD SCOTT
  12. JONES $2975.00 20 FORD JONES
  13. ADAMS $1100.00 20 FORD ADAMS
  14. SMITH $800.00 20 FORD SMITH
  15. BLAKE $2850.00 30 BLAKE BLAKE
  16. ALLEN $1600.00 30 BLAKE ALLEN
  17. TURNER $1500.00 30 BLAKE TURNER
  18. MARTIN $1250.00 30 BLAKE WARD
  19. WARD $1250.00 30 BLAKE WARD
  20. JAMES $950.00 30 BLAKE JAMES
  21. 14 rows selected.


加上开窗子句,指定窗口为所有行,就可以得到期待结果。

  1. tony@ORCL1> select ename, sal, deptno,
  2. 2 first_value(ename) over (partition by deptno order by sal desc
  3. 3 rows between unbounded preceding and unbounded following) first,
  4. 4 last_value(ename) over (partition by deptno order by sal desc
  5. 5 rows between unbounded preceding and unbounded following) last
  6. 6 from emp;
  7. ENAME SAL DEPTNO FIRST LAST
  8. ---------- ---------- ---------- -------------------- --------------------
  9. KING $5000.00 10 KING MILLER
  10. CLARK $2450.00 10 KING MILLER
  11. MILLER $1300.00 10 KING MILLER
  12. SCOTT $3000.00 20 SCOTT SMITH
  13. FORD $3000.00 20 SCOTT SMITH
  14. JONES $2975.00 20 SCOTT SMITH
  15. ADAMS $1100.00 20 SCOTT SMITH
  16. SMITH $800.00 20 SCOTT SMITH
  17. BLAKE $2850.00 30 BLAKE JAMES
  18. ALLEN $1600.00 30 BLAKE JAMES
  19. TURNER $1500.00 30 BLAKE JAMES
  20. MARTIN $1250.00 30 BLAKE JAMES
  21. WARD $1250.00 30 BLAKE JAMES
  22. JAMES $950.00 30 BLAKE JAMES
  23. 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。

  1. tony@ORCL1> select ename, sal, deptno,
  2. 2 lag(sal, 2) over(partition by deptno order by sal) lag,
  3. 3 lead(sal, 2) over(partition by deptno order by sal) lead
  4. 4 from emp;
  5. ENAME SAL DEPTNO LAG LEAD
  6. ---------- ---------- ---------- ---------- ----------
  7. MILLER $1300.00 10 5000
  8. CLARK $2450.00 10
  9. KING $5000.00 10 1300
  10. SMITH $800.00 20 2975
  11. ADAMS $1100.00 20 3000
  12. JONES $2975.00 20 800 3000
  13. SCOTT $3000.00 20 1100
  14. FORD $3000.00 20 2975
  15. JAMES $950.00 30 1250
  16. MARTIN $1250.00 30 1500
  17. WARD $1250.00 30 950 1600
  18. TURNER $1500.00 30 1250 2850
  19. ALLEN $1600.00 30 1250
  20. BLAKE $2850.00 30 1500
  21. 14 rows selected.
                        <li class="tool-item tool-active is-like "><a href="javascript:;"><svg class="icon" aria-hidden="true">
                            <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#csdnc-thumbsup"></use>
                        </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">
                            <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#icon-csdnc-Collection-G"></use>
                        </svg><span class="name">收藏</span></a></li>
                        <li class="tool-item tool-active is-share"><a href="javascript:;"><svg class="icon" aria-hidden="true">
                            <use xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="#icon-csdnc-fenxiang"></use>
                        </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://profile.csdnimg.cn/6/3/B/3_fw0124" class="avatar_pic" username="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>
    
发布了5 篇原创文章 · 获赞 0 · 访问量 1万+
展开阅读全文

没有更多推荐了,返回首页

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

分享到微信朋友圈

×

扫一扫,手机浏览