WITH truck AS (
SELECT car_id, car_type, daily_fee
FROM car_rental_company_car
WHERE car_type = '트럭'
),
history AS (
SELECT h.history_id, h.car_id, t.daily_fee, (DATEDIFF(end_date, start_date) + 1) as duration
FROM car_rental_company_rental_history as h
JOIN truck as t
ON h.car_id = t.car_id
)
SELECT
history_id,
daily_fee,
duration,
CASE WHEN duration >= 90 THEN round(daily_fee*(1-0.10)*duration, 0)
WHEN duration >= 30 THEN round(daily_fee*(1-0.07)*duration, 0)
WHEN duration >= 7 THEN round(daily_fee*(1-0.05)*duration, 0)
ELSE (daily_fee * duration)
END AS FEE
FROM history
ORDER BY FEE DESC, history_id DESC;
>>> 오답!!? 뭐가 잘못됐을까..? 결과는 잘 나오는데 흠🤔
🚨 오답 원인 분석 🚨
plan테이블을 조인이나 참조하지 않고, 값만 가져다 쓴 것이 가장 큰 문제 그럼 어떻게 해야하지? 1. 참조든 조인이든 사용할 것! 2. 어떤 컬럼을 키로 사용할지 고민해볼 것! ⭐️ 3. '90일 이상' 같은 문자를 숫자로 변환해보는 사고도 가져볼 것! ... etc
정답쿼리
WITH discount AS (
SELECT
duration_type,
discount_rate,
CASE duration_type
WHEN '7일 이상' THEN 7
WHEN '30일 이상' THEN 30
WHEN '90일 이상' THEN 90
ELSE 0
END AS min_days
FROM car_rental_company_discount_plan
WHERE car_type = '트럭'
),
matched AS (
SELECT
h.history_id,
c.daily_fee,
(DATEDIFF(h.end_date, h.start_date) + 1) AS duration,
d.discount_rate,
ROW_NUMBER() OVER (PARTITION BY h.history_id ORDER BY d.min_days DESC) AS rn
FROM car_rental_company_rental_history h
JOIN car_rental_company_car c
ON h.car_id = c.car_id
LEFT JOIN discount d
ON (DATEDIFF(h.end_date, h.start_date) + 1) >= d.min_days
WHERE c.car_type = '트럭'
)
SELECT
history_id,
CASE
WHEN discount_rate IS NOT NULL THEN (daily_fee * duration * (100 - discount_rate)) DIV 100
ELSE (daily_fee * duration)
END AS FEE
FROM matched
WHERE rn = 1
ORDER BY FEE DESC, history_id DESC;
✍️ 쿼리 작성 흐름
1) 가장 골치였던, 할인율을 적용을 위한 쿼리를 만들어보자!
case when 을 사용해서 '문자' -> '숫자' 변환 시도
car_type ='트럭' 만 골라서
WITH discount AS (
SELECT
duration_type,
discount_rate,
CASE duration_type
WHEN '7일 이상' THEN 7
WHEN '30일 이상' THEN 30
WHEN '90일 이상' THEN 90
ELSE 0
END AS min_days
FROM car_rental_company_discount_plan
WHERE car_type = '트럭'
),
2) join 사용해서 테이블 결합
기존 history테이블과 car테이블을 inner join / car_id를 키로 결합
이후 위에서 생성한 discount테이블을 left join 으로 결합하는데,,,,
(DATEDIFF(h.end_date, h.start_date) + 1) >= d.min_days (즉, duration이 min_days보다 크거나 같은 것을 키로!?)
matched AS (
SELECT
h.history_id,
c.daily_fee,
(DATEDIFF(h.end_date, h.start_date) + 1) AS duration,
d.min_days,
d.discount_rate,
ROW_NUMBER() OVER (PARTITION BY h.history_id ORDER BY d.min_days DESC) AS rn
FROM car_rental_company_rental_history h
JOIN car_rental_company_car c
ON h.car_id = c.car_id
LEFT JOIN discount d
ON (DATEDIFF(h.end_date, h.start_date) + 1) >= d.min_days
WHERE c.car_type = '트럭'
)
여가까지 결과를 뽑아보니 이렇다. 알고보니 예시에서 보이는 것과 트럭의 할인율은 조금 다르더라...90일 이상은 없었고, 30일 이상도 8%의 할인율 인 것!!
▼ 여기서 ROW_NUMBER()에서 그룹하고 순위를 매긴 rn 중에서 왜 1 만 불러오는 걸까?
위 사진에서 예를들어, history_id가 558을 보면 duration이 36일인 것이 확인된다. 이는 discount 의 case when 절에서 생성된 min_days에 따라서 '7일 이상'일 때와 '30일 이상'일 때 두가지 모두가 해당이 되는데!!
이것을 ROW_NUMBER()로 대여기록별로 묶어서 min_days 내림차순을 하고나서 rn=1인 값을 최우선 적용할 할인율로 채택을 해야한다!!
즉, history_id 별로 "적용 가능한 할인 조건 중 가장 큰 것"을 가져오게 되는 것!!
3) 이젠 메인쿼리를 작성할 시간!!
원하는 컬럼은 history_id, FEE
아 그럼 select 절에서 FEE를 계산해야겟네!? case when절 사용 !
그리고 rn = 1 인 것들 중에서 discount_rate 이 null(공백)인 것들도 빼줘야 돼!
🔍 ( ... ) DIV 100 는 무엇인가? >> (...)를 100으로 나눈 몫만 반환(정수)한다!
이렇게 해서 최종적으로 정렬까지 한 후에 합치면 된다... 넘 복잡스하다...😩😵💫
🤖 CASE WHEN절로 쿼리문이 길어지는 것 같아서, 서칭하면서 본 CAST() 나 COALESE() 함수를 사용해서 더 간결하게 가능하냐고 GPT에게 물어보았다... 그 쿼리문은 아래 접은글로 담아둔다.
WITH discount AS (
SELECT
CASE duration_type
WHEN '7일 이상' THEN 7
WHEN '30일 이상' THEN 30
WHEN '90일 이상' THEN 90
END AS min_days,
discount_rate
FROM car_rental_company_discount_plan
WHERE car_type = '트럭'
),
matched AS (
SELECT
h.history_id,
c.daily_fee,
(DATEDIFF(h.end_date, h.start_date) + 1) AS duration,
d.discount_rate,
ROW_NUMBER() OVER (PARTITION BY h.history_id ORDER BY d.min_days DESC) AS rn
FROM car_rental_company_rental_history h
JOIN car_rental_company_car c
ON h.car_id = c.car_id
LEFT JOIN discount d
ON (DATEDIFF(h.end_date, h.start_date) + 1) >= d.min_days
WHERE c.car_type = '트럭'
)
SELECT
history_id,
(daily_fee * duration * (100 - COALESCE(discount_rate, 0))) DIV 100 AS FEE
FROM matched
WHERE rn = 1
ORDER BY FEE DESC, history_id DESC;
어지럽고, 어렵고, 복습(?)해도 기억안나고...총체적 난국😩 내 학습 방법을 돌아보고 계획도 세워서 "체계적인 학습관리가 필요하겠구나" 라고 생각이 들었다.
