윈도우 함수란
- - 분석함수 중에서 윈도우절(WINDOWNING 절)을 사용하는 함수를 윈도우 함수라고 한다.
- - 윈도우절을 사용하면 PARTITION BY 절에 명시된 그룹을 좀 더 세부적으로 그룹핑 할 수 있다.
- - 윈도우절은 분석함수중에서 일부(AVG, COUNT, SUM, MAX, MIN)만 사용 할 수 있다.
WINDOWNING 절 Syntax
1 2 3 4 5 6 | 윈도우 함수 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의 급여 합계를 출력하는 예제이다
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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 |
이쿼리 분석: 순번대로 보기 바람.
SELECT empno, ename, deptno, sal,
SUM(sal) OVER (PARTITION BY deptno //1번 부서별로 그룹을 맺으며
ORDER BY empno //2번 정렬은 사번으로.
ROWS 1 PRECEDING ) pre_sum //3번 이전 로우1개와의 합을 pre_sum으로 출력
FROM emp;
ROWS 사용 예제2
아래 예제는 첫 번째 ROW부터 마지막 ROW까지의 합과(SAL1), 첫 번째 ROW부터 현재 ROW까지의 합(SAL2) 그리고 현재 ROW부터 마지막 ROW까지의 합(SAL3)을 출력하는 예제이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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 |
이쿼리 분석: 순서대로 보기 바람.
SELECT empno, ename, deptno, sal, SUM(sal) OVER(ORDER BY deptno, empno ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sal1,//1번 위에서 정의되어 있다시피 처음부터 마지막로우까지의 합을 deptno가 우선 그담 사원번호순으로 정렬하여 출력. SUM(sal) OVER(ORDER BY deptno, empno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sal2, //2번 처음부터 현재 로우까지의 합.정렬은 위와 동일 SUM(sal) OVER(ORDER BY deptno, empno ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) sal3 //3번 현재로우부터 마지막로우까지의 합 정렬은 위와동일. FROM emp;RANGE 사용 예제
아래는 월별 금액 리스트를 출력하고, 직전 3개월 합계(AMT_PRE3)와 이후 3개월 합계(AMT_FOL3)를 함께 표시하는 예제이다.
아래 예제에서는 7월 데이터가 없기 때문에 직전 3개월 합계(AMT_PRE3) 8월의 경우 6월,5월 두 달치만 누적된 것을 확인 할 수 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | WITH test AS(SELECT '200801' yyyymm, 100 amt FROM dualUNION ALL SELECT '200802', 200 FROM dualUNION ALL SELECT '200803', 300 FROM dualUNION ALL SELECT '200804', 400 FROM dualUNION ALL SELECT '200805', 500 FROM dualUNION ALL SELECT '200806', 600 FROM dualUNION ALL SELECT '200808', 800 FROM dualUNION ALL SELECT '200809', 900 FROM dualUNION ALL SELECT '200810', 100 FROM dualUNION ALL SELECT '200811', 200 FROM dualUNION 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 900200802 200 100 1200200803 300 300 1500200804 400 600 1100200805 500 900 1400200806 600 1200 1700200808 800 1100 1200200809 900 1400 600200810 100 1700 500200811 200 1800 300200812 300 1200 |
분석:순서대로 보기 바람. //좀힘들었음.
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 //1번 시작이 3개월 전부터 1개얼 전까지의 합임 정렬은 당연히 날짜순..
, SUM(amt) OVER(ORDER BY TO_DATE(yyyymm,'yyyymm')
RANGE BETWEEN INTERVAL '1' MONTH FOLLOWING
AND INTERVAL '3' MONTH FOLLOWING) amt_fol3 //2번 1개월후부터 3개월후까지의 합 정렬은 날짜순
FROM test
;
태그
문서에 대하여
- - 강좌 URL : http://www.gurubee.net/lecture/2674
- - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
'Oracle' 카테고리의 다른 글
| KEEP (0) | 2014.12.03 |
|---|---|
| LAG, LEAD (다른 행의 값을 참조) (0) | 2014.12.03 |
| 집계함수 -> 분석함수 사용하기 (0) | 2014.12.03 |
| 순위함수 RANK DENSE_RANK ROW_NUMBER 특징별 기억하면 좋고 (0) | 2014.12.03 |
| 집계함수와 분석함수 (0) | 2014.12.03 |
은호아빠
여행, 맛집, 일상, 프로그래밍, 개발자, 윈도우, 웹, jsp, spring, db, mysql, oracle, c#