마농의 SQL 백문백답: 단순하고 쉽게 작성하는 SQL 노하우 [9회]

분석함수(Analytic Function)(5)

단순하고 쉽게 작성하는 SQL 노하우를 공유하자!

SQL은 필자에게 있어서 참으로 이해하기 쉬운 프로그램 언어였다. 간단한 기본 구문(SELECT, FROM, WHERE, GROUP BY, HAVING)과 조인을 통해 안 되는 게 없을 정도였기 때문이다. 물론 기본 구문 외에 다양한 함수들은 따로 배워야 하겠지만 말이다. 필자가 Q&A 게시판에 올라오는 수많은 질문에 답하면서 느낀 점은 ‘질문을 올린 사람은 SQL을 어렵게 생각하고 있었다’는 것이었다.

앞서 언급한 기본 구문들은 정말 너무나도 간단한데, 이 간단한 구문을 복잡하게 사용하고 있었던 것이다. 필자가 다루는 SQL은 전혀 복잡하지 않다. 필자는 SQL을 단순하고 쉽게 작성하는 노하우를 이 페이지를 통해 독자들과 공유하고자 한다. 자주 올라오는 SQL 질문들, 독자들이 어려워했던 질문들의 해법을 제시하고자 한다.

지난 시간에는 지정한 개수만큼 그룹으로 나눠 주는 순위함수 NTILE 함수를 배웠다. 이와 함께 처음 값과 마지막 값을 구하는 FIRST_VALUE/LAST_VALUE 함수와 NULL 건너뛰기 구문인 IGNORE NULLS 구문을 배웠다. 이 구문은 LAG/LEAD, FIRST_VALUE/LAST_VALUE 함수에서 사용할 수 있다.

이번 시간에도 분석함수의 다른 기능들을 살펴보겠다. 분석함수로 풀어낼 수 있는 다양한 문제의 해법을 제시하고, 이를 통해 분석함수에 대해 배워보는 시간을 갖자.

[질문 38] 부서별 급여합계와 전체급여 대비 부서 급여가 차지하는 비율을 함께 구하고 싶어요.

이번 문제는 부서별 급여 합계 비율을 구하는 문제다. 비율을 구하는 공식은 부서합계 / 전체합계일 것이다. 부서별 합계는 부서별로 GROUP BY하여 SUM을 하면 구할 수 있을 것이다. 전체 합계는 앞선 시간에 배웠던 바대로 분석함수를 이용하면 쉽게 구할 수 있다.

SELECT deptno
, SUM(sal) sum_sal
, SUM(SUM(sal)) OVER() tot_sal
, ROUND(SUM(sal) / SUM(SUM(sal)) OVER() * 100, 2) rat_sal
FROM emp
GROUP BY deptno
ORDER BY deptno
;

GROUP BY와 SUM을 통해 부서별 급여 합계를 구했다. 이 부서별 급여 합계 SUM(sal)을 다시 SUM() OVER()하여 전체 합계를 구했다. 이 두 값을 나누어 백분율로 환산해 급여 합계 비율을 구했다.

하지만 이보다 더 간단한 방법이 있다.

SELECT deptno
, SUM(sal) sum_sal
, SUM(SUM(sal)) OVER() tot_sal
, ROUND( SUM(sal) / SUM(SUM(sal)) OVER() * 100, 2) rat_sal1
, ROUND(RATIO_TO_REPORT(SUM(sal)) OVER() * 100, 2) rat_sal2
FROM emp
GROUP BY deptno
ORDER BY deptno
;

분석함수 중 비율을 구하는 함수인 RATIO_TO_REPORT가 따로 있다. 전체합계를 구해 나누어 주는 방법 대신 바로 비율을 구할 수 있다.

[질문 39] 이전 문제 [질문 38]을 분석함수 없이 풀고 싶어요.

분석함수를 사용하지 않고 결과를 얻으려면 어떻게 해야 할까 부서별 합계와 전체 합계를 따로 구해와 조인한 뒤 계산해야 할 것이다.

SELECT a.deptno
, a.sum_sal
, b.tot_sal
, ROUND(a.sum_sal / b.tot_sal * 100, 2) rat_sal
FROM (SELECT deptno
, SUM(sal) sum_sal
FROM emp
GROUP BY deptno
) a
, (SELECT SUM(sal) tot_sal
FROM emp
) b
;

테이블을 따로 두 번 읽어 두 번 집계하고 조인하는 수고가 따른다. 분석함수를 사용하는 것이 간결하고 효율적일 것이다.

[질문 40] 직전 3개월 이후 3개월 합계를 함께 구하고 싶어요.

월별 금액이 저장된 다음 자료를 이용해 직전 3개월 합계와 이후 3개월 합계를 함께 표현하는 문제다.



이전 질문에서 이와 유사한 문제를 풀어본 적이 있다. [질문 28] 바로 이전 3행의 평균을 구하는 문제다. 분석함수의 WINDOW 절을 이용해 문제를 풀었으며 사용한 구문은 다음과 같다.

