Oracle Advanced SQL 강좌
윈도우 함수 4 9 18,768

by 김정식 PRECEDING FOLLOWING WINDOWING 절 윈도우 함수 분석함수 [2014.01.14]


윈도우 함수란

  • - 분석함수 중에서 윈도우절(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 

- 강좌 URL : http://www.gurubee.net/lecture/2674

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 시그너스7000 [2014.02.17 12:45:05]

윈도우 함수 설명이 Good 입니다


by 슬다 [2014.04.14 15:57:14]
요런건 어떤식으로 활용하는지 궁금하네요..감사합니다

by XX큰타이거 [2014.07.04 16:16:34]

영업정보시스템에서는 분석화면에 전월비교, 전년비교 분기별 합계, 분기별 평균 뭐 이런 데이터를 많이 뽑습니다.

저도 그런 분석 화면에 이런 쿼리들을 종종 써먹죠...^^


by 이근수 [2014.07.30 18:21:03]

좋은 강좌 감사합니다.

그런데 RANGE 설정은 날짜&시간 설정에서만 가능한가요??


by 마농 [2014.07.30 18:23:11]

수치도 가능합니다.


by 박민철 [2014.08.28 16:49:37]

좋은 강좌 감사합니다.

전월 수치 구할때 유용하겠네요~

즐겨찾기 등록해요


by 헬로헬로일 [2014.10.12 03:30:37]

잘보고가용 ~


by 버드나무 [2014.12.23 12:52:26]

강좌 잘봅니다.


by 준티 [2015.12.18 14:51:52]

작성해주신 row 사용예제2에서..

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;
 
셋다 depno, empno 정렬이 되어있어서 저렇게 출력되었으나
마지막 분석행을 order by empno, deptno으로 했더니 전체 로우가
empno,deptno 의 순서로 출력되더군요.
 
물론 맨마지막에 order by를 추가하면 되지만 혹시 여기 정해진 규칙같은것이 있나요?
항상 분석함수를 쓰면 자동으로 정렬되었던 기억이 납니다..
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입