윈도우 함수란
- – 분석함수 중에서 윈도우절(WINDOWNING 절)을 사용하는 함수를 윈도우 함수라고 한다.
- – 윈도우절을 사용하면 PARTITION BY 절에 명시된 그룹을 좀 더 세부적으로 그룹핑 할 수 있다.
- – 윈도우절은 분석함수중에서 일부(AVG, COUNT, SUM, MAX, MIN)만 사용 할 수 있다.
WINDOWNING 절 Syntax
윈도우 함수 OVER (
PARTITION BY 절
ORDER BY 절 [ASC|DESC]
ROWS | RANGE
BETWEEN UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW
AND UNBOUNDED FOLLOWING | n FOLLOWING | CURRENT ROW
- – ROWS : 물리적인 ROW 단위로 행 집합을 지정한다.
- – RANGE : 논리적인 상대번지로 행 집합을 지정한다.
- – BETWEEN ~ AND 절 : 윈도우의 시작과 끝 위치를 지정한다.
- – UNBOUNDED PRECEDING : PARTITION의 첫 번째 로우에서 윈도우가 시작한다.
- – UNBOUNDED FOLLOWING : PARTITION의 마지막 로우에서 윈도우가 시작한다.
- – CURRENT ROW : 윈도우의 시작이나 끝 위치가 현재 로우 이다.
WINDOWING 절 예제
ROWS 사용 예제1
아래는 부서별(PARTITION BY deptno)로 이전 ROW(ROWS 1 PRECEDING)의 급여와 현재 ROW의 급여 합계를 출력하는 예제이다
SELECT empno, ename, deptno, sal,
SUM(sal) OVER (PARTITION BY deptno
ORDER BY empno
ROWS 1 PRECEDING ) pre_sum
FROM emp;
-- PRE_SUM : 이전 ROW와 현재 ROW의 급여 합계가 출력된 것을 확인 할 수 있다.
EMPNO ENAME DEPTNO SAL PRE_SUM
------ ------- ---------- ---------- ----------
7782 CLARK 10 2450 2450
7839 KING 10 5000 7450
7934 MILLER 10 1300 6300
7369 SMITH 20 800 800
7566 JONES 20 2975 3775
7788 SCOTT 20 3000 5975
7876 ADAMS 20 1100 4100
7902 FORD 20 3000 4100
7499 ALLEN 30 1600 1600
7521 WARD 30 1250 2850
7654 MARTIN 30 1250 2500
7698 BLAKE 30 2850 4100
7844 TURNER 30 1500 4350
7900 JAMES 30 950 2450
ROWS 사용 예제2
아래 예제는 첫 번째 ROW부터 마지막 ROW까지의 합과(SAL1), 첫 번째 ROW부터 현재 ROW까지의 합(SAL2) 그리고 현재 ROW부터 마지막 ROW까지의 합(SAL3)을 출력하는 예제이다.
SELECT empno, ename, deptno, sal,
SUM(sal) OVER(ORDER BY deptno, empno
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) sal1,
SUM(sal) OVER(ORDER BY deptno, empno
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) sal2,
SUM(sal) OVER(ORDER BY deptno, empno
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) sal3
FROM emp;
-- SAL1 : 첫 번째 ROW부터 마지막 ROW까지의 급여 합계이다.
-- SAL2 : 첫 번째 ROW 부터 현재 ROW까지의 급여 합계이다.
-- SAL3 : 현재 ROW부터 마지막 ROW까지 급여 합계이다.
EMPNO ENAME DEPTNO SAL SAL1 SAL2 SAL3
------ ------- ---------- ---------- ---------- ---------- ----------
7782 CLARK 10 2450 29025 2450 29025
7839 KING 10 5000 29025 7450 26575
7934 MILLER 10 1300 29025 8750 21575
7369 SMITH 20 800 29025 9550 20275
7566 JONES 20 2975 29025 12525 19475
7788 SCOTT 20 3000 29025 15525 16500
7876 ADAMS 20 1100 29025 16625 13500
7902 FORD 20 3000 29025 19625 12400
7499 ALLEN 30 1600 29025 21225 9400
7521 WARD 30 1250 29025 22475 7800
7654 MARTIN 30 1250 29025 23725 6550
7698 BLAKE 30 2850 29025 26575 5300
7844 TURNER 30 1500 29025 28075 2450
7900 JAMES 30 950 29025 29025 950
RANGE 사용 예제
아래는 월별 금액 리스트를 출력하고, 직전 3개월 합계(AMT_PRE3)와 이후 3개월 합계(AMT_FOL3)를 함께 표시하는 예제이다.
아래 예제에서는 7월 데이터가 없기 때문에 직전 3개월 합계(AMT_PRE3) 8월의 경우 6월,5월 두 달치만 누적된 것을 확인 할 수 있다.
WITH test AS
(
SELECT '200801' yyyymm, 100 amt FROM dual
UNION ALL SELECT '200802', 200 FROM dual
UNION ALL SELECT '200803', 300 FROM dual
UNION ALL SELECT '200804', 400 FROM dual
UNION ALL SELECT '200805', 500 FROM dual
UNION ALL SELECT '200806', 600 FROM dual
UNION ALL SELECT '200808', 800 FROM dual
UNION ALL SELECT '200809', 900 FROM dual
UNION ALL SELECT '200810', 100 FROM dual
UNION ALL SELECT '200811', 200 FROM dual
UNION ALL SELECT '200812', 300 FROM dual
)
SELECT yyyymm
, amt
, SUM(amt) OVER(ORDER BY TO_DATE(yyyymm,'yyyymm')
RANGE BETWEEN INTERVAL '3' MONTH PRECEDING
AND INTERVAL '1' MONTH PRECEDING) amt_pre3
, SUM(amt) OVER(ORDER BY TO_DATE(yyyymm,'yyyymm')
RANGE BETWEEN INTERVAL '1' MONTH FOLLOWING
AND INTERVAL '3' MONTH FOLLOWING) amt_fol3
FROM test
;
-- AMT_PRE3 : 직전 3개월 합계
-- AMT_FOL3 : 이후 3개월 합계
YYYYMM AMT AMT_PRE3 AMT_FOL3
--------- ---------- ---------- ----------
200801 100 900
200802 200 100 1200
200803 300 300 1500
200804 400 600 1100
200805 500 900 1400
200806 600 1200 1700
200808 800 1100 1200
200809 900 1400 600
200810 100 1700 500
200811 200 1800 300
200812 300 1200