[TIL] DATEDIFF(), LEFT OUTER JOIN # MYSQL

Programmers Lv.4 - 자동차 대여 기록 별 대여 금액 구하기
👉 문제 바로가기(프로그래머스)

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

WITH FEE_INFO AS 
(
SELECT 
 t2.HISTORY_ID, 
 t2.CAR_ID,
 t1.CAR_TYPE,
 (DATEDIFF(end_date,start_date)+1) as DATE,
 t1.DAILY_FEE,
 CASE WHEN  (DATEDIFF(end_date,start_date)+1)  >= 7 AND  (DATEDIFF(end_date,start_date)+1)  < 30 THEN '7일 이상'
      WHEN  (DATEDIFF(end_date,start_date)+1)  >= 30 AND  (DATEDIFF(end_date,start_date)+1)  < 90 THEN '30일 이상'
      WHEN  (DATEDIFF(end_date,start_date)+1)  >= 90 THEN '90일 이상'
      ELSE '7일 미만'
 END as 'DURATION_TYPE'
FROM 
 CAR_RENTAL_COMPANY_CAR t1,
 CAR_RENTAL_COMPANY_RENTAL_HISTORY t2
WHERE 
 t1.CAR_TYPE = '트럭' AND
 t1.CAR_ID = t2.CAR_ID 
)

SELECT 
 t1.HISTORY_ID,
 IFNULL(ROUND((t1.DAILY_FEE*((100-t2.DISCOUNT_RATE)/100)*t1.DATE),0),t1.DAILY_FEE*t1.DATE) as FEE
FROM 
 FEE_INFO t1
LEFT OUTER JOIN 
 CAR_RENTAL_COMPANY_DISCOUNT_PLAN t2
ON 
 t1.CAR_TYPE = t2.CAR_TYPE AND
 t1.DURATION_TYPE = t2.DURATION_TYPE 
ORDER BY 
 FEE DESC,
 HISTORY_ID DESC

---

[ 논리 과정 ]

1. 대여 기록 테이블에서 날짜 차이를 구해 대여 할인율 정보 테이블과 JOIN할 수 있는 DURATION_TYPE 컬럼을 추가한다.
2. 대여중인 자동차 정보 테이블과 대여 기록 테이블을 CAR_ID를 기준으로 JOIN하여 할인율 정보 테이블과 JOIN에 필요한 모든 KEY컬럼을 하나의 테이블에 생성한다. (CAR_TYPE, DURATION_TYPE)
3. 위에서 JOIN한 테이블과 할인율 정보 테이블을 JOIN하여 구하고자 하는 정보를 조회한다.

[ 문제 상황 ]

• 할인율 정보 테이블과 JOIN할 때, 기간 타입을 키컬럼으로 JOIN하니까 할인을 받지 않는 데이터가 누락된다.
• 날짜를 단순히 빼니까 일수가 산출되지 않아 계산에 지장이 생긴다.

[ 해결 ]

• LEFT OUTER JOIN을 통해 JOIN한 후, IFNULL을 사용해 할인이 적용되지 않은 대여도 계산되도록 하였다.
• DATEDIFF()함수를 사용하고 결과값에 1을 더해줘 시작과 끝날짜를 포함하여 일수가 산출되도록 하였다.

[ 회고 ]

• 할인이 적용되지 않은 대여가 누락된다는 사실을 늦게 깨달아 시간이 많이 소요되었다.
• 다음부터는 큰 로직을 설계하고 작업 순서대로 결과를 확인 및 검토 후 다음 단계로 넘어가는 체계로 진행해봐야겠다.