[표준SQL] 퀴즈로 배우는 SQL : 전기 요금 계산

퀴즈로 배우는 SQL : 전기 요금 계산

퀴즈로 배우는 SQL

전기 요금 계산

이번 퀴즈로 배워보는 SQL 시간에는 전기 사용량에 따라 전기요금표 테이블을 이용해 전기요금을 계산하는 쿼리 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

[문제]

<리스트 1> 원본리스트(전기요금표)
WITH code_t AS
(
SELECT 0 s, 100 e, 410 v1, 60.7 v2 FROM dual
UNION ALL SELECT 100, 200, 910, 125.9 FROM dual
UNION ALL SELECT 200, 300, 1600, 187.9 FROM dual
UNION ALL SELECT 300, 400, 3850, 280.6 FROM dual
UNION ALL SELECT 400, 500, 7300, 417.7 FROM dual
UNION ALL SELECT 500, 9999, 12940, 709.5 FROM dual
)
SELECT * FROM code_t;

<리스트 2> 원본리스트(전기사용량)
WITH use_t AS
(
SELECT 1 id, 90 kwh FROM dual
UNION ALL SELECT 2, 120 FROM dual
UNION ALL SELECT 3, 240 FROM dual
UNION ALL SELECT 4, 360 FROM dual
UNION ALL SELECT 5, 480 FROM dual
UNION ALL SELECT 6, 600 FROM dual
)
SELECT * FROM use_t;

<표 1>의 전기요금표를 이용해 <표 2> 전기사용량의 전기요금을 계산하는 SQL을 작성하세요


 

[문제설명]

<표 1>은 전기요금표 테이블입니다. 전기 사용량별 기본요금과 구간별 전력량요금이 저장된 테이블입니다. <표 2>는 사용자별 전기사용량이 저장된 테이블입니다. <표 2>의 각 사용자별 전기사용량에 대해서 <표 1>의 전기요금표를 참조해 전기요금을 계산하는 문제입니다.

전기요금은 기본요금과 전력량요금으로 나누어 집니다. 기본요금은 사용량에 따라 부과됩니다. 사용량의 구간에 따라 요금 단가가 높아지는 누진제가 적용됩니다.

예를 들면 ID 3번의 사용량 240에 대해서는 <표 1> 전기요금표의 세 번째 구간(200~300)의 요금을 적용해 기본요금은 1600원이 됩니다. 전력량요금은 기본요금처럼 한가지로 정해지는게 아닙니다. 사용량 100 kWh 구간 마다 요금이 다르게 산정됩니다. 마찬가지로 ID 3번의 사용량 240에 대해서 처음 100kWh 까지는 첫 번째 구간 요금인 60.7원이 적용됩니다. 다음 100 kWh 까지는 두 번째 구간 요금인 125.9원이 적용됩니다. 다음 100 kWh까지는 세 번째 구간 요금인 187.9원이 적용됩니다. 전령량요금을 계산해보면

100 kWh * 60.7 원 = 6070 원
100 kWh * 125.9 원 = 12590 원
40 kWh * 187.9 원 = 7516 원

구간별 요금을 합산해보면 6070 + 12590 + 7516 = 26176원이 됩니다. ID 3번의 사용량 240 kWh에 대한 최종 전기요금은 기본요금 1600원에 전력량요금 26176원을 합산해 27776원이 되며 마지막으로 10원단위 절사한 금액 27770원이 최종 전기요금이 됩니다.

[정답]

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

<리스트 3> 정답 리스트
SELECT u.id
, u.kwh
, TRUNC( MAX(v1)
+ SUM((LEAST(u.kwh, c.e) - c.s) * v2)
, -1) amt
FROM use_t u
, code_t c
WHERE c.s < u.kwh
GROUP BY u.id, u.kwh
ORDER BY id
;

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

[해설]

이번 문제는 전기요금을 계산하는 문제입니다. 각 사용자별 사용량에 따른 전기요금을 전기요금표에서 찾아야 하는데요. 두 테이블을 연결하기 위해서는 조인을 해야 하겠죠. 일반적인 이퀄(=)조인을 할 수 없는 상황입니다. 구간 검색을 위한 조인 조건이 필요한 상황입니다.

<리스트 4> 사용량에 따른 구간 검색1
SELECT *
FROM use_t u
, code_t c
WHERE u.kwh > c.s
AND u.kwh < = c.e
ORDER BY id
;

<리스트 4>의 쿼리를 수행해 <표 4>의 결과를 얻었습니다. 조인 조건으로 전기요금표의 시작과 종료구간 사이에 사용량이 위치하는지를 체크했습니다.

<표 4>의 결과를 보면 ID 3에 대한 기본요금은 적절하게 조인이 됐습니다. 그러나 전력량 요금은 어떤가요 세 번째 구간의 요금만 연결이 됐습니다. 앞서 문제 설명의 요금처럼 적용되려면 첫 번째, 두 번째와도 연결이 돼야만 합니다.

<리스트 5> 사용량에 따른 구간 검색2
SELECT *
FROM use_t u
, code_t c
WHERE u.kwh > c.s
-- AND u.kwh < = c.e
ORDER BY id
;

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

