문제

• [리스트 1] 원본리스트(전기요금표)

• CREATE TABLE 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;
    

• [표 1] 원본테이블(전기요금표)

• 시작구간(kWh) 종료구간(kWh) 기본요금(원) 전력량요금(원/kWh)  
           S          E         V1         V2
  ---------- ---------- ---------- ----------
           0        100        410       60.7
         100        200        910      125.9
         200        300       1600      187.9
         300        400       3850      280.6
         400        500       7300      417.7
         500       9999      12940      709.5
    

• [리스트 2] 원본리스트(전기사용량)

• CREATE TABLE 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;
    

• [표 2] 원본테이블(전기사용량)

• 아이디 전력사용량(kWh)
    ID        KWH
  ---- ----------
     1         90
     2        120
     3        240
     4        360
     5        480
     6        600
    

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

• [표 3] 결과테이블(전기요금)

•        ID        KWH        AMT
  --------- ---------- ----------
          1         90       5870
          2        120       9490
          3        240      27770
          4        360      58130
          5        480     106220
          6        600     191170
    

문제설명

<표 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] 사용량에 따른 구간 검색1

•       ID        KWH          S          E         V1         V2
  -------- ---------- ---------- ---------- ---------- ----------
         1         90          0        100        410       60.7
         2        120        100        200        910      125.9
         3        240        200        300       1600      187.9
         4        360        300        400       3850      280.6
         5        480        400        500       7300      417.7
         6        600        500       9999      12940      709.5
    

<리스트 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
  ;
    

• [표 5] 사용량에 따른 구간 검색2

•      ID        KWH          S          E         V1         V2
  ------- ---------- ---------- ---------- ---------- ----------
        1         90          0        100        410       60.7
        2        120        100        200        910      125.9
        2        120          0        100        410       60.7
        3        240        200        300       1600      187.9
        3        240          0        100        410       60.7
        3        240        100        200        910      125.9
        4        360        200        300       1600      187.9
        4        360          0        100        410       60.7
        4        360        100        200        910      125.9
        4        360        300        400       3850      280.6
        5        480        100        200        910      125.9
        5        480        200        300       1600      187.9
        5        480        300        400       3850      280.6
        5        480        400        500       7300      417.7
        5        480          0        100        410       60.7
        6        600        100        200        910      125.9
        6        600        200        300       1600      187.9
        6        600        300        400       3850      280.6
        6        600        400        500       7300      417.7
        6        600        500       9999      12940      709.5
        6        600          0        100        410       60.7
  

<리스트 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] 사용량을 구간별로 나누기

•      ID        KWH          V         V1         V2
  ------- ---------- ---------- ---------- ----------
        1         90         90        410       60.7
        2        120        100        410       60.7
        2        120         20        910      125.9
        3        240        100        410       60.7
        3        240        100        910      125.9
        3        240         40       1600      187.9
        4        360        100        410       60.7
        4        360        100        910      125.9
        4        360        100       1600      187.9
        4        360         60       3850      280.6
        5        480        100        410       60.7
        5        480        100        910      125.9
        5        480        100       1600      187.9
        5        480        100       3850      280.6
        5        480         80       7300      417.7
        6        600        100        410       60.7
        6        600        100        910      125.9
        6        600        100       1600      187.9
        6        600        100       3850      280.6
        6        600        100       7300      417.7
        6        600        100      12940      709.5
    

<리스트 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] LEAST 함수 이용

•       ID        KWH          V         V1         V2
  -------- ---------- ---------- ---------- ----------
         1         90         90        410       60.7
         2        120        100        410       60.7
         2        120         20        910      125.9
         3        240        100        410       60.7
         3        240        100        910      125.9
         3        240         40       1600      187.9
         4        360        100        410       60.7
         4        360        100        910      125.9
         4        360        100       1600      187.9
         4        360         60       3850      280.6
         5        480        100        410       60.7
         5        480        100        910      125.9
         5        480        100       1600      187.9
         5        480        100       3850      280.6
         5        480         80       7300      417.7
         6        600        100        410       60.7
         6        600        100        910      125.9
         6        600        100       1600      187.9
         6        600        100       3850      280.6
         6        600        100       7300      417.7
         6        600        100      12940      709.5
  

<리스트 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] GROUP BY

•     ID        KWH         V1         V2
  ------ ---------- ---------- ----------
       1         90        410       5463
       2        120        910       8588
       3        240       1600      26176
       4        360       3850      54286
       5        480       7300      98926
       6        600      12940     178230
  

<리스트 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함수를 이용하는 팁 등을 배웠습니다.