퀴즈로 배우는 SQL
목록
[퀴즈] 분석함수의 이해 1 7 99,999+

by 마농 분석함수 RANGE BETWEEN ROWS BETWEEN NUMTOYMINTERVAL NUMTODSINTERVAL UNBOUNDED PRECEDING [2015.06.09]

이번 퀴즈로 배워보는 SQL 시간에는 분석 함수(Analytic Function)를 이용해 합계를 구하는 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

문제

다음과 같이 월별 금액을 가진 집합에서 해당 월을 기준으로 이전 3개월간 금액 합계와 이후 3개월간 금액 합계를 함께 출력하는 SQL을 작성하세요.

  • [리스트 1] 원본 리스트
WITH t AS
(
SELECT '201201' yyyymm, 100 amt FROM dual
UNION ALL SELECT '201202', 200 FROM dual
UNION ALL SELECT '201203', 300 FROM dual
UNION ALL SELECT '201204', 400 FROM dual
UNION ALL SELECT '201205', 500 FROM dual
UNION ALL SELECT '201206', 600 FROM dual
UNION ALL SELECT '201207', 700 FROM dual
UNION ALL SELECT '201208', 800 FROM dual
UNION ALL SELECT '201209', 900 FROM dual
UNION ALL SELECT '201210', 100 FROM dual
UNION ALL SELECT '201211', 200 FROM dual
UNION ALL SELECT '201212', 300 FROM dual
)
SELECT * FROM t
;


YYYYMM        AMT
------ ----------
201201        100
201202        200
201203        300
201204        400
201205        500
201206        600
201207        700
201208        800
201209        900
201210        100
201211        200
201212        300

  • [표 1] 원본 테이블
  • 원본 테이블

  • [표 2] 결과 테이블
  • 결과 테이블

문제설명

이 문제는 연도와 월별로 특정 금액을 가진 집합에서 해당 연월을 기준으로 이전 3개월간의 금액 합계와 이후 3개월간의 금액 합계를 구하는 문제입니다.

[표 2]의 결과를 보면 첫 번째 달인 1월에는 직전 3개월에 해당하는 자료가 없으므로 Null 값이 나오고, 이후 3개월에 속하는 2, 3, 4월의 합계는 200, 300, 400을 더한 900이 나왔습니다.

이처럼 2월에는 1월 합계와 3, 4, 5월 합계, 3월에는 1, 2월 합계와 4, 5, 6월 합계를 보여주면 됩니다.

정답

문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.

  • [리스트 2] 정답 리스트
