퀴즈로 배우는 SQL
[퀴즈] 이용요금에 대한 납부현황 구하기 0 1 99,999+

by 마농 누적금액 LEAST MONTHS_BETWEEN 요금납부 [2015.06.26]


이번 퀴즈로 배워보는 SQL 시간에는 이용요금과 납부내역을 서로 연결해 납부현황표를 보여주는 SQL을 작성하는 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.

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

문제

다음과 같이 매월 이용자별로 부과되는 이용료 집합인 [표 1]과 이용자별 납부내역인 [표 2]가 있습니다. 이 두 집합을 연결해 [표 3]과 같은 결과를 만들어내는 SQL을 작성하세요.

  • [리스트 1] 원본 리스트 - 요금
 CREATE TABLE 요금 AS
 WITH 요금 AS
 (
 SELECT '001' u_id, '201201' ym, 200 p_amt FROM dual
 UNION ALL SELECT '001', '201202', 200 FROM dual
 UNION ALL SELECT '001', '201203', 200 FROM dual
 UNION ALL SELECT '001', '201204', 200 FROM dual
 UNION ALL SELECT '001', '201205', 200 FROM dual
 UNION ALL SELECT '002', '201202', 300 FROM dual
 UNION ALL SELECT '002', '201203', 300 FROM dual
 UNION ALL SELECT '002', '201204', 300 FROM dual
 UNION ALL SELECT '002', '201205', 300 FROM dual
 )
 SELECT * FROM 요금;

  • [표 1] 원본 테이블 - 요금
  • U_I YM          P_AMT
    --- ------ ----------
    001 201201        200
    001 201202        200
    001 201203        200
    001 201204        200
    001 201205        200
    002 201202        300
    002 201203        300
    002 201204        300
    002 201205        300
      

  • [리스트 2] 원본 리스트 - 납부
CREATE TABLE 납부 AS
WITH 납부 AS
(
SELECT '001' u_id, '201203' ym, 300 i_amt FROM dual
UNION ALL SELECT '001', '201204', 400 FROM dual
UNION ALL SELECT '002', '201203', 400 FROM dual
UNION ALL SELECT '002', '201205', 500 FROM dual
UNION ALL SELECT '002', '201210', 100 FROM dual
UNION ALL SELECT '002', '201212', 200 FROM dual
)
SELECT * FROM 납부;

  • [표 2] 원본 테이블 - 납부
  • U_I YM          I_AMT
    --- ------ ----------
    001 201203        300
    001 201204        400
    002 201203        400
    002 201205        500
    002 201210        100
    002 201212        200
      

  • [표 3] 결과 테이블
  • U_ID   YM         P_AMT    M00     M01    M02     M_ETC   납부총액       잔액
    ------ -------- ------- ------ ------- ------ --------- ---------- ----------
    001    201201       200                   200                  200          0
    001    201202       200            100    100                  200          0
    001    201203       200            200                         200          0
    001    201204       200    100                                 100        100
    001    201205       200                                          0        200
    002    201202       300            300                         300          0
    002    201203       300    100            200                  300          0
    002    201204       300            300                         300          0
    002    201205       300                             300        300          0
      

문제설명

이 문제는 이용료와 납부액의 관계를 보여주는 문제입니다. 이용요금은 이용자별로 매월 한 번씩 부과됩니다. 하지만 모든 이용자가 이용요금을 매월 납입하지는 않습니다. 또한 몇 달치 요금을 한꺼번에 몰아서 납부하기도 합니다. [표 1]의 이용요금에 대해 [표 2]와 같이 납입했을 때, 이용자와 요금부과월별로 납부내역과 잔액을 표시해야 합니다.

또한 이용요금에 대한 납부가 요금부과 당월에 이루어진 경우, 1개월 후 납부한 경우, 2개월 후에 납부한 경우, 3개월 이상 지난 후에 납부한 경우를 각각 금액별로 별도 항목으로 표시하고 납부 총액과 잔액을 표시해야 합니다. [표 3]을 보면서 좀더 자세히 설명해보죠. 이용고객 001은 2012년 1월부터 5개월간 200원의 요금고지를 받았습니다. 이에 대한 요금납부는 3월에 300원과 4월에 400원씩 총 2회 납부했습니다. 여기서 3월에 납부한 300원 중 200원은 1월 요금에 대한 납부액이며, 나머지 100원은 2월 요금에 대한 납부액으로 순차적으로 차감되게 됩니다.

