서브 테이블로 복잡한 쿼리 풀기

문제

 

프로그래머스

자동차 대여 기록 별 대여 금액 구하기 문제.

 

---

 

재정의

자동차를 대여하는데 필요한 금액을 출력한다. 금액의 기간에 따라 할인률을 고려해야 한다.

주어지는 테이블은 다음과 같다

• 차량 별 정보
• 차량 대여 기간
• 대여 기간별 할인률

 

예시는 위의 순서대로 다음과 같다.

 

금액을 구하는 것은 단순하다.

END_DATE와 START_DATE의 차이를 구해서 그에 해당하는 DAILY_FEE를 곱하면 끝이기 때문이다.

 

하지만 기간별로 할인이 들어간 테이블이 있어, 이것도 고려를 해야하기 때문에 머리가 아프다.

이는 두 개의 연산을 요구한다.

 

DAILY_FEE를 구하는 연산 하나, DURATION을 구하고 비교하는 연산 하나,

이번에도 서브쿼리를 이용해 해결한다.

 

DRUATION_TYPE이 있는 마지막 테이블과 JOIN해서 해결해야 할 것이다.

그럼 Key를 CAR_TYPE과 DURATION_TYPE으로 해야 한다.

 

그렇다면 DAILY_FEE를 위해 차량 정보와 대여기간테이블을 JOIN 할 때,

DURATION_TYPE을 추가하여 JOIN을 할 수 있게 만들어 주어야 한다.

 

즉, 다음 처럼 서브 쿼리를 이용해 테이블을 새로 만들고 JOIN을 해야 한다.

다음과 같이 만들수 있을 것이다.

 

    SELECT
        HISTORY_ID,
        a.CAR_ID,
        CAR_TYPE,
        DAILY_FEE,
        (DATEDIFF(END_DATE, START_DATE)+1) AS DURATION,
        CASE
            WHEN (DATEDIFF(END_DATE, START_DATE)+1) < 7 THEN NULL
        WHEN (DATEDIFF(END_DATE, START_DATE)+1) < 30 THEN '7일 이상'
        WHEN (DATEDIFF(END_DATE, START_DATE)+1) < 90 THEN '30일 이상'
        ELSE '90일 이상'    
        END
        DURATION_TYPE
    
    FROM
        CAR_RENTAL_COMPANY_RENTAL_HISTORY AS a
    JOIN
        CAR_RENTAL_COMPANY_CAR AS b
    ON
        a.CAR_ID = b.CAR_ID
    
    WHERE CAR_TYPE = '트럭'

 

WITH을 이용하여 서브 테이블에 명칭을 붙일 수 있다.

위의 결과 테이블을 WITH으로 정의하고, 다시 JOIN을 하면 간단한 문제로 변한다.

 

여기서 조심해야 할 부분은, default로 INNER JOIN이기 때문에

할인률이 NULL인 행이 사라진다. 그래서 LEFT JOIN으로 할인률이 없는 행을 살려야 한다.

 

그리고 IFNULL을 활용하여, 따로 할인률을 명시적으로 0으로 처리함에 주의 하자.

 

WITH tmp AS(
    SELECT
        HISTORY_ID,
        a.CAR_ID,
        CAR_TYPE,
        DAILY_FEE,
        (DATEDIFF(END_DATE, START_DATE)+1) AS DURATION,
        CASE
            WHEN (DATEDIFF(END_DATE, START_DATE)+1) < 7 THEN NULL
        WHEN (DATEDIFF(END_DATE, START_DATE)+1) < 30 THEN '7일 이상'
        WHEN (DATEDIFF(END_DATE, START_DATE)+1) < 90 THEN '30일 이상'
        ELSE '90일 이상'    
        END
        DURATION_TYPE
    
    FROM
        CAR_RENTAL_COMPANY_RENTAL_HISTORY AS a
    JOIN
        CAR_RENTAL_COMPANY_CAR AS b
    ON
        a.CAR_ID = b.CAR_ID
    
    WHERE CAR_TYPE = '트럭'
)

SELECT
    HISTORY_ID,
    ROUND((DURATION * DAILY_FEE)*(100-IFNULL(DISCOUNT_RATE, 0))/(100)) AS FEE

FROM
    tmp
LEFT JOIN
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS b
ON
    tmp.CAR_TYPE = b.CAR_TYPE AND tmp.DURATION_TYPE = b.DURATION_TYPE

WHERE tmp.CAR_TYPE = '트럭'

ORDER BY FEE DESC, HISTORY_ID DESC