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

이번 퀴즈로 배워보는 SQL 시간에는 여러 할인 쿠폰을 중복으로 적용하여 최종 할인 금액을 구하는 쿼리 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.

진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

문제

[리스트 1] 원본리스트(할인쿠폰)

CREATE TABLE t
AS
SELECT 1 seq, 2000 amt, NULL rat FROM dual
UNION ALL SELECT 2, NULL,   10 FROM dual
UNION ALL SELECT 3, 3000, NULL FROM dual
UNION ALL SELECT 4, NULL,   20 FROM dual;

SELECT * FROM t;

[표 1] 원본리스트(할인쿠폰)

     SEQ        AMT        RAT
-------- ---------- ----------
       1       2000
       2                    10
       3       3000
       4                    20

<표 1> 원본테이블로부터 <표 2>의 결과를 도출하는 SQL을 작성하세요.

[표 2] 결과테이블(할인금액)

     SEQ        AMT        RAT        PRC         DC        REM
-------- ---------- ---------- ---------- ---------- ----------
       1       2000                 20000       2000      18000
       2                    10      18000       1800      16200
       3       3000                 16200       3000      13200
       4                    20      13200       2640      10560

문제설명

<표 1>은 할인쿠폰 테이블입니다. 각 할인 쿠폰은 금액 할인 또는 비율 할인 두가지 방식의 쿠폰이 존재합니다.

금액(AMT)항목에 값이 있는 경우 정액으로 할인됩니다. 비율(RAT)항목에 값이 있는 경우 비율로 계산되어 할인됩니다. 순번(SEQ) 항목에 따라 할인 우선순위가 결정됩니다. 원금(PRC) 에 대한 쿠폰 적용 할인금액(DC) 와 할인금액 차감후 남은금액(REMAIN)을 구하는 문제입니다.

남은 금액(REMAIN)은 다음 순번의 할인 적용시 원금(PRC)으로 사용됩니다. 마농군은 <표 1>의 할인 쿠폰 네 장을 가지고 있습니다. 2만원짜리 옷을 한 벌 구입하면서 이 할인 쿠폰 네 장을 한꺼번에 사용할 생각입니다.

마농군은 순차적으로 할인금액을 적용항 후 남은 금액을 알고 싶어합니다. <표 2>의 결과를 도출하는 SQL을 작성하여 마농군을 도와주세요.

정답

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

[리스트 2] 정답 리스트

SELECT *
  FROM t
 MODEL
 DIMENSION BY (seq)
 MEASURES (amt, rat, 20000 prc, 0 dc, 0 rem)
 RULES
 ( rem[ANY] = NVL( rem[CV()-1], prc[1])
            - NVL( amt[CV()]
                 , NVL(rem[CV()-1], prc[1])
                   * rat[CV()] / 100
                 )
 , prc[seq > 1] = rem[CV()-1]
 , dc[ANY] = prc[CV()] - rem[CV()]
 )
;

어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

이번 문제는 순차적으로 할인쿠폰을 적용해 나가면서 할인금액을 게산하는 문제입니다. 문제를 풀기 전에 마농군이 최초 도전한 실패 쿼리를 살펴보겠습니다.

[리스트 3] 오류결과 리스트

SELECT seq, amt, rat
     , 20000 - SUM(dc) OVER(ORDER BY seq) + dc prc
     , dc
     , 20000 - SUM(dc) OVER(ORDER BY seq) rem
  FROM (SELECT seq, amt, rat
             , NVL(amt, 20000 * rat / 100) dc
          FROM t
        )
;

[표 3] 오류결과 테이블(할인금액)

   SEQ        AMT        RAT        PRC         DC        REM
------ ---------- ---------- ---------- ---------- ----------
     1       2000                 20000       2000      18000
     2                    10      18000       2000      16000
     3       3000                 16000       3000      13000
     4                    20      13000       4000       9000

<리스트 3>의 쿼리를 수행하여 <표 3>의 결과를 얻었습니다. <표 3>의 결과는 정답테이블인 <표2>의 결과와는 조금 다릅니다.

