(SQL)자동차 대여 기록 별 대여 금액 구하기 (MySQL)

문제

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

프로그래머스

데이터는 다음 포스트의 데이터와 동일

https://sha-sha-sha.tistory.com/61 

[SQL]특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(MySQL)

 

풀이과정

• CAR_TYPE = '트럭'
• 대여 기록에서 대여 기간 구한 후 분류 -> 7일 미만 / 7일 이상 / 30일 이상 ~ 90일 미만 / 90일 이상
• 대여기록의 CAR_ID = 자동차 정보의 CAR_ID -> DAILY_FEE 확인
• 할인 정책의 할인율 확인 후 적용 : DAILY_FEE * (100-할인율)/100 * 대여일자
• 정렬 : 대여금액 내림차순 / 기록 ID 내림차순

 

Script

SELECT 
    ORG.HISTORY_ID,
    ROUND(ORG.DAILY_FEE*(100 - COALESCE(D.DISCOUNT_RATE,0))/100 * ORG.DURATION) AS FEE
FROM
(
    SELECT 
        H.HISTORY_ID,
        H.CAR_ID,
        C.CAR_TYPE,
        C.DAILY_FEE,
        H.DURATION,
        CASE WHEN (H.DURATION >=0 AND H.DURATION<7) THEN 0
            WHEN (H.DURATION>=7 AND H.DURATION <30) THEN 7
            WHEN (H.DURATION>=30 AND H.DURATION <90) THEN 30
            ELSE 90
            END AS D_TYPE
    FROM (
        SELECT 
            HISTORY_ID,
            CAR_ID,
            DATEDIFF(END_DATE, START_DATE)+1 AS DURATION
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) H
        LEFT JOIN CAR_RENTAL_COMPANY_CAR C
        ON H.CAR_ID = C.CAR_ID
    WHERE 1=1
        AND C.CAR_TYPE = '트럭'
    ) ORG
    LEFT JOIN
    (SELECT 
        CAR_TYPE,
        REGEXP_REPLACE(DURATION_TYPE, '[가-힣]','') AS D_INT,
        DISCOUNT_RATE
     FROM
        CAR_RENTAL_COMPANY_DISCOUNT_PLAN ) D
     ON ORG.CAR_TYPE = D.CAR_TYPE
         AND ORG.D_TYPE = D.D_INT
    
ORDER BY FEE DESC, ORG.HISTORY_ID DESC

* COALESCE(D.DISCOUNT_RATE,0) : Null을 0으로 채움 (할인 정책에 포함되지 않는 대여기간)

* DATEDIFF(END_DATE, START_DATE)+1  : 두 날짜의 차이에 대여 당일을 포함해야 하므로 +1

* REGEXP_REPLACE(DURATION_TYPE, '[가-힣]','') : 할인 타입에서 숫자를 추출하기 위해 문자 제거