[프로그래머스] SQL 자동차 대여 기록 별 대여 금액 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

CAR_RENTAL_COMPANY_CAR -- 품목 테이블 

CAR_RENTAL_COMPANY_RENTAL_HISTORY -- 대여 이력 테이블

CAR_RENTAL_COMPANY_DISCOUNT_PLAN -- 대여 이력 할인율 테이블

 

자동차 종류가 트럭인 자동차의 대여 기록 별 대여 금액을 구하는 문제였다. 

 

처음에는 아래와 같이 풀었다. 

 

SELECT A.HISTORY_ID
        ,CASE 
         WHEN (A.END_DATE - A.START_DATE + 1) >= 90 
         THEN FLOOR((A.END_DATE - A.START_DATE + 1) * (B.DAILY_FEE * COALESCE((100 - RTRIM(E.DISCOUNT_RATE, '%'))/ 100, 1)))
         WHEN (A.END_DATE - A.START_DATE + 1) >= 30 
         THEN FLOOR((A.END_DATE - A.START_DATE + 1) * (B.DAILY_FEE * COALESCE((100 - RTRIM(D.DISCOUNT_RATE, '%'))/ 100 , 1)))
         WHEN (A.END_DATE - A.START_DATE + 1) >= 7 
         THEN FLOOR((A.END_DATE - A.START_DATE + 1) * (B.DAILY_FEE * COALESCE((100 - RTRIM(C.DISCOUNT_RATE, '%'))/ 100, 1)))
         ELSE FLOOR((A.END_DATE - A.START_DATE + 1) * B.DAILY_FEE)
         END FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
INNER JOIN CAR_RENTAL_COMPANY_CAR B ON A.CAR_ID = B.CAR_ID 
LEFT OUTER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C ON B.CAR_TYPE = C.CAR_TYPE 
                                                   AND SUBSTR(C.DURATION_TYPE, 0, 1) = '7' 
LEFT OUTER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON B.CAR_TYPE = D.CAR_TYPE 
                                                   AND SUBSTR(D.DURATION_TYPE, 0, 2) = '30' 
LEFT OUTER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN E ON B.CAR_TYPE = E.CAR_TYPE 
                                                   AND SUBSTR(E.DURATION_TYPE, 0, 2) = '90'                   
WHERE B.CAR_TYPE = '트럭'
ORDER BY 2 DESC, A.HISTORY_ID DESC

 

딱 봐도 아주 지저분하고 할인율 구간이 변경 되면 쿼리도 바꿔야 하는 별로 좋지 못한 쿼리이다. 

정말 딱 테스트 케이스 통과용 쿼리..!

 

7, 30, 90 일 이상 중 어느 구간에 해당하는지 CASE 문을 통해 확인 하고 

테스트 데이터의 경우 오래 빌릴 수록 높은 할인율을 주고 있어서 역순으로 확인했다. 

구간 별로 LEFT OUTER JOIN을 걸어줘서 쉽게 할인율을 구했다. 

 

그나저나 할인율, 할인율기준에 문자 왜 넣었는지 모르겠다... 이것도 제거할 수 있나 보려고 한 건가?

하여튼 내가 데이터 형식 정할 수 있었으면 그냥 숫자만 받아서 넣었을 것 같다. 

 

SELECT A.HISTORY_ID
      ,FLOOR((A.END_DATE - A.START_DATE + 1) * (B.DAILY_FEE * (100 - (
                    SELECT COALESCE(MAX(TO_NUMBER(RTRIM(D.DISCOUNT_RATE, '%'))), 0)
                    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN D 
                    WHERE D.CAR_TYPE = B.CAR_TYPE
                    AND (A.END_DATE - A.START_DATE + 1) >= REGEXP_REPLACE(D.DURATION_TYPE, '[^0-9]') 
                ))/ 100)) FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
INNER JOIN CAR_RENTAL_COMPANY_CAR B ON A.CAR_ID = B.CAR_ID    
WHERE B.CAR_TYPE = '트럭'
ORDER BY 2 DESC, A.HISTORY_ID DESC

 

구간을 일일히 쓰지 않고 테이블에서 가져다가 쓰는 쿼리로 수정했다. 

이렇게 수정한 것도 스칼라 쿼리이긴 하지만 조건을 충족하는 할인율 데이터가 그렇게 많이 있을 것 같진 않아서 

이 정도로 만족하기로 했다. 

 

이렇게 쿼리를 짰을 때 발생할 수 잇는 오류는, 

현재 할인율이 장기로 빌릴수록 높기 때문에 MAX(DISCOUNT_RATE)를 써도 원하는 결과가 나오지만 아닌 경우에는 

기간을 구하고 MAX(DURATION_TYPE)을 사용하여 결과를 구해야 할 것 같다. 

 

* 테이블에 할인율이 적용되는 기간이 없다.

아마 실제로는 할인율이 적용될 수 있는 기간도 정해져 있을 것이다. 

스칼라 쿼리 부분에 적용기간 조건을 하나 더 걸어주면 해결 된다.