[MySQL] 프로그래머스 - 자동차 대여 기록 별 대여 금액 구하기 (WITH(임시테이블), CASE WHEN-THEN)

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

프로그래머스

 

💡 WITH : WITH절은 동일한 SQL이 반복되어서 사용될 때 성능을 높이기 위해 사용하는데, 임시테이블에 SQL 쿼리문의 결과를 저장하여 필요에 따라 불러온다. 

 

 

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

---

 

구현 과정

• 자동차 종류가 '트럭'인 자동차의 대여기록에 대해 대여 기간 종류 구하기 
• 대여 기간 종류에 따른 할인률을 적용하여 대여금액(FEE) 구하기 

 

1️⃣ 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해 대여 기간 종류 구하기 

자동차 종류가 '트럭'인 모든 자동차의 대여 시작일 ~ 대여 종료일을 계산하여 대여 기간을 구한 후, 대여 기간에 따라 할인률을 결정 짓는 대여 기간 종류를 구한다. 

• DATEDIFF(시작일, 종료일) : 종료일과 시작일의 날짜 차이를 구한다. 총 대여일을 위해선 +1 을 한다. 
• CASE WHEN ~THEN , ELSE END : 조건절을 사용하여 각 대여일에 따른 대여 기간 종류를 구한다. 할인이 안될 경우 (7일 미만일 경우), '할인 없음' 으로 표시한다. 

 

모든 자동차 기록에 대해 대여 기간을 따져 구하는 동일한 연산을 반복해야 하기 때문에 WITH 절을 사용하여 SQL 문을 임시테이블로 저장한다. 

 

대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 조인하여 자동차 종류가 트럭인 자동차의 대여 날짜를 계산하고, 그에 따른 대여 기간 종류를 구한다. 

WITH value AS 
(SELECT A.DAILY_FEE, A.CAR_TYPE, B.HISTORY_ID, 
 DATEDIFF(B.END_DATE, B.START_DATE)+1 AS RENT_DATE,
 (CASE WHEN DATEDIFF(B.END_DATE, B.START_DATE)+1 >=90 THEN '90일 이상'
 WHEN DATEDIFF(B.END_DATE, B.START_DATE)+1 >=30 THEN '30일 이상' 
 WHEN DATEDIFF(B.END_DATE, B.START_DATE)+1 >=7 THEN '7일 이상'
 ELSE '할인 없음' END) AS DURATION_TYPE
 
FROM CAR_RENTAL_COMPANY_CAR A 
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B 
ON A.CAR_ID= B.CAR_ID
WHERE A.CAR_TYPE ='트럭')

 

2️⃣ 대여 기간 종류에 따른 할인률을 적용하여 대여금액(FEE) 구하기 

1번 과정에서 만든 임시 테이블 value 와 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 조인한다. 단, value 테이블의 DISCOUNT_TYPE 에는 '할인 없음' 컬럼이 추가 되어 있기 때문에 OUTER JOIN 을 해야한다. 

JOIN할 때, 대여 종류와 자동차 종류를 기준으로 조인한다. (트럭만) 

 

value의 '할인 없음' 과 매치되는 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 컬럼은 NULL로 저장되어 있기 때문에 할인률을 계산할 때 IFNULL을 사용하여 NULL값으로 저장되어 있다면 할인률을 0으로 적용한다.

• IFNULL(할인률, 0) : 할인률이 NULL 값이라면 0으로 적용.

 

SELECT value.HISTORY_ID,
ROUND(value.RENT_DATE * value.DAILY_FEE * (1-IFNULL(C.DISCOUNT_RATE,0)/100)) AS FEE 
FROM value 
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
ON value.DURATION_TYPE= C.DURATION_TYPE  
AND value.CAR_TYPE= C.CAR_TYPE
ORDER BY FEE DESC, value.HISTORY_ID DESC;

 

---

✔️코드

-- 코드를 입력하세요
WITH value AS 
(SELECT A.DAILY_FEE, A.CAR_TYPE, B.HISTORY_ID, 
 DATEDIFF(B.END_DATE, B.START_DATE)+1 AS RENT_DATE,
 (CASE WHEN DATEDIFF(B.END_DATE, B.START_DATE)+1 >=90 THEN '90일 이상'
 WHEN DATEDIFF(B.END_DATE, B.START_DATE)+1 >=30 THEN '30일 이상' 
 WHEN DATEDIFF(B.END_DATE, B.START_DATE)+1 >=7 THEN '7일 이상'
 ELSE '할인 없음' END) AS DURATION_TYPE
FROM CAR_RENTAL_COMPANY_CAR A 
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B 
ON A.CAR_ID= B.CAR_ID
WHERE A.CAR_TYPE ='트럭')

SELECT value.HISTORY_ID,
ROUND(value.RENT_DATE * value.DAILY_FEE * (1-IFNULL(C.DISCOUNT_RATE,0)/100)) AS FEE 
FROM value 
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
ON value.DURATION_TYPE= C.DURATION_TYPE  
AND value.CAR_TYPE= C.CAR_TYPE
ORDER BY FEE DESC, value.HISTORY_ID DESC;