PRECEDING AND FOLLOWING 예제 (ORACLE)

-- PREDECING AND FOLLOWING TEST 
WITH UNBOUNDED_TEST AS (SELECT '1' AS COL1, '' AS COL2 FROM DUAL
UNION ALL
SELECT '2' AS COL1, '1' AS COL2 FROM DUAL
UNION ALL
SELECT '3' AS COL1, '2' AS COL2 FROM DUAL
UNION ALL
SELECT '4' AS COL1, '3' AS COL2 FROM DUAL
) SELECT
FIRST_VALUE(COL1) OVER (PARTITION BY COL1 ORDER BY CO1 ROWS UNBOUNDED PRECEDING) AS UNBOUNDED_PRECEDING
,FIRST_VALUE(COL1) OVER (ORDER BY COL1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS ONE_PRECEDING_AND_CURRENT_ROW
,FIRST_VALUE(COL1) OVER (ORDER BY COL1 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS TWO_PRECEDING_AND_CURRENT_ROW
,FIRST_VALUE(COL1) OVER (ORDER BY COL1 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS THREE_PRECEDING AND_CURRENT_ROW
,FIRST_VALUE(COL1) OVER (ORDER BY COL1 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS CURRENT_ROW_AND_1
,FIRST_VALUE(COL1) OVER (ORDER BY COL1 ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS CURRENT_ROW_AND_MAX_VALUE_TEST
FROM UNBOUNDED_TEST
--END OF TEST

결과값▼

UNBOUNDED_PRECEDING	ONE_PRECEDING_AND_CURRENT_ROW	TWO_PRECEDING_AND_CURRENT_ROW	THREE_PRECEDING_AND_CURRENT_ROW	CURRENT_ROW_AND_1	CURRENT_ROW_AND_MAX_VALUE
1	1	1	1	1	1
2	1	1	1	2	2
3	2	1	1	3	3
4	3	2	1	4	4