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

 

📑 1. 문제설명

---

✏️ 2. 문제 요약

문제에서 주어진 조건

CAR_RENTAL_COMPANY_CAR	CAR_RENTAL_COMPANY_RENTAL_HISTORY	CAR_RENTAL_COMPANY _DISCOUNT_PLAN
대여 중인 자동차들의 정보	자동차 대여 기록 정보	자동차 종류 별 대여 기간 종류 별 할인 정책 정보
CAR_ID,  CAR_TYPE,  DAILY_FEE,  OPTIONS	HISTORY_ID,  CAR_ID,  START_DATE,  END_DATE	PLAN_ID,  CAR_TYPE,  DURATION_TYPE,  DISCOUNT_RATE
- 자동차 ID, - 자동차 종류, - 일일 대여 요금(원), - 자동차 옵션 리스트,	- 자동차 대여 기록 ID, - 자동차 ID - 대여 시작일, - 대여 종료일	- 요금 할인 정책 ID,  - 자동차 종류, - 대여 기간 종류, - 할인율(%)

 

문제 요약

1) 자동차 종류가 '트럭'인 자동차

2) 대여 기록에 대해서, 대여 기록 별로 대여 기록 ID와 대여 금액 리스트를 출력하기

3) 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬

2-1. 대여 기록별 대여 금액을 계산하기 위해 필요한 컬럼 

- 대여 기간 (period): 대여한 일수 
- 일일 요금 (daily_fee): 하루에 청구되는 기본 금액
- 할인율 (discount_rate): 적용 가능한 할인율 (퍼센트 값)

 

자동차 종류별, 기간별로 할인율이 다른데 종류는 이미 '트럭'으로 한정되어 있기 때문에 서브쿼리에서 기간별로 JOIN을 해야 한다. 

 

대여기간 구하기

car_rental_company_rental_history 테이블에서 DATEDIFF() + 1을 통해 대여 기간을 구해야 한다.

 

+ 1을 하는 이유는 빌린 당일에 반납해도 1일 대여로 계산하기 때문이다. 대여 기간을 먼저 구하고 해당 기간을 CASE문으로 (7일 이상, 30일 이상, 90일 이상)으로 구분하는 작업을 통해 대여기간 종류 컬럼인 DURATION_TYPE을 결정할 수 있다.

-> 여기서 구한 값을 WITH VALUE 라는 서브쿼리로 만들고 실제 데이터 검색은 이 서브쿼리 안에서 수행하기

 

따라서 서브쿼리와 실제 쿼리로 두 부분으로 구분해서 SQL문을 작성해야 함.

 

2-2. 서브쿼리(WITH value 구문) 에서 수행하는 작업

car_rental_company_rental_history 테이블의 `end_date`, `start_date` 컬럼을 가져와 DATEDIFF 함수 + 1 해서 대여 기간(period)을 계산한다.

 

이 값으로 CASE문을 분기해서 DURATION_TYPE 결정하기 (별칭 history)

 

위에서 필터링한 history와 car_rental_company_car 테이블을 car.car_id = history.car_id로 JOIN 한다.

 

자동차 종류가 '트럭'인 자동차만 필터링한다. 

WITH VALUE AS (
    SELECT CAR.DAILY_FEE, CAR.CAR_TYPE, HISTORY.HISTORY_ID,
           DATEDIFF(END_DATE, START_DATE) + 1 AS PERIOD,
           CASE 
             WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
             WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
             WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
             ELSE '' 
           END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY
    INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR ON CAR.CAR_ID = HISTORY.CAR_ID
    WHERE CAR.CAR_TYPE = '트럭')

 

2-3. 실제 SELECT 절에서 수행하는 작업

위에서 구한 WITH VALUE 의 결과를 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블과CAR_TYPE, DURATION_TYPE 컬럼으로 조인해서 최종 대여금액을 계산하고 정렬하기

 

대여 금액 계산하는 법

일일 요금 × 대여 기간 × (100% - 할인율)

value.daily_fee * value.period * (100 - IFNULL(plan.discount_rate,0)) / 100

할인율을 백분율로 계산하기 위해 (100 - discount_rate) / 100을 사용한다.

예) 할인율 10%라면, 금액에 90 / 100을 곱해 90%만 남기기

 

VALUE.DAILY_FEE * VALUE.PERIOD

DAILY_FEE는 하루 대여 비용이고, PERIOD는 대여 기간(일 수)이므로 이 두 값을 곱하면 기본 대여금액(할인 전 금액)을 계산할 수 있다.

IFNULL(PLAN.DISCOUNT_RATE, 0)

IFNULL 함수로 NULL 처리 해 주는 이유는 만약에 PLAN.DISCOUNT_RATE 값으로 NULL이 들어오는 경우에  NULL 처리를 하지 않으면, 연산에서 오류는 발생하지 않지만 결과가 항상 NULL이 되기 때문이다.

 

정렬

대여 금액(FEE) 기준 내림차순 정렬, 금액이 동일하면 history_id 기준 내림차순 정렬

ORDER BY 2 DESC, 1 DESC

전체 쿼리

SELECT VALUE.HISTORY_ID, 
    ROUND(VALUE.DAILY_FEE * VALUE.PERIOD * 
          (100 - IFNULL(PLAN.DISCOUNT_RATE,0)) / 100) AS FEE
FROM VALUE
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN 
    ON PLAN.DURATION_TYPE = VALUE.DURATION_TYPE 
    AND PLAN.CAR_TYPE = VALUE.CAR_TYPE
ORDER BY 2 DESC, 1 DESC

---

⭐ 3. 정답코드

WITH VALUE AS (
    SELECT CAR.DAILY_FEE, CAR.CAR_TYPE, HISTORY.HISTORY_ID,
           DATEDIFF(END_DATE, START_DATE) + 1 AS PERIOD,
           CASE 
             WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
             WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
             WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
             ELSE '' 
           END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY
    INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR ON CAR.CAR_ID = HISTORY.CAR_ID
    WHERE CAR.CAR_TYPE = '트럭')

SELECT VALUE.HISTORY_ID, 
    ROUND(VALUE.DAILY_FEE * VALUE.PERIOD * 
          (100 - IFNULL(PLAN.DISCOUNT_RATE,0)) / 100) AS FEE
FROM VALUE
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN 
    ON PLAN.DURATION_TYPE = VALUE.DURATION_TYPE 
    AND PLAN.CAR_TYPE = VALUE.CAR_TYPE
ORDER BY 2 DESC, 1 DESC

---

📌 TMI

lv 4 문제라서 어렵긴 어렵네