[Oracle][Programmers] 자동차 대여 기록 별 대여 금액 구하기

1. 문제

프로그래머스

CAR_RENTAL_COMPANY_CAR 테이블

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블

총 3개의 테이블이 등장하니 각 테이블에서 얻을 수 있는 정보를 잘 분석해야 한다.

 

자동차 종류(CAR_TYPE)가 '트럭'인 자동차의 대여 기록에 대해 대여 기록 별로 대여 금액(컬럼명 : FEE)을 구하여 대여 기록 ID(HISTORY_ID)와 대여 금액(FEE) 리스트를 출력하는 문제. 결과는 대여 금액(FEE)을 기준으로 내림차순 정렬, 대여 금액이 같다면 대여 기록 ID(HISTORY_ID)를 기준으로 내림차순 정렬.

 

2. 풀이

• 자동차 종류가 트럭 : WHERE car_type = '트럭'
• 대여 기록별 대여 금액 : 왠지 GROUP BY를 써야 할 것 같지만 동일한 HISTORY_ID를 갖는 행은 존재하지 않았다. 그래서 단순 연산만 하면 된다.
  • 대여 기간이 7일 미만 : daily_fee * (end_date - start_date + 1)
  • 대여 기간이 7일 이상 : daily_fee * (end_date - start_date + 1) * {7일 이상의 할인율}
  • 대여 기간이 30일 이상 : daily_fee * (end_date - start_date + 1) * {30일 이상의 할인율}
  • 대여 기간이 90일 이상 : daily_fee * (end_date - start_date + 1) * {90일 이상의 할인율}
• 대여 금액 기준 내림차순, 대여 기록 ID 기준 내림차순 : ORDER BY fee DESC, history_id DESC

2-1. 각 대여 기록의 대여 기간 구하기

대여 기간별로 적용되는 할인율이 다르므로 대여 기간을 구해야 한다. 이때 자동차의 일일 대여 요금(daily_fee)도 같이 조회해야 하므로 아래 두 테이블을 JOIN한다. 대여 기간은 대여 종료일 - 대여시작일에 1을 더한 것이라는걸 기억해야 한다.

SELECT c.car_type, 
       c.daily_fee, 
       r.history_id, 
       (r.end_date - r.start_date + 1) AS rent_days,
       CASE 
           WHEN (r.end_date - r.start_date + 1) < 7 THEN ''
           WHEN (r.end_date - r.start_date + 1) < 30 THEN '7일 이상'
           WHEN (r.end_date - r.start_date + 1) < 90 THEN '30일 이상'
           ELSE '90일 이상'
       END AS duration_type 
FROM car_rental_company_car c
JOIN car_rental_company_rental_history r 
  ON c.car_id = r.car_id

CASE WHEN 구문이 실행되는 순서를 사용하여 각 대여 기간별 조건이 매치되도록 작성하였다. 만약 대여 기간이 2일이라면 첫 번째 WHEN절에서 걸리고, 10일이라면 두 번째 WHEN절에서 걸리는 것처럼 동작하기 때문이다.

2-2. 각 대여 기간에 해당하는 할인율 적용하여 대여 금액 구하기

SELECT a.history_id,
       a.daily_fee * a.rent_days * NVL2(d.discount_rate, (100-d.discount_rate)/100, 1) AS fee
FROM (
    /*
     * 대여 기록별 자동차 대여 요금, 대여일, 대여 기록 ID를
     * 조회하는 인라인뷰
     */
) a
LEFT OUTER JOIN car_rental_company_discount_plan d
             ON a.car_type = d.car_type
            AND a.duration_type = d.duration_type 
WHERE a.car_type = '트럭'
ORDER BY fee DESC, history_id DESC;

car_rental_company_discount_plan 테이블과 LEFT OUTER JOIN을 하고 있는데 이는 7일 미만의 경우는 할인율이 없으므로 INNER JOIN을 하면 해당 기간의 대여 기록 데이터는 필터링되기 때문이다. 

LEFT OUTER JOIN을 했으므로 7일 미만의 경우는 할인율(discount_rate)이 NULL이다. 그러므로 NVL2( ) 함수를 사용하여 대여 기록별 대여 요금을 구해주었다.

2-3. 최종 쿼리💡

2-1번 쿼리를 2-2의 인라인뷰에 넣어주면 최종 쿼리다.

SELECT a.history_id,
       a.daily_fee * a.rent_days * NVL2(d.discount_rate, (100-d.discount_rate)/100, 1) AS fee
FROM (
    SELECT c.car_type, 
           c.daily_fee, 
           r.history_id, 
           (r.end_date - r.start_date + 1) AS rent_days,
           CASE 
               WHEN (r.end_date - r.start_date + 1) < 7 THEN ''
               WHEN (r.end_date - r.start_date + 1) < 30 THEN '7일 이상'
               WHEN (r.end_date - r.start_date + 1) < 90 THEN '30일 이상'
               ELSE '90일 이상'
           END AS duration_type 
    FROM car_rental_company_car c
    JOIN car_rental_company_rental_history r 
      ON c.car_id = r.car_id
) a
LEFT OUTER JOIN car_rental_company_discount_plan d
             ON a.car_type = d.car_type
            AND a.duration_type = d.duration_type 
WHERE a.car_type = '트럭'
ORDER BY fee DESC, history_id DESC;