SELECT empno, sal
, ROUND(
AVG(sal) OVER(ORDER BY sal
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
, 2) AS sal_avg3
FROM emp
;

이 방법을 이용한다면 문제를 쉽게 해결할 수 있을 것이다.

SELECT ym, amt
, SUM(amt) OVER(ORDER BY ym
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) pre_3
, SUM(amt) OVER(ORDER BY ym
ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) fol_3
FROM t
;

ROWS BETWEEN 구문을 이용해 3행 이전부터 1행 이전까지의 합계를 구했다. 이전 행은 PRECEDING을 이용했으며 이후 행은 FOLLOWING을 이용했다. 언뜻 보면 결과가 잘 나온 것 같다.

하지만 빨간색으로 표시된 부분의 데이터는 올바른 데이터가 아니다. 2015년 2월의 이후 3개월 합계는 3월(300), 4월(400), 5월(500)의 합계(1200)가 정확하게 계산된다.

하지만 2015년 3월의 경우 계산 결과 1600 은 4월(400), 5월(500), 7월(700)의 합계이다. 6월의 결과가 없기 때문에 7월이 밀려 올라가 합계에 포함된 것이다.

만약 6월의 자료가 있었다면, 결과는 맞게 나왔을 것이다. 하지만 모든 월의 자료가 항상 존재한다는 가정을 할 수 없는 상황에서는 이 방법은 올바른 방법이 아닐 것이다.

그러기 위해서는 값을 구하는 기준이 행이 아닌 월이 되어야 한다. 여기서 우리는 ROWS 구문 대신 RANGE 구문을 사용할 것이다.

SELECT ym, amt
, SUM(amt) OVER(ORDER BY TO_DATE(ym, 'yyyymm')
RANGE BETWEEN INTERVAL '3' MONTH PRECEDING
AND INTERVAL '1' MONTH PRECEDING) pre_3
, SUM(amt) OVER(ORDER BY TO_DATE(ym, 'yyyymm')
RANGE BETWEEN INTERVAL '1' MONTH FOLLOWING
AND INTERVAL '3' MONTH FOLLOWING) FOL_3
FROM t
;

ROWS 구문이 RANGE 구문으로 변경되면서 바뀐 부분을 파란색으로 표시했다. ROWS 구문에서 3행 이전을 의미하는 숫자 3 대신 RANGE 구문에서는 INTERVAL '3' MONTH 구문을 사용하여 3개월 이전을 표시했다. NUMBER 타입 대신 INTERVAL 타입이 사용된 것이다.

이렇게 시간의 간격(INTERVAL)을 사용하려면 정렬기준 자체가 시간이어야 한다. 따라서 TO_DATE 를 사용하여 날짜형으로 변경해 정렬했다.

[질문 41] 이전 문제 [질문 40]을 분석함수 없이 풀고 싶어요.

분석함수를 사용하지 않고 이 문제를 해결하려면 어떻게 해야 할까 셀프 조인을 이용해 직전 3개월을 조인한 뒤 그룹바이로 집계해야 할 것이다.

SELECT a.ym
, a.amt
, SUM(b.amt) pre_3
FROM t a
, t b
WHERE b.ym(+) BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE(a.ym, 'yyyymm'), -3), 'yyyymm')
AND TO_CHAR(ADD_MONTHS(TO_DATE(a.ym, 'yyyymm'), -1), 'yyyymm')
GROUP BY a.ym, a.amt
ORDER BY a.ym
;

이후 3개월도 추가한다면 어떻게 될까 t 테이블을 한꺼번에 3번 사용하여 Self Join 한다면 어떻게 될까 2번 조인하면서 3배로 부풀려진 상태로 다시 조인하면 9건(3*3)의 자료로 부풀려 질 것이다. 이 상태로 합산하면 원치 않게 합산 결과가 3배로 커진다.

즉 M:M 조인이 아닌 1:M 조인이 돼야만 원하는 결과를 얻을 수 있다. 그러려면 조인 후 집계하고 집계결과를 이용해 다시 단계별로 조인해야 할 것이다.

SELECT a.ym
, a.amt
, a.pre_3
, SUM(c.amt) fol_3
FROM (SELECT a.ym
, a.amt
, SUM(b.amt) pre_3
FROM t a
, t b
WHERE b.ym(+) BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE(a.ym, 'yyyymm'), -3), 'yyyymm')
AND TO_CHAR(ADD_MONTHS(TO_DATE(a.ym, 'yyyymm'), -1), 'yyyymm')
GROUP BY a.ym, a.amt
) a
, t c
WHERE c.ym(+) BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE(a.ym, 'yyyymm'), 1), 'yyyymm')
AND TO_CHAR(ADD_MONTHS(TO_DATE(a.ym, 'yyyymm'), 3), 'yyyymm')
GROUP BY a.ym, a.amt, a.pre_3
ORDER BY a.ym
;

분석함수를 이용하는 것이 얼마나 편리한지를 보여주는 문제다.

정리하며

전체 대비 비율을 구하는 분석함수 RATIO_TO_REPORT 함수를 배웠다. 분석함수의 WINDOW 구문 사용법 중 RANGE 사용법과 이 때 시간 구간을 지정할 수 있는 INTERVAL 타입의 사용에 대해 배웠다. 또한 분석함수를 사용하지는 않는 쿼리도 함께 작성해 보았다.

다음 시간에는 이제까지 배운 분석함수를 사용하는 응용문제를 풀어보겠다.