[MYSQL] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과    CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

내가 푼 쿼리

WITH CHECK_CAR_STATUS AS(
SELECT
    CAR_ID,
    MAX(START_DATE < "2022-11-01" AND END_DATE > "2022-11-30") AS STATUS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID)

SELECT
    *
FROM(
SELECT
    C1.CAR_ID,
    C1.CAR_TYPE,
    ROUND(DAILY_FEE * (1 - C3.DISCOUNT_RATE/100) * 30) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C1
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C3
ON C1.CAR_TYPE = C3.CAR_TYPE
WHERE
    C1.CAR_TYPE IN ('세단','SUV') AND 
    C1.CAR_ID IN (
        SELECT
            CAR_ID
        FROM CHECK_CAR_STATUS
        WHERE STATUS = 1) AND
    C3.DURATION_TYPE = "30일 이상") SUB
WHERE FEE BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

이번 문제는 쿼리가 맞다고 생각했지만, 문제를 풀지 못했다. 어떤 점이 문제인지 확인해보고, 효율적인 방법을 생각해보자.

MAX(START_DATE < '2022-11-01' AND END_DATE > '2022-11-30') AS STATUS

"2022년 11월 1일부터 2022년 11월 30일까지 대여 가능" 이라는 조건이 이 문제의 핵심이다. 내가 푼 쿼리의 의도는 시작 날짜가11월 1일 이전이고, 반납 날짜가 11월 30일 이후인 상태 여부 COLUMN을 만든 것이다. 그러나, 이 조건은 대여가 전체 기간(즉, 11월 1일보다 이전에 시작하고 11월 30일 보다 이후에 끝나는 경우)만 판단하여, 부분적으로 겹치는 대여가 누락된다. 11월 1일부터 11월 30일까지 하루라도 대여 중인 차량을 조건으로 찾아야 문제를 해결할 수 있다.

 

MAX(START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01') AS STATUS

두 조건을 동시에 만족하면, 대여 기간이 11월 1일부터 11월 30일 사이의 어떤 시점과도 겹친다는 의미다.

 

예를 들어, 대여가 10월 25일에 시작하여 11월 5일에 끝난다고 가정해보자.

• 10월 25일 < 11월 30일 (참)
• 11월 5일 > 11월 1일 (참)

=> 11월 기간 중에 대여 중인 날이 존재한다.

 

반대로 대여가 10월 20일에 시작하여 10월 29일에 끝난다고 가정해보자.

• 10월 20일 < 11월 30일 (참)
• 10월 29일 < 11월 1일 (거짓)

=> 11월 기간 중에 대여 중인 날이 존재하지 않는다.

 

즉, 어느 한쪽만 조건에 맞지 않더라도 대여 기간이 겹치지 않는다. 두 조건을 모두 만족하면 11월 기간 중에 대여 중인 날이 있다고 해석하면 된다.

 

최종 쿼리

WITH CarFees AS (
  SELECT 
    C.CAR_ID, 
    C.CAR_TYPE, 
    ROUND(C.DAILY_FEE * (1 - D.DISCOUNT_RATE / 100) * 30) AS FEE
  FROM CAR_RENTAL_COMPANY_CAR C
  JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D 
    ON C.CAR_TYPE = D.CAR_TYPE 
    AND D.DURATION_TYPE = '30일 이상'
  WHERE C.CAR_TYPE IN ('세단','SUV')
)
SELECT *
FROM CarFees CF
WHERE FEE BETWEEN 500000 AND 2000000
  -- 지정한 기간에 대여 중인 기록이 없는 자동차 선택
  AND NOT EXISTS (
      SELECT 1 
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
      WHERE H.CAR_ID = CF.CAR_ID
        AND H.START_DATE < '2022-11-30'
        AND H.END_DATE > '2022-11-01'
  )
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

조건을 수정하면서, 불필요한 집계(CTE 내 MAX 사용)을 피하고 HAVING 절에 조건을 넣으면 부분 집합을 바로 필터링하므로 불필요한 집계 연산을 피할 수 있다.