SQL: 특정기간 동안 대여할 자동차들의 한 달 대여비용

 

01 최종 코드

---

# CTE: 세단과 SUV 할인 비율
WITH RATE AS (SELECT car_type, discount_rate
              FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
              WHERE (CAR_TYPE = '세단' OR CAR_TYPE = 'SUV')
              AND (duration_type = '30일 이상'))

# 메인 쿼리: 타입, 대여기간, 할인적용가
SELECT CAR.CAR_ID, CAR.car_type, FLOOR(CAR.DAILY_FEE*30*((100-DISCOUNT_RATE)*0.01)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS CAR
JOIN RATE ON CAR.car_type = RATE.car_type
WHERE (CAR.CAR_TYPE = '세단' OR CAR.CAR_TYPE = 'SUV')
AND CAR.CAR_ID not IN (SELECT CAR_ID
                       FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                       WHERE (DATE(start_date) <= '2022-11-30' AND DATE(end_date) >= '2022-11-01'))
AND (FLOOR(CAR.DAILY_FEE*30*((100-DISCOUNT_RATE)*0.01)) BETWEEN 500000 AND 1999999)
ORDER BY FEE DESC, CAR.car_type, CAR.CAR_ID DESC

 

02 문제점

---

AND CAR.CAR_ID IN (SELECT CAR_ID
                   FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                   WHERE !(DATE(start_date) <= '2022-11-30' AND DATE(end_date) >= '2022-11-01'))

1. NOT IN과 !(NOT)의 잘못된 사용으로 위의 코드로 조건을 작성해서 틀렸다.

2. 나는 해당 대여 기간동안 렌탈이 진행 중인 CAR_ID만 NOT(!)하면 될 줄 알았는데,

3. CAR_ID 27번과 18번이 대여가 이미 끝난 기록과 대여 중인 기록 동시에 존재했다.

4. 결론은 기록에 의존하면 안되고 해당 기간에 대여 중인 CAR_ID 자체를 NOT IN 시키는게 정확하다.

 

💡여기서 알아둘 점: SQL에서는 NOT IN 연산자가 NULL 값을 포함할 때 문제가 발생할 수 있으므로, 이를 피하기 위해 NOT EXISTS를 사용하는 것이 더 안전하다.

 

WITH RATE AS (SELECT car_type, discount_rate
              FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
              WHERE (CAR_TYPE = '세단' OR CAR_TYPE = 'SUV')
              AND (duration_type = '30일 이상'))

SELECT CAR.CAR_ID, CAR.car_type, FLOOR(CAR.DAILY_FEE*30*((100-DISCOUNT_RATE)*0.01)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS CAR
JOIN RATE ON CAR.car_type = RATE.car_type
WHERE (CAR.CAR_TYPE = '세단' OR CAR.CAR_TYPE = 'SUV')
AND NOT EXISTS (SELECT 1
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE CAR.CAR_ID = CAR_RENTAL_COMPANY_RENTAL_HISTORY.CAR_ID
                AND (DATE(start_date) <= '2022-11-30' AND DATE(end_date) >= '2022-11-01'))
AND (FLOOR(CAR.DAILY_FEE*30*((100-DISCOUNT_RATE)*0.01)) BETWEEN 500000 AND 1999999)
ORDER BY FEE DESC, CAR.car_type, CAR.CAR_ID DESC

- NOT EXISTS 절을 사용하여 주어진 기간 동안 대여되지 않은 차량을 필터링한다. SELECT 1은 조건을 만족하는 행이 존재하는지 확인하기 위한 용도로 사용되며, 실제 출력되는 값은 중요하지 않다. 조건을 만족하는 행이 존재하면 NOT EXISTS는 FALSE로 평가되고, 그렇지 않으면 TRUE로 평가된다.

 

03 인사이트

---

1. RATE CTE 만들어주고 CASE WHEN 쓰려고 함. BUT JOIN이 더 빠름!

2. 정수를 만들어 주는 함수 FLOOR를 써서 깔끔하게 만들어야 함.

 

04 문제 링크

---

코딩테스트 연습 - 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 | 프로그래머스 스쿨