이때 1월 요금에 대한 납부액 200원은 3월에 납부한 금액이므로 요금부가월인 1월과는 2개월 차이가 납니다. 따라서 2개월 후 납부액에 해당하는 컬럼(M02)에 표시됩니다. 마찬가지로 3월 납부액 중 1월 요금 200원을 제외하고 남은 100원은 2월 요금으로 납부하므로 해당 금액은 1개월 후에 납부한 것으로 간주돼 M01 컬럼에 표시합니다.

이로써 3월 납부액 300원을 모두 소진했고 이후 5월에 납부한 금액 400원 중 2월 요금의 잔액 100원을 납부하고, 남은 300원 중 200원을 3월 요금에서 제합니다. 그리고 또 다시 남은 100원을 4월 요금에서 제하는 방식입니다. 결국 4월 요금인 200원 중 100원만 납부했으므로 4월 요금에 대한 납부총액은 100원이기 때문에 잔액은 100원으로 표시합니다.

정답

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

  • [리스트 3] 정답 리스트
SELECT u_id, ym, p_amt
     , SUM(DECODE(m, 0, v)) m00
     , SUM(DECODE(m, 1, v)) m01
     , SUM(DECODE(m, 2, v)) m02
     , SUM(CASE WHEN m >= 3 THEN v END) m_etc
     , NVL(SUM(v), 0) 납부총액
     , p_amt - NVL(SUM(v), 0) 잔액
  FROM (SELECT a.u_id, a.ym, a.p_amt
             , MONTHS_BETWEEN( TO_DATE(b.ym, 'yyyymm')
                             , TO_DATE(a.ym, 'yyyymm')
                             ) m
             , LEAST( p_amt
                    , i_amt
                    , p_amt_s - i_amt_s + i_amt
                    , i_amt_s - p_amt_s + p_amt
                    ) v
          FROM (SELECT u_id, ym, p_amt
                     , SUM(p_amt) OVER(
                       PARTITION BY u_id
                       ORDER BY ym) p_amt_s
                  FROM 요금
                ) a
             , (SELECT u_id, ym, i_amt
                     , SUM(i_amt) OVER(
                       PARTITION BY u_id
                       ORDER BY ym) i_amt_s
                  FROM 납부
                ) b
         WHERE a.u_id = b.u_id(+)
           AND a.p_amt_s - a.p_amt < b.i_amt_s(+)
           AND a.p_amt_s > b.i_amt_s(+) - b.i_amt(+)
        )
 GROUP BY u_id, ym, p_amt
 ORDER BY u_id, ym
;

해설

이번 문제는 불규칙적인 납부금액을 적절히 나눠 부과한 요금을 차감하는 것입니다. 참 난해하죠? 서로 교차해 차감하다 보면 요금이 남는 경우도 있고, 납부금액이 남는 경우도 있습니다.

이런 두 집합을 연결하기 위해서는 연결고리가 필요한 데요. 눈에 보이는 연결고리는 단지 이용자ID 외엔 없습니다. 그리고 이용자ID 만으로 조인 조건을 준다면 카티션 프러덕트(Cartesian product)가 발생해 원하지 않는 결과가 나오겠지요?

결국 우리는 가상의 연결고리를 만들어 조인 조건으로 사용해야만 합니다. 좀더 자세히 설명하면, 이용요금은 매월 누적이 되니까 납부액도 납부한 만큼 누적될 것입니다.

