이번 퀴즈로 배워보는 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
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
이거 푸신분 있나요...
이거 너무어려워요
이거랑 좀더 쉬운 비슷한거 문제나 설명같은거좀 해주실분 ㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.