[SQL] 코드카타 10일차

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

date_diff에 따라 duration_type을 다르게 설정하는 걸 어떻게 작성해야 할 지 감이 잘 오지 않았다
일단은 car_type='트럭'만 다루고 있기에 다소 하드코딩을 해보는 걸로

초안

SELECT
    distinct history_id
    , CASE
        WHEN date_dif < 7 THEN round((100-0)/100*date_dif*daily_fee, 0)
        WHEN date_dif < 30 THEN round((100-5)/100*date_dif*daily_fee, 0)
        WHEN date_dif < 90 THEN round((100-8)/100*date_dif*daily_fee, 0)
        ELSE round((100-15)/100*date_dif*daily_fee, 0)
        END as 'fee'
FROM (
    SELECT
        c.car_id
        , h.history_id
        , c.car_type
        , c.daily_fee
        , datediff(h.end_date, h.start_date) as 'date_dif'
        , replace(d.duration_type, '일 이상', '') as 'duration_day'
        , d.discount_rate
    FROM
        CAR_RENTAL_COMPANY_CAR c
        INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON c.car_type = d.car_type
        INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.car_id = h.car_id
    WHERE
        c.car_type = '트럭'
    ) a
ORDER BY
    2 desc
    , 1 desc
;

문자열 지우기 : replace

duration_type은 '7일 이상', '30일 이상'과 같은 형태로 작성되어 있다
처음에는 substr()을 쓰려 했으나, 7과 30이 문자열 길이가 다르기 때문에 이 방법은 사용할 수 없었다
대신 replace를 활용해서 '일 이상'을 ''로 대체하는 방식으로 문자열 삭제를 적용할 수 있었다

위 코드를 테스트해보고 생긴 의문은 실행 결과에 fee=0인 row들이 있었다는 것

-> 대여 당일에 반납이 이루어진 차량들이었다. 내가 date_diff() 함수를 적용할 때 +1을 하지 않아서 생긴 문제

그래서 date_diff() 함수에 +1을 적용했고, 정답이 나왔다

SELECT
    distinct history_id
    , CASE
        WHEN date_dif < 7 THEN round((100-0)/100*date_dif*daily_fee, 0)
        WHEN date_dif < 30 THEN round((100-5)/100*date_dif*daily_fee, 0)
        WHEN date_dif < 90 THEN round((100-8)/100*date_dif*daily_fee, 0)
        ELSE round((100-15)/100*date_dif*daily_fee, 0)
        END as 'fee'
FROM (
    SELECT
        c.car_id
        , h.history_id
        , c.car_type
        , c.daily_fee
        , datediff(h.end_date, h.start_date)+1 as 'date_dif'
        , replace(d.duration_type, '일 이상', '') as 'duration_day'
        , d.discount_rate
    FROM
        CAR_RENTAL_COMPANY_CAR c
        INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON c.car_type = d.car_type
        INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.car_id = h.car_id
    WHERE
        c.car_type = '트럭'
    ) a
ORDER BY
    2 desc
    , 1 desc
;

어제부터 오늘까지 1시간 반을 한 문제에 투자했다 실패하고
결국 꺾여서(?) 다음 문제로 넘어왔는데 그나마 20분만에 풀려서 다행이다...

하지만 개인적으로 위 코드가 아주 만족스럽지는 않다
지금이야 car_type='트럭'만 다루고 있기에 discount_rate를 하드코딩할 수 있었지만,
car_type이 늘어나면 car_type과 date_dif를 참고하여 discount_rate를 변동 적용할 수 있어야 할 것이다

그렇기에 아래와 같이 추가로 진행해보았다

1) car_type과 date_dif를 참고하여 해당하는 discount_rate를 반환한다

1-1) 본래 duration_type에서 숫자만 뽑아내서 그걸로 범위를 만들고 알맞은 discount_rate를 반환하게 하고 싶었으나, list나 index도 없이 이를 표현하는 것은 어려울 것 같다. 7, 30, 90이라는 수치 자체는 하드코딩으로 진행하자
2) 이를 통해 fee를 계산한다

일단 아래 보이는 2개의 서브쿼리용 코드를 작성하고

SELECT
    c.car_id
    , c.car_type
    , datediff(h.end_date, h.start_date)+1 as 'date_dif'
FROM
    CAR_RENTAL_COMPANY_CAR c
    INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.car_id = h.car_id
;

SELECT
    c.car_id
    , c.car_type
    , replace(d.duration_type, '일 이상', '') as 'duration_n'
    , d.discount_rate
FROM
    CAR_RENTAL_COMPANY_CAR c
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON c.car_type = d.car_type
;

INNER JOIN을 사용해 이 둘을 엮었다

SELECT
    a.car_id
    , a.car_type
    , a.date_dif
    , b.duration_n
FROM (
    SELECT
    c.car_id
    , c.car_type
    , datediff(h.end_date, h.start_date)+1 as 'date_dif'
    FROM
        CAR_RENTAL_COMPANY_CAR c
        INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.car_id = h.car_id
    ) a
    INNER JOIN
    (
    SELECT
        c.car_id
        , c.car_type
        , replace(d.duration_type, '일 이상', '') as 'duration_n'
        , d.discount_rate
    FROM
        CAR_RENTAL_COMPANY_CAR c
        INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON c.car_type = d.car_type
    ) b
        ON a.car_type = b.car_type
;

현재 결과는 아래와 같다

동일한 row에 대해 duration_n이 3차례 반복되고 있다
그런데 사실 어차피 7, 30, 90을 하드코딩 한다면 duration_n을 굳이 select에 포함할 필요는 없지 않을까?
fee를 계산할 때 CASE문을 사용해서 date_dif의 크기를 비교하면서 원하는 1개 row만 추출할 수 있을 것 같

......
았지만 도저히 안 짜진다
이런건 나중에 Python 할 때나 해야겠다... 너무 어렵...

짜다가 포기한 코드는 아래와 같다

WITH dd as(
    SELECT
        a.car_id
        , a.car_type
        , a.date_dif
        , b.duration_n
        , b.discount_rate
    FROM (
        SELECT
        c.car_id
        , c.car_type
        , datediff(h.end_date, h.start_date)+1 as 'date_dif'
        FROM
            CAR_RENTAL_COMPANY_CAR c
            INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.car_id = h.car_id
        ) a
        INNER JOIN
        (
        SELECT
            c.car_id
            , c.car_type
            , replace(d.duration_type, '일 이상', '') as 'duration_n'
            , d.discount_rate
        FROM
            CAR_RENTAL_COMPANY_CAR c
            INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON c.car_type = d.car_type
        ) b
            ON a.car_type = b.car_type
        ) #with문 종료
SELECT
    CASE
        WHEN date_diff < 7 THEN 0
        WHEN date_diff < 30 THEN (SELECT  
                                  FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN  
                                  WHERE replace(duration_type, '일 이상', '')=7 AND # 여기 작성 중 포기
    END as 'fee'
FROM
    dd

;