결국 누적된 이용금액에서 납부한 누적금액을 빼면 결과는 현재 잔액이 되겠네요. 이러한 누적금액을 조인 키로 이용할 수 있지 않을까요? 우선 분석함수를 이용해 누적금액을 구해보겠습니다.

  • [리스트 4] 이용 누적금액
  • SELECT u_id, ym, p_amt
         , SUM(p_amt) OVER(
           PARTITION BY u_id
           ORDER BY ym) p_amt_s
      FROM 요금
    ;
      

  • [표 4] 이용 누적금액
  • U_ID   YM                P_AMT    P_AMT_S
    ------ ------------ ---------- ----------
    001    201201              200        200
    001    201202              200        400
    001    201203              200        600
    001    201204              200        800
    001    201205              200       1000
    002    201202              300        300
    002    201203              300        600
    002    201204              300        900
    002    201205              300       1200
      

  • [리스트 5] 납부 누적금액
  • SELECT u_id, ym, i_amt
         , SUM(i_amt) OVER(
           PARTITION BY u_id
           ORDER BY ym) i_amt_s
      FROM 납부
    ;
      

  • [표 5] 납부 누적금액
  • U_ID   YM                I_AMT    I_AMT_S
    ------ ------------ ---------- ----------
    001    201203              300        300
    001    201204              400        700
    002    201203              400        400
    002    201205              500        900
    002    201210              100       1000
    002    201212              200       1200
      

이렇게 구한 월별 누적금액을 어떻게 이용해야 연결고리를 찾을 수 있을까요? From ~ To 구간을 가진 선분 이력끼리의 조인 조건을 생각하면 됩니다. 구간끼리의 조인은 다음과 같이 합니다.

WHERE a.시작 < b.종료
  AND a.종료 > b.시작

맞습니다. 시작지점과 종료지점을 서로 교차해 비교하는 것이죠. 이를 이용요금과 납부금액에 적용하면 다음과 같습니다.

WHERE 전월까지 요금 누적금액(요금시작) < 납부 누적금액(납부종료)
  AND 요금 누적금액(요금종료) > 전월까지 납부 누적금액(납부시작)

한글로 표시된 부분을 [표 4]와 [표 5]에서 구한 I_AMT_S와 P_AMT_S를 이용해 바꾸면 다음과 같습니다.

WHERE a.p_amt_s - a.p_amt < b.i_amt_s
  AND a.p_amt_s > b.i_amt_s - b.i_amt

세부항목들을 살펴보죠. 우선 a.p_amt_s :는 이용요금 누적금액입니다. 그리고 a.p_amt_s - a.p_amt :는 이용요금 누적금액에서 당월 요금을 차감한 것으로 전월까지의 누적금액을 의미합니다.

한편 b.i_amt_s : 납부 누적금액입니다. 그리고 b.i_amt_s - b.i_amt :는 납부 누적금액에서 당월 납부한 금액을 차감한 것, 즉 전월까지의 누적금액을 의미합니다. 이 연결조건을 적용한 결과는 어떻게 나타났을까요?

  • [리스트 6] 요금과 납부를 조인
  • SELECT *
      FROM (SELECT u_id, ym, p_amt
                 , SUM(p_amt) OVER(
                   PARTITION BY u_id
                   ORDER BY ym) p_amt_s
              FROM 요금
            ) a
         , (SELECT u_id, ym, i_amt
                 , SUM(i_amt) OVER(
                   PARTITION BY u_id
                   ORDER BY ym) i_amt_s
              FROM 납부
            ) b
     WHERE a.u_id = b.u_id(+)
       AND a.p_amt_s - a.p_amt < b.i_amt_s(+)
       AND a.p_amt_s > b.i_amt_s(+) - b.i_amt(+)
     ORDER BY a.u_id, a.ym, b.ym
    ;
      

  • [표 6] 요금과 납부를 조인
  • U_ID   YM                P_AMT    P_AMT_S U_ID   YM                I_AMT    I_AMT_S
    ------ ------------ ---------- ---------- ------ ------------ ---------- ----------
    001    201201              200        200 001    201203              300        300
    001    201202              200        400 001    201203              300        300
    001    201202              200        400 001    201204              400        700
    001    201203              200        600 001    201204              400        700
    001    201204              200        800 001    201204              400        700
    001    201205              200       1000
    002    201202              300        300 002    201203              400        400
    002    201203              300        600 002    201203              400        400
    002    201203              300        600 002    201205              500        900
    002    201204              300        900 002    201205              500        900
    002    201205              300       1200 002    201210              100       1000
    002    201205              300       1200 002    201212              200       1200
      