<리스트 5>의 쿼리를 수행해 <표 5>의 결과를 얻었습니다. 이번에는 검색 조건중 하나를 제거했습니다. 시작과 종료 구간 사이를 체크하는 것이 아닌 시작이 사용량보다 작은지 여부만을 체크했습니다. 사용량 보다 적은 하위 구간과 모두 연결하는 방식이 됐습니다.

이번에는 사용량을 구간별로 나누어 단가와 곱해줘야 하는데요. 사용량을 구간별로 나누려면 어떻게 해야 할까요 ID 3번의 사용량 240을 예를 들면. 100 - 0 = 100, 두 번째 값 100도 마찬가지로 종료 - 시작인 200 - 100이 됩니다.

세 번째는 조금 다르죠. 종료 - 시작이 아닌 사용량 - 시작인 240 - 200 = 40 이 됩니다. 여기서 두가지 계산방식이 나오는데요. 하나는 구간종료값 - 구간시작값이고요. 하나는 전기사용량 - 구간시작값입니다.

<리스트 6> 사용량을 구간별로 나누기
SELECT u.id
, u.kwh
, CASE WHEN u.kwh < = c.e
THEN u.kwh - c.s
ELSE c.e - c.s
END v
, c.v1
, c.v2
FROM use_t u
, code_t c
WHERE u.kwh > c.s
-- AND u.kwh < = c.e
ORDER BY id, s
;

<리스트 6>의 쿼리를 수행해 <표 6>의 결과를 얻었습니다. 사용량을 구간별로 나누었습니다. 하위 구간과 모두 조인하기 위해 주석처리했던 조건이 SELECT 절의 CASE WHEN 절의 조건으로 사용했습니다.

사용량이 종료보다 작다면 (사용량 - 시작)으로 그렇지 않다면 (종료 - 시작)이 됩니다. 올바른 결과가 나왔지만 CASE 구문이 조금은 복잡해 보입니다. 두가지 계산식을 비교해 보면 차감되는 시작값은 동일하지만 앞의 값은 다르죠 사용량과 종료값의 선택 기준은 두 값 중 더 작은 값이 오게 된다는 것을 알 수 있습니다.

수식으로 표현하면 다음과 같습니다.

구간별 사용량 = 작은 값(사용량, 종료) - 시작

더 작은 값을 구하는 함수 LIST를 이용해 CASE 문을 간략화하겠습니다.

<리스트 7> LEAST 함수 이용
SELECT u.id
, u.kwh
, LEAST(u.kwh, c.e) - c.s v
, c.v1
, c.v2
FROM use_t u
, code_t c
WHERE u.kwh > c.s
-- AND u.kwh < = c.e
ORDER BY id, s
;

<리스트 7>의 쿼리를 통해 같은 결과를 얻었습니다. 이제는 구간별로 나누어진 값을 ID별로 하나로 합쳐야 할 때입니다. GROUP BY 와 집계함수를 이용해 하나로 합쳐보겠습니다.

<리스트 8> GROUP BY
SELECT u.id
, u.kwh
, MAX(v1) v1
, SUM((LEAST(u.kwh, c.e) - c.s) * v2) v2
FROM use_t u
, code_t c
WHERE c.s < u.kwh
GROUP BY u.id, u.kwh
ORDER BY id
;

<리스트 8>의 쿼리를 수행해 <표 8>의 결과를 얻었습니다. 기본요금과 전력량요금을 집계하는 집계함수가 다르게 사용됐습니다. 기본요금은 ID별로 하나만 적용돼야 합니다. 가장 큰 구간의 값을 적용해야 하므로 MAX 함수를 사용했습니다.

반면에 전력량요금은 각 구간별로 다르게 적용해 계산해야 하므로 <리스트 7>에서 구한 구간별 계산식의 값을 SUM 함수를 이용해 합산했습니다. 각 사용자별 기본요금과 전력량요금을 계산했습니다. 이제 두 값을 더해주면 정답리스트가 완성이 됩니다.

<리스트 9> 정답 리스트
SELECT u.id
, u.kwh
, TRUNC( MAX(v1)
+ SUM((LEAST(u.kwh, c.e) - c.s) * v2)
, -1) amt
FROM use_t u
, code_t c
WHERE c.s < u.kwh
GROUP BY u.id, u.kwh
ORDER BY id
;

<리스트 9> 정답 리스트가 완성됐습니다. 10원미만 절사를 위해 TRUNC 함수를 사용했습니다. TRUNC 함수의 두 번째 인자로 음수가 오는 것이 특이합니다. 양수는 소수점 이하 자리수를 의미하며 음수는 반대로 수수점 앞 자리수를 의미합니다. 이번 시간에는 누진제를 적용한 전기요금 계산문제를 간단하게 풀어보았습니다. 부등호 조인을 이용해 두 테이블을 연결하는 방법, CASE 문 대신 LEAST함수를 이용하는 팁 등을 배웠습니다.

출처 : 마이크로소프트웨어 1월호

제공 : 데이터전문가 지식포털 DBguide.net

[출처] https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?pageid=16&mod=document&uid=235885