자동차 대여 기록 별 대여 금액 구하기
Q. CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서
자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여
대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요.
결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.(첫번째 쿼리 = 오답)
SELECT DISTINCT a.history_id,
ROUND(b.daily_fee * (DATEDIFF(a.end_date, a.start_date) + 1) * COALESCE(1 - (COALESCE(c.discount_rate, 0) * 0.01), 1), 0) AS Fee
FROM car_rental_company_rental_history a
JOIN car_rental_company_car b ON a.car_id = b.car_id
LEFT JOIN car_rental_company_discount_plan c ON b.car_type = c.car_type
WHERE b.car_type = '트럭'
AND (
(DATEDIFF(a.end_date, a.start_date) + 1 BETWEEN 7 AND 29 AND c.duration_type = '7일 이상')
OR (DATEDIFF(a.end_date, a.start_date) + 1 BETWEEN 30 AND 89 AND c.duration_type = '30일 이상')
OR (DATEDIFF(a.end_date, a.start_date) + 1 >= 90 AND c.duration_type = '90일 이상')
OR c.duration_type IS NULL -- 할인을 적용할 수 없는 경우 null (대여기간이 7일 미만)
)
order by 2 desc, 1 desc맞는줄 알았는데 오답이었다.
여기서 COALESCE는
COALESCE 함수
1. NULL이 아닌 첫번째 값 가져옴
2. NULL이 있을 시 디폴트값이 필요할 때 대체하기위해 사용
3. ex.
coalesce( [컬럼명], 0 ) : [컬럼명] 안에 NULL이 있으면 0으로 대체함
FEE 값을 구하기 위해선
- 대여 일수 계산 :
datediff(end_date, start_date) + 1 - 일일 대여 요금 :
daily_fee - 할인율(%) :
discount_rate
을 모두 곱해서 구해야 하는데
(datediff(end_date, start_date) + 1 xdaily_fee x discount_rate)
여기서 3번 할인율은 대여기간에 따라 할인율이 상이하다.
_<대여기간에 따른 할인율>
- 7일 미만 : 할인 없음
- 7일~29일 : 5%
- 30일~89일 : 7%
- 90일~ : 10%
렌트한 차들이 모두 7일 이상 렌트를 한 것이 아니기 때문에, *7일 미만의 건들의 discount_rate는 null이 아니라 1로 값을 쳐야해서 coalesce 함수로 null인 값을 1로 바꿔준다.
- Null 이 아닌 1로 바꿔줘야 하는 이유 :
Null은 0이 아닌 데이터가 존재하지 않는다는 의미로
ex) 3 x 5 x null => null
이렇게 FEE를 계산할 수가 없기 때문에 null이 아닌 값으로 꼭 바꿔줘야 한다.
(두번째 쿼리 = 정답)
SELECT
a.history_id,
ROUND(a.daily_fee * (DATEDIFF(a.end_date, a.start_date) + 1) * COALESCE(1 - (p.discount_rate * 0.01), 1), 0) AS fee
FROM (
SELECT
h.history_id,
h.car_id,
c.car_type,
c.daily_fee,
h.end_date,
h.start_date,
CASE
WHEN DATEDIFF(h.end_date, h.start_date) + 1 BETWEEN 7 AND 29 THEN '7일 이상'
WHEN DATEDIFF(h.end_date, h.start_date) + 1 BETWEEN 30 AND 89 THEN '30일 이상'
WHEN DATEDIFF(h.end_date, h.start_date) + 1 >= 90 THEN '90일 이상'
ELSE NULL -- 할인을 적용할 수 없는 경우 null (대여기간이 7일 미만)
END AS duration_date
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY h
JOIN
CAR_RENTAL_COMPANY_CAR c ON c.car_id = h.car_id
) a
LEFT JOIN
CAR_RENTAL_COMPANY_DISCOUNT_PLAN p ON a.car_type = p.car_type AND a.duration_date = p.duration_type
WHERE
a.car_type = '트럭'
ORDER BY
fee DESC, history_id DESC;두번째 쿼리는 from절 서브쿼리 안에서 duration_date을 계산해서 전처리를 했다.
-
일수를 계산해서 직접 나눈 duration_date과 discount_rate가 들어있는 테이블을
left join한다. -
이렇게 하위 쿼리에서 사전적으로 처리한 데이터를 기반으로 main select 문에서 FEE를 계산한다.
-> 주 쿼리에서 조인 및 후속 계산을 수행하기 전에 하위(a)에서 전처리를 해놨기 때문에, 더 모듈화가 되어있고, 두번째 쿼리가 더 읽기 쉬워졌다. -
참고로
left join이 아닌inner join으로만 바꿔줘도 오답으로 뜬다.
실행결과가 첫번째 쿼리와 마찬가지로 discount_rate이 존재하는 데이터만 계산된다.
-> from 서브쿼리 a 의 case when의 else null 값(대여 기간 7일 미만)은 조인이 걸릴 수 없어, 서브쿼리 a의 Null 데이터가 꼭 나오기위해선 inner join 이 아닌 left join으로 해야했다.
(첫번째 쿼리문 결과)
724 6336960
681 5356240
630 4791360
558 4404960
653 3793160
722 2909040
594 1888600
680 1524750
714 1118150
591 1118150
556 813200(두번째 쿼리문 결과)
724 6336960
681 5356240
630 4791360
558 4404960
653 3793160
722 2909040
594 1888600
680 1524750
714 1118150
591 1118150
556 813200
--------------(<= 여기까지는 첫번째 쿼리와 두번째 쿼리 결과가 동일)
610 672000 (여기서 부터는
676 568000 대여기간(duration_type(= 쿼리에선 case when 문의 duration_date) 이 NULL 인 값들)
527 535000
720 532000
710 532000
701 504000
640 428000
641 426000
628 426000
623 336000
602 336000
673 321000
631 321000
654 284000
618 214000
586 214000
581 214000
546 214000
716 140000
711 107000
705 107000
627 107000
524 107000조인하라고 하면 무조건 inner 부터 하고 봤었는데,
이번 문제를 통해 Inner join의 허점?을 많이 깨닫게되었다.
앞으로 join을 할때 내가 원하는 대로 잘 된건지 중간중간 확인을 꼭 하는 습관을 들여야겠다.
(분명 전처리를 잘 해놓고 inner join을 걸었더니 내가 원하는 그림이 안나오고 마음대로 join 된 경험으로 다시 머리 싸매고 싶지 않다...🫠)