나름 분석함수를 통해 할인금액을 누적하여 적용하는 고급기술(?)을 사용했으나 결과는 틀렸습니다. 도대체 뭐가 문제일까요? 인라인 뷰 안의 할인금액(DC)를 살펴 보겠습니다.

    , NVL(amt, 20000 * rat / 100) dc

금액(AMT)이 있을 경우 금액을 그대로 적용하고, 없을 경우 NVL을 이용해 계산식을 적용합니다. 계산식은 원금(20000) * 비율(RAT) / 100을 적용합니다.

이때 순번(SEQ) 1의 경우 할인금액이 정확하게 맞지만, 그 이후로는 금액이 틀어지기 시작합니다. 이유는 원금이 20000원 고정으로 적용되었기 때문입니다.

두번째 순번에서는 첫 번째 남은금액(REM) 18000원을 기준으로 계산해야 하는데 계속해서 원금인 20000원을 그대로 사용하기 때문입니다.

만약 금액 할인권만 가지고 있다거나? 비율 할인권만 가지고 있었다면? 위와 같은 방법으로 분석함수를 통해 해결이 가능했을 것입니다. 여기서 반론을 제기하는 독자분이 계실 것 같은데요. “금액은 누적합산으로 간단하게 가능할 것 같은데 비율은 불가능 할 것 같다”라고 말이죠.

[리스트 4] 비율 누적 계산

WITH t AS
(
SELECT 1 seq, 10 rat FROM dual
UNION ALL SELECT 2, 20 FROM dual
UNION ALL SELECT 3, 30 FROM dual
UNION ALL SELECT 4, 40 FROM dual
)
SELECT seq, rat
     , rem / (1-rat/100) prc
     , rem / (1-rat/100) - rem dc
     , rem
  FROM (SELECT seq, rat
             , 20000 * ROUND(EXP(
               SUM(LN(1-rat/100)) OVER(ORDER BY seq)
               ), seq*2) rem
          FROM t
        )
;

[표 4] 비율 누적 계산

   SEQ        RAT        PRC         DC        REM
------ ---------- ---------- ---------- ----------
     1         10      20000       2000      18000
     2         20      18000       3600      14400
     3         30      14400       4320      10080
     4         40      10080       4032       6048

<리스트 4>의 쿼리를 수행해 <표 4>의 결과를 얻었습니다. 인라인뷰 안에서 우선 남은금액(REM)을 계산한 후 인하인뷰 밖에서 나머지 원금(PRC)과 할인금액(DC)를 구했습니다. 남은금액(REM)은 어떻게 구했을까요?

할인율 10%라는 말은 거꾸로 생각하면 남는 비율 90%를 의미합니다. 처음 90%가 남고, 다음 80%, 다음 70%, 다음 60%가 남는 형태입니다. 두번째는 90%의 80%, 세 번째는 90%의 80%의 70% 가 되는 거죠.

남는 비율이 계속 곱해지면 원금대비 남는 비율이 계산되는 원리인데요. 그렇다면 이는 금액과 달리 누적합이 아닌 누적곱이 되어야 한다는 말입니다.

그러나 누적합을 구해주는 분석함수는 있지만, 누적곱을 구하는 함수는 없습니다. 이때 이용할 수 있는 것이 로그함수를 이용한 합계를 구한 뒤 지수함수로 다시 풀어내면 곱셈결과가 나오게 되는 수학의 원리를 이용하는 것입니다.

  EXP(SUM(LN(1-rat/100)) OVER(ORDER BY seq))

잊었던 수학공식까지 다시 상기하실 필요는 없구요. 이정도 공식은 한번 외워두시면 좋을 듯 합니다. 다만, 로그와 지수등의 함수를 사용하면서 발생되는 소수점 오차에 주의하셔야 하구요. 저는 오차 보정을 위해 ROUND 함수를 사용했습니다. <리스트 4>의 쿼리는 이정도까지만 설명드리겠습니다.

