-- BETWEEN 사용 타입
---------------------------------------------------------------------------
ROWS | RANGE BETWEEN
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
AND
UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
-- BETWEEN 미사용 타입
---------------------------------------------------------------------------
ROW | RANGE
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING
* ROWS 는 물리적인 결과 행의 수, RANGE 는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
-- 예제
---------------------------------------------------------------------------
SELECT E.*
, FIRST_VALUE(SAL) OVER (ORDER BY EMPNO) AS FV1
, FIRST_VALUE(SAL) OVER (ORDER BY EMPNO ROWS UNBOUNDED PRECEDING) AS FV2
, FIRST_VALUE(SAL) OVER (ORDER BY EMPNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FV3
, FIRST_VALUE(SAL) OVER (ORDER BY EMPNO ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS FV4
, LAST_VALUE(SAL) OVER (ORDER BY EMPNO) AS LV1
, LAST_VALUE(SAL) OVER (ORDER BY EMPNO ROWS CURRENT ROW) AS LV2
, LAST_VALUE(SAL) OVER (ORDER BY EMPNO ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LV3
, LAST_VALUE(SAL) OVER (ORDER BY EMPNO ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LV4
, NVL(COMM, LAG(COMM) IGNORE NULLS OVER (ORDER BY EMPNO)) AS COM1
, LAST_VALUE(COMM) IGNORE NULLS OVER (ORDER BY EMPNO) AS COM2
, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS SIM_CNT
, TO_CHAR(SAL - 50) || '->' || TO_CHAR(SAL + 150) AS RANGE
FROM EMP E;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO FV1 FV2 FV3 FV4 LV1 LV2 LV3 LV4 COM1 COM2 SIM_CNT RANGE
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
7369 SMITH CLERK 7902 80/12/17 800 20 1100 1100 1100 1100 800 800 1300 1300 2 750->950
7900 JAMES CLERK 7698 81/12/03 950 30 1100 1100 1100 1500 950 950 1300 1300 0 0 2 900->1100
1111 PARK DEVELOPER 7782 15/12/11 1100 10 1100 1100 1100 1100 1100 1100 1300 1300 3 1050->1250
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 1100 1100 1100 1600 1250 1250 1300 1300 500 500 3 1200->1400
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 1100 1100 1100 2975 1250 1250 1300 1300 1400 1400 3 1200->1400
7934 MILLER CLERK 7782 82/01/23 1300 10 1100 1100 1100 3000 1300 1300 1300 1300 0 0 3 1250->1450
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 1100 1100 1100 5000 1500 1500 1300 1300 0 0 2 1450->1650
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 1100 1100 1100 800 1600 1600 1300 1300 300 300 1 1550->1750
7782 CLARK MANAGER 7839 81/06/09 2450 10 1100 1100 1100 2850 2450 2450 1300 1300 1400 1400 1 2400->2600
7698 BLAKE MANAGER 7839 81/05/01 2850 30 1100 1100 1100 1250 2850 2850 1300 1300 1400 1400 3 2800->3000
7566 JONES MANAGER 7839 81/04/02 2975 20 1100 1100 1100 1250 2975 2975 1300 1300 500 500 2 2925->3125
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO FV1 FV2 FV3 FV4 LV1 LV2 LV3 LV4 COM1 COM2 SIM_CNT RANGE
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
7902 FORD ANALYST 7566 81/12/03 3000 20 1100 1100 1100 950 3000 3000 1300 1300 0 0 2 2950->3150
7839 KING PRESIDENT 81/11/17 5000 10 1100 1100 1100 2450 5000 5000 1300 1300 1400 1400 1 4950->5150
13개의 행이 선택됨
참조 : https://blog.daonelab.com/post/24/1805/