[20190728]分析函式LAST_VALUE.txt

[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語句就不會寫的這麼糟糕!!