아직 납부되지 않은 요금도 보여주기 위해 아우터 조인을 이용했습니다. [표 6]을 보면 3월 납부액 300원이 1, 2월 요금과 연결됐고, 2월 요금은 3, 4월 납부액과 연결돼 있음을 확인할 수 있습니다. 역시 4월 납부액 400원은 3, 4, 5월 요금과 연결됐네요.

우리가 원하던 연결고리를 드디어 찾았습니다. Where 절을 완성했으니 이제 남은 것은 Select 절에서 원하는 결과물을 가공하는 일뿐입니다. 1번 행을 보면 요금 200원에 납부 300원입니다.

즉 200원이 납부되고 100원이 남은 거죠. 2번 행에서는 앞서 남은 납부금액 100원과 요금 200원 중 작은 금액인 100원이 납부액이 됩니다. 3번 행에서는 앞서 남은 요금 100원과 납부금액 400원 중 작은 금액인 100원 이 납부액이 됩니다. 4번 행에서는 앞서 남은 납부금액 300원과 요금 200원 중 작은 금액인 200원이 납부액이 됩니다.

이런 식으로 결과자료를 분석해 보면 몇 가지 규칙을 찾을 수 있습니다. 각각의 금액을 비교한 다음 작은 값이 납부액이 됩니다. 서로 값을 비교해 작은 값을 구하는 함수인 LEAST 를 이용해 결과를 구해보죠. 물론 채납 개월 수도 함께 구해보겠습니다.

  • [리스트 7] 납부금액 및 채납 개월 구하기
  • SELECT a.u_id, a.ym, a.p_amt
         , MONTHS_BETWEEN( TO_DATE(b.ym, 'yyyymm')
                         , TO_DATE(a.ym, 'yyyymm')
                         ) m
         , LEAST( p_amt
                , i_amt
                , p_amt_s - i_amt_s + i_amt
                , i_amt_s - p_amt_s + p_amt
                ) v
      FROM (SELECT u_id, ym, p_amt
                 , SUM(p_amt) OVER(
                   PARTITION BY u_id
                   ORDER BY ym) p_amt_s
              FROM 요금
            ) a
         , (SELECT u_id, ym, i_amt
                 , SUM(i_amt) OVER(
                   PARTITION BY u_id
                   ORDER BY ym) i_amt_s
              FROM 납부
            ) b
     WHERE a.u_id = b.u_id(+)
       AND a.p_amt_s - a.p_amt < b.i_amt_s(+)
       AND a.p_amt_s > b.i_amt_s(+) - b.i_amt(+)
     ORDER BY a.u_id, a.ym, b.ym
    ;
      

  • [표 7] 납부금액 및 채납 개월 구하기
  • U_ID   YM                P_AMT          M          V
    ------ ------------ ---------- ---------- ----------
    001    201201              200          2        200
    001    201202              200          1        100
    001    201202              200          2        100
    001    201203              200          1        200
    001    201204              200          0        100
    001    201205              200
    002    201202              300          1        300
    002    201203              300          0        100
    002    201203              300          2        200
    002    201204              300          1        300
    002    201205              300          5        100
    002    201205              300          7        200
      

LEAST 함수를 이용해 납부금액을 구하고 MONTHS_BETWEEN 함수를 이용해 채납 개월 수를 구했습니다.

이제 마지막 단계는 요금내역 별로 납부금액을 집계하는 일입니다. GROUP BY와 SUM, DECODE를 이용해 마지막 단계를 풀면 정답 쿼리가 완성됩니다.

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

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

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

by 고수가되고싶어요 [2017.09.06 18:11:21]

이거 푸신분 있나요...

이거 너무어려워요

이거랑 좀더 쉬운 비슷한거 문제나 설명같은거좀 해주실분 ㅠ

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