[펌글][오라클] first_value, last_value 함수를 사용하여 최고, 최저값 구하기

- 출처 : http://blog.naver.com/gseducation/20095625332 -

* first_value 함수를 이용해서 최저 및 최고 구하기

select empno, ename, sal,
       first_value(sal) over (order by sal) "최저",
       first_value(sal) over (order by sal desc) "최고"
from emp
order by sal;

     EMPNO ENAME             SAL       최저       최고
---------- ---------- ---------- ---------- ----------
      7369 SMITH             800        800       5000
      7900 JAMES             950        800       5000
      7876 ADAMS            1100        800       5000
      7654 MARTIN           1250        800       5000
      7521 WARD             1250        800       5000
      7934 MILLER           1300        800       5000
      7844 TURNER           1500        800       5000
      7499 ALLEN            1600        800       5000
      7782 CLARK            2450        800       5000
      7698 BLAKE            2850        800       5000
      7566 JONES            2975        800       5000
      7788 SCOTT            3000        800       5000
      7902 FORD             3000        800       5000
      7839 KING             5000        800       5000

* last_value 함수를 이용해서 최고 구하기 시도 : 결과가 이상하다. 버그(bug)다.

select empno, ename, sal,
       last_value(sal) over (order by sal) "최고"
from emp
order by sal;

     EMPNO ENAME             SAL       최고
---------- ---------- ---------- ----------
      7369 SMITH             800        800
      7900 JAMES             950        950
      7876 ADAMS            1100       1100
      7521 WARD             1250       1250
      7654 MARTIN           1250       1250
      7934 MILLER           1300       1300
      7844 TURNER           1500       1500
      7499 ALLEN            1600       1600
      7782 CLARK            2450       2450
      7698 BLAKE            2850       2850
      7566 JONES            2975       2975
      7788 SCOTT            3000       3000
      7902 FORD             3000       3000
      7839 KING             5000       5000

Bug number 5684819 on Metalink

"If you omit the windowing_clause of the analytic_clause, it defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This default will return a value that sometimes may seem unexpected, because the last value in the window is at the bottom of the window, which is not fixed. It keeps changing as the current row changes. For expected results, specify the windowing clause as RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Another option, is to specify the windowing clause as RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING."

* last_value 함수를 이용해서 최고 및 최저 구하기

select empno, ename, sal, hiredate,
last_value(sal) over (order by sal range between unbounded preceding and unbounded following) "최고",
last_value(sal) over (order by sal desc range between unbounded preceding and unbounded following) "최저"
from emp;

     EMPNO ENAME             SAL HIREDATE           최고       최저
---------- ---------- ---------- ------------ ---------- ----------
      7839 KING             5000 17-NOV-81          5000        800
      7902 FORD             3000 03-DEC-81          5000        800
      7788 SCOTT            3000 09-DEC-82          5000        800
      7566 JONES            2975 02-APR-81          5000        800
      7698 BLAKE            2850 01-MAY-81          5000        800
      7782 CLARK            2450 09-JUN-81          5000        800
      7499 ALLEN            1600 20-FEB-81          5000        800
      7844 TURNER           1500 08-SEP-81          5000        800
      7934 MILLER           1300 23-JAN-82          5000        800
      7654 MARTIN           1250 28-SEP-81          5000        800
      7521 WARD             1250 22-FEB-81          5000        800
      7876 ADAMS            1100 12-JAN-83          5000        800
      7900 JAMES             950 03-DEC-81          5000        800
      7369 SMITH             800 17-DEC-80          5000        800