[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语句就不会写的这么糟糕!!