문제 설명

문제 요약

• CAR_TYPE 컬럼은 CAR_RENTAL_COMPANY_CAR와 CAR_RENTAL_COMPANY_DISCOUNT_PLAN에 존재합니다. 이때, 자동차 종류가 '트럭'인 자동차의 대여기록을 찾습니다.
• 대여 기록 별로 대여 금액 FEE을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성합니다.
• 대여 기록 별로 대여 금액을 구할 때 할인율을 생각해주어야합니다. 7일 이상, 30일 이상, 90일 이상 각각에 대한 할인율이 존재합니다. 단, 대여 기간이 7일 미만인 경우 할인정책이 없습니다.
• 결과를 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬합니다.

문제 해결 방법

예시로, 테이블들의 정보들은 아래와 같다고 하겠습니다.

테이블 별로 명칭을 일단 정해줍니다.

CAR_RENTAL_COMPANY_CAR -> C
CAR_RENTAL_COMPANY_RENTAL_HISTORY -> H
CAR_RENTAL_COMPANY_DISCOUNT_PLAN -> P

FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
INNER JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE AND

테이블들을 join 해줍니다.

FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
INNER JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID

일단 H테이블과 C테이블의 inner join과 on을 통해 C테이블의 CAR_ID와 H테이블의 CAR_ID가 같은 레코드들만 뽑습니다.

FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
INNER JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE AND

다음으로 left join과 on을 통해 P테이블의 자동차 종류와 C테이블의 자동차 종류가 같은 레코드들만 뽑습니다.

이렇게 되면, 대여된 자동차가 무엇인지와 언제부터 언제까지 빌렸고 할인율은 얼마인지를 확인 할 수 있게됩니다.

REPLACE(P.DURATION_TYPE, '일 이상', '') <= (DATEDIFF(H.END_DATE, H.START_DATE) + 1)

P테이블의 duration_type이 'X일 이상'으로 출력되므로 replace 함수를 통해 숫자만 남기고
datediff 함수를 통해서 대여 기간이 해당 자동차가 할인율이 적용될 수 있는 기준을 넘었는지 확인합니다.

WHERE C.CAR_TYPE = '트럭'

자동차 타입이 트럭인 것만 확인합니다.

group by h.history_id

대여 기록 별로 금액을 확인하야하므로 group by를 통해 history_id로 그룹화 해줍니다.

여기까지 보고 select을 통해 뽑아야하는 데이터들을 확인해봅시다.

SELECT 
H.HISTORY_ID, 
ROUND((((100 - COALESCE(P.DISCOUNT_RATE, 0)) * C.DAILY_FEE) * (DATEDIFF(H.END_DATE, H.START_DATE) + 1)) / 100, 0)
AS FEE

H테이블의 history_id를 뽑고 다음으로 FEE를 뽑습니다.

이때, FEE는 COALESCE(P.DISCOUNT_RATE, 0)을 통해서 P테이블의 discount_rate가 null이 아니면 해당 discount_rate를 뽑아내고 null이라면 0으로 처리합니다.

ORDER BY FEE DESC, HISTORY_ID DESC

마지막으로 fee를 기준으로 내림차순으로 정렬하고 history_id를 기준으로 내림차순 해줍니다.

전체 코드

SELECT H.HISTORY_ID, 
       ROUND((((100 - COALESCE(P.DISCOUNT_RATE, 0)) * C.DAILY_FEE) * (DATEDIFF(H.END_DATE, H.START_DATE) + 1)) / 100, 0)
       AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
INNER JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE AND
REPLACE
(P.DURATION_TYPE, '일 이상', '') <= (DATEDIFF(H.END_DATE, H.START_DATE) + 1)
WHERE C.CAR_TYPE = '트럭'
group by h.history_id
ORDER BY FEE DESC, HISTORY_ID DESC;