[MySQL] 자동차 대여 기록 별 대여 금액 구하기

문제 링크

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

쿼리

with rent_history as(
    select history_id, car_id, datediff(end_date,start_date)+1 as rent_day 
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY
),
discount_int as(
    select car_type, discount_rate, case
        when duration_type='7일 이상' then 7
        when duration_type='30일 이상' then 30
        when duration_type='90일 이상' then 90
        end as duration_int
    from CAR_RENTAL_COMPANY_DISCOUNT_PLAN
), 
last_table as (
    select history_id, 
        C.car_type, 
        rent_day, 
        daily_fee, 
        ifnull(duration_int,0) as duration_int, 
        ifnull(discount_rate,0) as discount_rate, 
        ifnull(max(duration_int) over(partition by history_id),0) as max_duration_type
    from rent_history R
    left join CAR_RENTAL_COMPANY_CAR C using(car_id)
    left join discount_int D on C.car_type=D.car_type and rent_day>=duration_int
)

select history_id, 
    floor(rent_day*daily_fee*(1-discount_rate*0.01)) as fee
from last_table
where car_type='트럭'
    and max_duration_type=duration_int
order by fee desc, history_id desc;

길게도 썼다. 처음 생각했던 구성에서 오류난 걸 고치고 고치다보니 이렇게까지 길어졌다.

처음엔 7일 이하의 렌트에 대해선 할인이 적용되지 않기 때문에, 그 경우 ifnull을 사용해 discount_rate를 0으로 대체해줬어야했는데 그 부분을 잊고 discount_rate가 null인 경우를 제외하고 select 했어서 애를 먹었다.

문제점

이 쿼리에서 적폐는 저 last_table CTE에서 마지막 join 조건인 rent_day>=duration_int이다.

저렇게 쓰니까 예를 들어 렌트 일수가 90일 이상인 경우는 7,30,90일 이상인 discount 정보와 전부 조인되다보니 내가 원하는 데이터가 아니었음. 그래서 max(duration_int)를 써서 내가 원하는 데이터만 골라줘야 했음

근데! discount_int CTE와 조인 조건을 합쳐서! 그냥 조인 조건을 case문으로 써주면 훨씬 단순하게 작성할 수 있다. 아래처럼

새로 작성한 것

select history_id, 
    floor((datediff(end_date,start_date)+1)*daily_fee*(1-ifnull(discount_rate,0)*0.01)) as fee
from CAR_RENTAL_COMPANY_RENTAL_HISTORY R
left join CAR_RENTAL_COMPANY_CAR C using(car_id)
left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN D 
    on C.car_type=D.car_type 
    and duration_type = (case 
        when datediff(end_date,start_date)+1 >=90 then '90일 이상'
        when datediff(end_date,start_date)+1 >=30 then '30일 이상'
        when datediff(end_date,start_date)+1 >=7 then '7일 이상'
        end)
where C.car_type='트럭'
order by fee desc, history_id desc;

case문에서 약간의 하드코딩(?)이 들어가긴 하나 문제의 조건 자체가 duration_type은 90일, 30일, 7일 이상으로 정해져있다 했으니 이렇게 해도 될 것 같다.

만약 datediff가 계속 계산되는 게 싫다면 아래처럼 CTE를 사용해 좀 더 깔끔하게 작성할 수 있다.

with rent_day as (
    select history_id, car_id, datediff(end_date,start_date)+1 as day
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

select history_id, 
    floor(day*daily_fee*(1-ifnull(discount_rate,0)*0.01)) as fee
from rent_day R
left join CAR_RENTAL_COMPANY_CAR C using(car_id)
left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN D 
    on C.car_type=D.car_type 
    and duration_type = (case 
        when day >=90 then '90일 이상'
        when day >=30 then '30일 이상'
        when day >=7 then '7일 이상'
        end)
where C.car_type='트럭'
order by fee desc, history_id desc;

알게 된 것

• 윈도우 함수: 집계함수() over(partition by ~ order by ~)와 같이 윈도우 함수를 사용해 각 partition 별 집계 함수 값을 구할 수 있다.
• 연산 우선순위: 부등호는 사칙 연산보다 우선순위가 낮기 때문에, 부등호를 사용한 조건 작성에선 괄호를 쓰지 않아도 됨
  • datediff(end_date,start_date)+1 >=90 얘처럼 왼쪽 연산을 괄호로 묶을 필요 없다.
• 반올림/버림: round를 쓰면 반올림되고, floor를 쓰면 소수점을 버리게 된다. 해당 문제에선 fee가 정수 부분만 출력되어야 한다. 라고 했으니 반올림보다는 버림이 적절해보인다.
• case문 else, as 생략
  • else를 생략하면 when 조건에 해당되지 않는 경우는 모두 null이 반환된다고 한다. 즉 else null 쓸 거면 안써도 된다.
  • as는 단순히 별칭을 달아주는 것이기 때문에 생략해도 문제 없다. 위 쿼리에서 case문의 값을 참조해 사용할 수 없으니 굳이 as를 쓰지 않았다.