일단 동일한 기준으로는 분석함수를 통한 누적집계가 가능하다는 것을 살펴봤습니다. 하지만 금액과 비율이 공존하는 이번 문제는 이러한 단순 누적으로는 해결이 안됩니다.

분석함수를 통한 집계로는 풀 수 없는 문제입니다. 이 문제는 한행 한행 차례대로 결과를 계산해 나가면서 이전행의 계산결과를 받아와서 이번행에서 처리하고 다시또 결과를 다음행으로 넘겨줘야만 합니다.

집합적인 처리가 아닌 절차적인 처리를 해야만 하는 문제입니다. 하지만 SQL은 집합적 구조적 질의어로 절차적 처리가 불가능하여 EK로 절처적 처리를 위한 PL/SQL을 두고 있습니다.

그렇다면 이번 문제는 SQL로는 풀 수 없는 문제일까요? 만약 그렇다면 제가 이번 문제를 내지 않았겠죠? 오라클 SQL에서도 일부 절차적 처리가 가능한 코드들이 새로 생겼습니다.

바로 오라클 10G의 MODEL 절과 11G의 재귀쿼리(RECURSIVE SQL) 구문입니다. 우선 MODEL 절의 기본 문법을 살펴보겠습니다.

[리스트 5] MODEL 기본 문법

SELECT *
  FROM 테이블
 MODEL
 PARTITION BY ( 구룹컬럼 )
 DIMENSION BY ( 키컬럼 )
 MEASURES ( 기타 컬럼 )
 RULES ( 컬럼에 대한 계산식 )
;

MODEL은 FROM절 뒤에 오면서 MODEL절을 사용하겠다는 선언부 역할을 합니다. PARTITION BY절은 대상 그룹을 나누는 기준 역할을 합니다.

이번 문제에서는 그룹별로 계산하는 문제가 아니라 생략되었습니다. DIMENSION BY 절은 키값을 지정하는 구문입니다. 이번 문제에서는 순번(SEQ) 항목을 킷값으로 지정했습니다.

  DIMENSION BY (seq)

MEASURES 절은 사용될 힝목들을 나열합니다.

  MEASURES (amt, rat, 20000 prc, 0 dc, 0 rem)

테이블 항목 뿐 아니라 상수값을 이용하여 선언할 수 있습니다. RULES 절에서는 항목들을 추출하는 공식을 사용합니다. MEASURES에서 정한 항목명 다음에 대괄호[]가 오며 이 대괄호 안에 키값을 배열처럼 지정할 수 있습니다. 예를 들어 다음과 같이 사용했다면

  RULES ( prc[1] = 20000 )

이는 순번(SEQ) 키 1인 행의 금액(PRC)에 값 20000을 지정하는 구문입니다. 프로그램 언어에서 사용하는 배열과 매우 흡사합니다. 이번 문제에서 사용된 형태를 살펴보면 우선 rem[ANY]를 사용했습니다.

이는 특정 키값을 지정한게 아니라 모든 킷값을 의미합니다. 즉 모든 행의 남은 금액을 계산하는 공식을 지정하겠다는 의미입니다. 남은 금액을 구하는 공식은 (원금 - 할인금액 = 남은 금액) 과 같습니다.

  NVL(rem[CV()-1], prc[1])

이게 원금입니다. 여기서 원금은 이전행의 남은 금액이 되겠지요. CV()는 현재행의 값을 의미합니다. CV()-1은 현재행의 키값에서 1을 뺀 값으로 rem[CV()-1] 은 이전행의 남은금액을 의미합니다.

NVL을 이용한 이유는 첫 번째 행의 이전행이 없으므로 현재행의 원금을 이용하기 위함입니다. 즉 이 구문은 이전행의 남은금액을 원금으로 가져오는 구문입니다.

- NVL( amt[CV()]
, NVL(rem[CV()-1], prc[1]) * rat[CV()] / 100
)

이것은 할인 금액을 빼는 부분입니다. 할인 금액은 이번 행의 금액이 있다면 그 금액을 amt[CV()] 적용하고 그렇지 않다면 원금과 비율을 이용하여 계산합니다.

