[20190728]分析函数LAST_VALUE.txt
--//如果开发善用oracle分析函数，能大大减少编程工作量。可以我发现许多开发根本不学甚至不知道有这样的东西，
--//把语句写的异常复杂。分析函数LAST_VALUE用于取后值，实际使用中遇到一些问题，做1个记录：
1.环境：
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.测试：
SCOTT@test01p> select emp.*,FIRST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) FIRST_SAL FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO FIRST_SAL
----- ------ --------- ---- ------------------- ---- ---- ------ ----------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 1300
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 1300
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 1300
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 800
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 800
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 800
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 800
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 800
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 950
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 950
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 950
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 950
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 950
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 950
14 rows selected.
SCOTT@test01p> select emp.*,last_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) last_SAL FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO LAST_SAL
----- ------ --------- ---- ------------------- ---- ---- ------ --------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 1300
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 2450
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 5000
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 800
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 1100
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 2975
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 3000
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 3000
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 950
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 1250
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 1250
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 1500
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 1600
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 2850
14 rows selected.
--//可以发现last_VALUE取到的值基本是当前窗口集中的大值，这样就不会是PARTITION BY DEPTNO的大sal。
--//如果改写如下OK：
SCOTT@test01p> select emp.*,max(SAL) OVER (PARTITION BY DEPTNO ) last_SAL FROM EMP order by deptno ,sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO LAST_SAL
----- ------ --------- ---- ------------------- ---- ---- ------ --------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 5000
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 5000
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 5000
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 3000
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 3000
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 3000
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 3000
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 3000
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 2850
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 2850
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 2850
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 2850
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 2850
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 2850
14 rows selected.
--//如果order by deptno ,sal仅仅影响排序输出。
--//实际上问题出在分区window的范围，实际上缺省是BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
--//这样一定要输出分区的大sal，改变分区window的范围为ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
SCOTT@test01p> select emp.*,LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAST_SAL FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO LAST_SAL
----- ------ --------- ---- ------------------- ---- ---- ------ --------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 5000
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 5000
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 5000
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 3000
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 3000
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 3000
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 3000
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 3000
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 2850
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 2850
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 2850
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 2850
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 2850
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 2850
14 rows selected.
--//其实对于分析函数我自己也不熟悉，工作中也很少用到。现在通过网络查找资料也非常容易，只要开发心中有这个意识，
--//许多sql语句就不会写的这么糟糕!!
[20190728]分析函数LAST_VALUE.txt
分享好友
分享这个小栈给你的朋友们，一起进步吧。
订阅须知
• 所有用户可根据关注领域订阅专区或所有专区
• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询
• 专区发布评论属默认订阅所评论专区（除付费小栈外）
技术专家
查看更多- 栈栈专家