WITH t AS
(
SELECT '201201' yyyymm, 100 amt FROM dual
UNION ALL SELECT '201202', 200 FROM dual
UNION ALL SELECT '201203', 300 FROM dual
UNION ALL SELECT '201204', 400 FROM dual
UNION ALL SELECT '201205', 500 FROM dual
UNION ALL SELECT '201206', 600 FROM dual
UNION ALL SELECT '201207', 700 FROM dual
UNION ALL SELECT '201208', 800 FROM dual
UNION ALL SELECT '201209', 900 FROM dual
UNION ALL SELECT '201210', 100 FROM dual
UNION ALL SELECT '201211', 200 FROM dual
UNION ALL SELECT '201212', 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 t
;


YYYYMM        AMT   AMT_PRE3   AMT_FOL3
------ ---------- ---------- ----------
201201        100                   900
201202        200        100       1200
201203        300        300       1500
201204        400        600       1800
201205        500        900       2100
201206        600       1200       2400
201207        700       1500       1800
201208        800       1800       1200
201209        900       2100        600
201210        100       2400        500
201211        200       1800        300
201212        300       1200

어떤가요? 여러분이 만들어본 리스트와 같은가요?

틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

이번 문제는 분석 함수의 기본적인 사용법에 대해 익히는 문제입니다. 분석 함수에서 잘 사용하기 않는 기능 하나를 배우는 몸풀기 문제라고 보시면 됩니다. 우선 분석 함수를 이용하지 않고 문제를 풀어본 다음, 분석 함수를 적용해 풀어본 답과 비교해보겠습니다.

  • [리스트 3] 셀프 조인을 이용한 문제 풀이
WITH t AS
(
SELECT '201201' yyyymm, 100 amt FROM dual
UNION ALL SELECT '201202', 200 FROM dual
UNION ALL SELECT '201203', 300 FROM dual
UNION ALL SELECT '201204', 400 FROM dual
UNION ALL SELECT '201205', 500 FROM dual
UNION ALL SELECT '201206', 600 FROM dual
UNION ALL SELECT '201207', 700 FROM dual
UNION ALL SELECT '201208', 800 FROM dual
UNION ALL SELECT '201209', 900 FROM dual
UNION ALL SELECT '201210', 100 FROM dual
UNION ALL SELECT '201211', 200 FROM dual
UNION ALL SELECT '201212', 300 FROM dual
)
SELECT a.yyyymm
     , a.amt
     , SUM(CASE WHEN b.yyyymm < a.yyyymm THEN b.amt END) amt_pre3
     , SUM(CASE WHEN b.yyyymm > a.yyyymm THEN b.amt END) amt_fol3
  FROM t a
     , t b
 WHERE b.yyyymm >= TO_CHAR(ADD_MONTHS(TO_DATE(a.yyyymm, 'yyyymm'), -3), 'yyyymm')
   AND b.yyyymm <= TO_CHAR(ADD_MONTHS(TO_DATE(a.yyyymm, 'yyyymm'),  3), 'yyyymm')
 GROUP BY a.yyyymm, a.amt
 ORDER BY a.yyyymm
;

YYYYMM        AMT   AMT_PRE3   AMT_FOL3
------ ---------- ---------- ----------
201201        100                   900
201202        200        100       1200
201203        300        300       1500
201204        400        600       1800
201205        500        900       2100
201206        600       1200       2400
201207        700       1500       1800
201208        800       1800       1200
201209        900       2100        600
201210        100       2400        500
201211        200       1800        300
201212        300       1200

[리스트 3]은 분석 함수 대신 셀프 조인을 이용해 문제를 해결한 것입니다. 내용을 살펴보면 같은 테이블을 두 번 읽어 서로 범위를 비교해 조인한 다음, 기준이 되는 테이블로 그룹 바이해 비교 대상 금액을 합산하는 방식입니다. 설명에서도 알 수 있듯이 같은 테이블을 두 번이나 읽어 조인해야 하므로 효율성이 떨어집니다.

그러나 분석 함수를 사용하면 테이블을 한 번만 읽고도 문제를 해결할 수 있으니 매우 효율적이겠죠? 정답 쿼리를 설명하기 전 우리가 흔히 사용하는 누적 합계를 구하는 SQL부터 살펴보겠습니다.

  • [리스트 4] 누적 합계 구하기
SELECT yyyymm
     , amt
     , SUM(amt) OVER(ORDER BY yyyymm) amt_tot
  FROM t
;

YYYYMM        AMT    AMT_TOT
------ ---------- ----------
201201        100        100
201202        200        300
201203        300        600
201204        400       1000
201205        500       1500
201206        600       2100
201207        700       2800
201208        800       3600
201209        900       4500
201210        100       4600
201211        200       4800
201212        300       5100

  • [표 3] 누적합계
  • 누적합계

[표 3]의 결과를 보면 위에서부터 차례대로 금액이 누적되는 것을 볼 수 있습니다. [리스트 4]에 있는 구문들을 좀더 자세히 알아보죠. 

    , SUM(amt) OVER(ORDER BY yyyymm) amt_tot

이 구문은 yyyymm으로 정렬하면서 amt 값을 합산하라는 의미입니다. 간단한 구문만으로도 아주 쉽게 누적 합계를 구할 수 있습니다. 하지만 우리가 지금 구하려고 하는 것은 처음부터 해당 월까지의 누적 합계가 아니라 바로 이전 3개월 동안의 값을 더한 것입니다.

그렇다면 분석 함수에서 구간을 정해줄 수 있는 구문이 있다면 좋겠네요? 사실 앞서 살펴본 누적 합계 구문도 다음과 같이 구간을 정해주는 구문이 생략된 형태입니다. 

, SUM(amt) OVER(
                ORDER BY yyyymm
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ) amt_tot

여기서 잠깐, 집계 함수와 분석 함수의 차이점을 살펴보도록 하죠. 집계 함수와 분석 함수의 차이는 결과 행의 수가 다르다는 데 있습니다. 집계 함수는 결과를 집약하는 것인 만큼 레코드 수가 줄어들지만, 분석 함수는 레코드 수가 줄어들지 않은 상태 그대로를 보여주면서 집계 결과도 함께 보여줍니다.

구문상에서 보면 SUM이라고 하는 집계 함수에 OVER라는 구문이 붙어서 분석 함수가 된다는 걸 알 수 있습니다. 원래 OVER 안에는 그룹을 지정해주는 PARTITION BY 라는 구문이 있는데 여기서는 생략됐죠. 그 다음에는 순서를 지정해주는 ORDER BY 구문이 있고 집계 대상의 구간을 지정해주는 ROWS 또는 RANGE와 같은 구문이 있다는 걸 알 수 있습니다.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 특히 이 부분이 생략된 경우라면 기본(Default) 값을 나타내고 있는 것 입니다. 위 구문을 해석해 보면, 맨앞 행(UNBOUNDED PRECEDING)에서부터 현재 행(CURRENT ROW)까지로 구간을 한정하고 있습니다.

따라서 이 구문을 생략하지 않고 구간 정보를 변경한 다음 실행한다면 원하는 결과를 얻을 수 있을 것 같습니다. 3행 이전부터 1행 이전까지 그리고 1행 이후부터 3행 이후까지로 구간을 한정해보겠습니다.

  • [리스트 5] ROWS BETWEEN 
SELECT yyyymm
     , amt
     , SUM(amt) OVER(ORDER BY yyyymm
                ROWS BETWEEN '3' PRECEDING AND '1' PRECEDING) amt_pre3
     , SUM(amt) OVER(ORDER BY yyyymm
                ROWS BETWEEN '1' FOLLOWING AND '3' FOLLOWING) amt_fol3
  FROM t
;

YYYYMM        AMT   AMT_PRE3   AMT_FOL3
------ ---------- ---------- ----------
201201        100                   900
201202        200        100       1200
201203        300        300       1500
201204        400        600       1800
201205        500        900       2100
201206        600       1200       2400
201207        700       1500       1800
201208        800       1800       1200
201209        900       2100        600
201210        100       2400        500
201211        200       1800        300
201212        300       1200

[리스트 5]의 쿼리 실행결과가 [표 2]의 결과 테이블과 같음을 확인할 수 있습니다. 그렇다면 ROWS BETWEEN 구문이 정답일까요? 예제에서는 1년 12달 자료가 모두 있습니다. 하지만 만약 중간에 누락된 달이 있다면 어떻게 될까요? 4월 자료를 삭제한 다음 결과를 직접 확인해보겠습니다.

  • [리스트 6] ROWS BETWEEN 검증
WITH t AS
(
SELECT '201201' yyyymm, 100 amt FROM dual
UNION ALL SELECT '201202', 200 FROM dual
UNION ALL SELECT '201203', 300 FROM dual
-- UNION ALL SELECT '201204', 400 FROM dual -- 삭제
UNION ALL SELECT '201205', 500 FROM dual
UNION ALL SELECT '201206', 600 FROM dual
UNION ALL SELECT '201207', 700 FROM dual
UNION ALL SELECT '201208', 800 FROM dual
UNION ALL SELECT '201209', 900 FROM dual
UNION ALL SELECT '201210', 100 FROM dual
UNION ALL SELECT '201211', 200 FROM dual
UNION ALL SELECT '201212', 300 FROM dual
)
SELECT yyyymm
     , amt
     , SUM(amt) OVER(ORDER BY yyyymm
                ROWS BETWEEN '3' PRECEDING AND '1' PRECEDING) amt_pre3
     , SUM(amt) OVER(ORDER BY yyyymm
                ROWS BETWEEN '1' FOLLOWING AND '3' FOLLOWING) amt_fol3
  FROM t
;

YYYYMM        AMT   AMT_PRE3   AMT_FOL3
------ ---------- ---------- ----------
201201        100                  1000
201202        200        100       1400
201203        300        300       1800
201205        500        600       2100
201206        600       1000       2400
201207        700       1400       1800
201208        800       1800       1200
201209        900       2100        600
201210        100       2400        500
201211        200       1800        300
201212        300       1200
  • [표 4] ROWS BETWEEN 검증 결과 테이블
  • ROWS BETWEEN 검증 결과 테이블

[표 4]의 결과를 보면 5월을 기준으로 이전 3개월 자료는 2, 3, 4월의 합계가 나와야 하는데 1, 2, 3월의 합계가 나왔습니다. 즉 ROWS BETWEEN 구문을 사용하면 오류의 소지가 있다는 것이지요. 즉, 3개 행이라는 행 단위 구간이 아닌 3개월이라는 시간 단위 구간으로 확인돼야 합니다.

이 때 사용하는 구문이 RANGE BETWEEN입니다. 하지만 RANGE BETWEEN 구문을 사용하기 위해서는 몇 가지 조건이 있습니다. 우선 ORDER BY 구문에 들어가는 항목이 반드시 시간을 나타낼 수 있는 Date형이어야 합니다.

따라서 문자형인 yyyymm 항목을 날짜로 형을 변환해야 합니다. 또한 BETWEEN 절에 들어갈 구간 값은 INTERVAL 형태로 주어져야 합니다. 모든 과정을 거치고 나니 정답 구문이 완성됐습니다.

    , 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

여기서 한 가지 팁을 소개하면, INTERVAL 구문은 다른 형태로도 사용할 수 있습니다.

    INTERVAL '3' MONTH
    NUMTOYMINTERVAL(3, 'MONTH')

INTERVAL에 사용할 수 있는 것은 MONTH만 있는 게 아니겠지요? [표 5]를 참고해 다양한 형태의 구간 정보에 대한 부분 합계를 구해보세요.

  • [표 5] INTERVAL 정보
  • INTERVAL 정보

이번 시간에는 분석 함수의 숨은 기능에 대해 살펴봤습니다. 어떤가요? 분석 함수에 대해 확실하게 이해가 되셨나요? 대부분 분석 함수에서 PARTITION BY와 ORDER BY 정도만 사용했을 텐데, 이번 시간을 통해 분석 함수에서 범위를 제한할 수 있는 ROWS/RANGE 구문도 있다는 걸 알아두길 바랍니다.

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

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

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

목록
by 빡친다 [2015.06.09 15:59:45]

1시간동안 잘배웠습니다.

by 갈매기 [2016.07.20 07:59:16]

MSsql로는 to_date를 사용할 수 없는데, MSsql코드로 부탁 드려 봅니다. 

by 마농 [2016.07.20 09:31:45]

TO_DATE(yyyymm, 'yyyymm')  ===>  CAST(CONCAT(yyyymm, '01') AS DATE)
단, Range Between 구문으로 3개월 전 조건을 줄 수 없는 듯 하네요.

by 갈매기 [2016.07.21 07:42:47]

네 그러네요. Range 안에 Between을 쓸수가 없네요. 방법 없을까요? 

by 마농 [2016.07.21 09:30:41]

[리스트 3] 셀프 조인을 이용한 문제 풀이 <-- 이걸 이용하셔야 할 듯.
 

WITH t AS
(
SELECT '201201' yyyymm, 100 amt
UNION ALL SELECT '201202', 200
UNION ALL SELECT '201203', 300
--UNION ALL SELECT '201204', 400
UNION ALL SELECT '201205', 500
UNION ALL SELECT '201206', 600
UNION ALL SELECT '201207', 700
UNION ALL SELECT '201208', 800
UNION ALL SELECT '201209', 900
UNION ALL SELECT '201210', 100
UNION ALL SELECT '201211', 200
UNION ALL SELECT '201212', 300
)
SELECT a.yyyymm
     , a.amt
     , SUM(CASE WHEN b.yyyymm < a.yyyymm THEN b.amt END) amt_pre3
     , SUM(CASE WHEN b.yyyymm > a.yyyymm THEN b.amt END) amt_fol3
  FROM t a
     , t b
 WHERE b.yyyymm >= CONVERT(VARCHAR(6), DATEADD(Month, -3, CONCAT(a.yyyymm, '01')), 112)
   AND b.yyyymm <= CONVERT(VARCHAR(6), DATEADD(Month,  3, CONCAT(a.yyyymm, '01')), 112)
 GROUP BY a.yyyymm, a.amt
 ORDER BY a.yyyymm
;

 

by 갈매기 [2016.07.26 00:08:39]

감사합니다. 한가지 더 질문이 있습니다. 

b.yyyymm >= CONVERT(VARCHAR(6), DATEADD(Month, -3, CONCAT(a.yyyymm, '01')), 112)

yyyymm(4자리)와 yyyymmdd(6자리를) 굳이 비교하는 까닭은 무엇인지요? Convert style에 YYYYMM 이 없음으로 6자리인 112로만 convert 가능해서 그런것인가요? 

by 마농 [2016.07.26 08:18:39]

비교하는 양쪽 모두 yyyymm(6자리) 입니다.
4자리도 없고, yyyymmdd 형태도 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입