할인금액 = 원금*비율/100입니다. 원금은 앞서 설명드린 이전행의 남은금액(NVL(rem[CV()-1], prc[1]))이 되며 여기에 현재행의 비율 (rat[CV()])을 곱하고 100으로 나눕니다.

ANY라는 키워드로 인해 모든 행의 남은금액에 계산되는데 계산되는 과정이 절차적으로 진행됩니다. ANY 에 의한 계산과정을 키값으로 차례대로 표현해보면 다음과 같습니다.

rem[1] = prc[1] - amt[1]
= 20000 - 20000 = 18000
rem[2] = rem[1] - rem[1]*rat[2]/100
= 18000 - 18000*10/100 = 16200
rem[3] = rem[2] - amt[3]
= 16200 - 3000 = 13200
rem[4] = rem[3] - rem[3]*rat[4]/100
= 13200 - 13200*20/100 = 10560

이전행의 값을 참조하여 다음행 계산에 사용하고 있습니다. 이를 ANY 라는 키워드를 이용하여 하나의 공식화 하여 나타낸 것이 바로 이것입니다.

RULES
( rem[ANY] = NVL( rem[CV()-1], prc[1])
- NVL( amt[CV()]
, NVL(rem[CV()-1], prc[1])
* rat[CV()] / 100
)

다음엔 이렇게 구한 남은금액을 이용하여 원금을 구합니다.

, prc[seq > 1] = rem[CV()-1]

남은 금액은 어렵게 구했지만 원금의 아주 간단합니다. 바로 이전행의 남은 금액을 그대로 가져오면 됩니다. 이번엔 ANY 대신 순번(SEQ)이 1보다 크다는 조건을 사용했습니다. 1번에 대한 원금은 이미 MEASURES 절에서 정의를 해준 상태이고 1행은 이전행이 없기 때문에 ANY 라는 키워드를 사용했다면 다음과 같이 계산식이 더 복잡해지겠지요.

, prc[ANY] = NVL(rem[CV()-1], prc[1])

마지막으로 할인금액(DC)는 원금(PRC) - 남은금액(REM)으로 구할 수 있습니다.

, dc[ANY] = prc[CV()] - rem[CV()]

이번 퀴즈로 배우는 SQL 시간에는 할인쿠폰을 순차적으로 적용하여 남은 금액을 계산해 내는 과정을 배웠습니다. 누적합과 누적곱을 이용하는 방법도 배웠습니다. 집합적, 구조적 언어인 SQL의 한계(절차적 쿼리 불가)를 이해하고 오라클 10G의 새로운 기능인 MODEL 구문을 이용하여 절차적인 처리가 가능함을 소개했습니다. 잘 알아두면 SQL의 한계를 극복하는 좋은 TIP이 될 것입니다.

[리스트 6] 11G Recursive SQL

WITH t AS
(
SELECT 1 seq, 2000 amt, NULL rat FROM dual
UNION ALL SELECT 2, NULL,   10 FROM dual
UNION ALL SELECT 3, 3000, NULL FROM dual
UNION ALL SELECT 4, NULL,   20 FROM dual
)
, t1(seq, amt, rat, prc, dc, rem) AS
(
SELECT seq, amt, rat
     , 20000 prc
     , NVL(amt, 20000 * rat / 100) dc 
     , 20000 - NVL(amt, 20000 * rat / 100) rem
  FROM t
 WHERE seq = 1
 UNION ALL
SELECT a.seq, a.amt, a.rat
     , b.rem prc
     , NVL(a.amt, b.rem * a.rat / 100) dc 
     , b.rem - NVL(a.amt, b.rem * a.rat / 100) rem
  FROM t  a
     , t1 b
 WHERE a.seq = b.seq + 1
)
SELECT * FROM t1
;

<리스트 6>은 오라클 11G의 새로운 기능인 재귀쿼리(Recursive SQL)를 이용하여 문제를 해결한 것입니다.