[SQL 문제] 분석함수의 이해

[문제]

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

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

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

[원본 데이터]

CREATE TABLE TEMP
AS 
SELECT '202001' YYYYMM, 100 AMT FROM DUAL
UNION ALL SELECT '202002', 200 FROM DUAL
UNION ALL SELECT '202003', 300 FROM DUAL
UNION ALL SELECT '202004', 400 FROM DUAL
UNION ALL SELECT '202005', 500 FROM DUAL
UNION ALL SELECT '202006', 600 FROM DUAL
UNION ALL SELECT '202007', 700 FROM DUAL
UNION ALL SELECT '202008', 800 FROM DUAL
UNION ALL SELECT '202009', 900 FROM DUAL
UNION ALL SELECT '202010', 100 FROM DUAL
UNION ALL SELECT '202011', 200 FROM DUAL
UNION ALL SELECT '202012', 300 FROM DUAL;
 
SELECT * 
  FROM TEMP;

[결과 데이터]

[문제 풀이]

-- 셀프 조인
SELECT T1.YYYYMM
     , T1.AMT
     , SUM(CASE WHEN T2.YYYYMM < T1.YYYYMM THEN T2.AMT END) AS SUM_AMT_PRE3
     , SUM(CASE WHEN T2.YYYYMM > T1.YYYYMM THEN T2.AMT END) AS SUM_AMT_FOL3
  FROM TEMP T1, TEMP T2
 WHERE T2.YYYYMM >= TO_CHAR(ADD_MONTHS(TO_DATE(T1.yyyymm, 'yyyymm'), -3), 'yyyymm')
   AND T2.YYYYMM <= TO_CHAR(ADD_MONTHS(TO_DATE(T1.yyyymm, 'yyyymm'),  3), 'yyyymm')
 GROUP BY T1.YYYYMM, T1.AMT
 ORDER BY T1.YYYYMM;
 
-- 분석 함수
-- ROWS BETWEEN 구문을 사용하면 중간에 누락된 달이 있는 경우 합계가 다름.
SELECT YYYYMM
     , AMT
     , SUM(AMT) OVER(ORDER BY TO_DATE(YYYYMM, 'yyyymm') ROWS BETWEEN 3 PRECEDING 
                                                                 AND 1 PRECEDING) AS SUM_AMT_PRE3
     , SUM(AMT) OVER(ORDER BY TO_DATE(YYYYMM, 'yyyymm') ROWS BETWEEN 1 FOLLOWING 
                                                                 AND 3 FOLLOWING) AS SUM_AMT_FOL3
  FROM TEMP;
  
-- RANGE BETWEEN 구문을 사용하여 행 단위가 아니라 시간 단위로 합계를 구함.
-- ORDER BY 구문에 들어갈 값은 DATE형, BETWEEN 절에 들어갈 값은 INTERVAL형으로 변경.
SELECT YYYYMM
     , AMT
     , SUM(AMT) OVER(ORDER BY TO_DATE(YYYYMM, 'yyyymm') RANGE BETWEEN INTERVAL '3' MONTH  PRECEDING 
                                                                  AND INTERVAL '1' MONTH  PRECEDING) AS SUM_AMT_PRE3
     , SUM(AMT) OVER(ORDER BY TO_DATE(YYYYMM, 'yyyymm') RANGE BETWEEN INTERVAL '1' MONTH  FOLLOWING 
                                                                  AND INTERVAL '3' MONTH  FOLLOWING) AS SUM_AMT_FOL3
  FROM TEMP;

[참고]

그룹 내 집계(AGGREGATE) 관련 함수, WINDOWNING 절

출처 : 꿈꾸는 개발자, DBA 커뮤니티 구루비