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