[MYSQL] 프로그래머스_자동차 대여 기록 별 대여 금액 구하기 (DATEDIFF/CASE WHEN/PIVOT)

프로그래머스 > String, Date> 자동차 대여 기록 별 대여 금액 구하기

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

프로그래머스

전체 코드

-- 코드를 입력하세요
SELECT HISTORY_ID,
    FLOOR(CASE 
            WHEN DAY >= 90
            THEN DAY * DAILY_FEE * (100 - DAY_90) / 100
            WHEN DAY >= 30
            THEN DAY * DAILY_FEE * (100 - DAY_30) / 100
            WHEN DAY >= 7
            THEN DAY * DAILY_FEE * (100 - DAY_7) / 100
            ELSE DAY * DAILY_FEE END ) AS FEE
FROM (SELECT CAR_TYPE, DAILY_FEE, HISTORY_ID,
        DATEDIFF(end_date, start_date) + 1 AS DAY
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY RH
        LEFT OUTER JOIN CAR_RENTAL_COMPANY_CAR CC ON RH.CAR_ID = CC.CAR_ID
        WHERE CC.CAR_TYPE='트럭'
    ) C
LEFT OUTER JOIN 
    (SELECT CAR_TYPE,
        MAX(CASE WHEN DURATION_TYPE='7일 이상' THEN DISCOUNT_RATE ELSE NULL END) AS DAY_7,
        MAX(CASE WHEN DURATION_TYPE='30일 이상' THEN DISCOUNT_RATE ELSE NULL END) AS DAY_30,
        MAX(CASE WHEN DURATION_TYPE='90일 이상' THEN DISCOUNT_RATE ELSE NULL END) AS DAY_90
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE='트럭'
    GROUP BY CAR_TYPE ) P 
    ON C.CAR_TYPE=P.CAR_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC

---

대여기간 구하기

DATEDIFF 를 사용해서 END_DATE - START_DATE 를 구했다.

대여기간은 시작일과 종료일을 포함하여 계산하기 때문에 (END_DATE - START_DATE) 결과에 + 1을 해줬다.

 

예를 들어, 2023-04-20 부터 2023-04-22 까지 대여면 20, 21, 22일을 사용한다.
따라서 DATEDIFF(END_DATE, START_DATE)는 2이므로 + 1을 해서 총 대여기간 3일로 계산되도록 처리했다.

 

차 종류별 할인 혜택 구하기

일단 CAR_RENTAL_COMPANY_DISCOUNT_PLAN의 '트럭'의 할인정책은 다음과 같다.

이 테이블을 pivot 하여 JOIN 해서 할인율 계산 시 사용했다.

SELECT CAR_TYPE,
        MAX(CASE WHEN DURATION_TYPE='7일 이상' THEN DISCOUNT_RATE ELSE NULL END) AS DAY_7,
        MAX(CASE WHEN DURATION_TYPE='30일 이상' THEN DISCOUNT_RATE ELSE NULL END) AS DAY_30,
        MAX(CASE WHEN DURATION_TYPE='90일 이상' THEN DISCOUNT_RATE ELSE NULL END) AS DAY_90
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE CAR_TYPE='트럭'
GROUP BY CAR_TYPE

 

👇PIVOT 결과

 

할인율이 적용된 대여 금액 구하기

1. 일일대여요금, 대여기간을 구한 SELECT 결과와 트럭의 대여기간별 할인 정책을 Pivot한 테이블을 LEFT OUTER JOIN 한 뒤

2. CASE WHEN으로 대여기간별로 할인율을 적용한 대여요금을 구한다.

SELECT HISTORY_ID,
    FLOOR(CASE 
            WHEN DAY >= 90
            THEN DAY * DAILY_FEE * (100 - DAY_90) / 100
            WHEN DAY >= 30
            THEN DAY * DAILY_FEE * (100 - DAY_30) / 100
            WHEN DAY >= 7
            THEN DAY * DAILY_FEE * (100 - DAY_7) / 100
            ELSE DAY * DAILY_FEE END ) AS FEE
            
            ...

👉 할인율 적용된 대여금액 : 대여기간 * 일일요금 * {(100 - 할인율) / 100}

 

주의💡

- DAILY_FEE는 일일 요금이기 때문에 대여기간을 곱해줘야한다.

- FEE는 정수부분만 출력해야하기 때문에 FLOOR 함수로 반올림을 적용해준다.