[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 = 0) AND
    C3.DURATION_TYPE = "30일 이상") SUB
WHERE FEE BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

 

이전에 포스팅했던 자동차 대여 기록에서 MAX 함수를 활용해서 각 대여 기록마다 특정 날짜가 해당 기간에 속하는지 평가 했었다. 동일한 로직으로 생각했지만, 문제를 풀 수 없었다. 어떤 부분이 문제 일까?

 

조건의 차이

어떤 차량이 11월 중 어느 순간이라도 대여되고 있으면 대여 중으로 판단하고, 그렇지 않으면 대여 가능으로 판단해야한다. 11월 한달 간 빌릴 수 있는 차량을 선별하는 것이 문제의 조건이기 때문이다. SQL에서 특정 기간(11월 1일 ~ 11월 30일)과 대여 기록의 겹침 여부를 판단할 때, 두 가지 조건을 고려할 수 있다.

 

조건 A : 

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

이 조건은 대여 기록이 11월 전체를 완전히 커버할 때만 TRUE를 반환한다. 즉, 대여 시작일이 11월 1일 이전이어야 하고, 종료일이 11월 30일 이후여야 한다. 예를 들어 대여 기간이 11월 5일부터 11월 20일인 경우에는 조건이 FALSE로 평가되어 결과가 누락된다. 즉, 기존 쿼리는 11월 전체 기간에서 대여가 포함된 것은 추출되지만, 부분적으로 대여되는 ID를 감지하지 못해 잘못된 것이다.

 

조건 B : 

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

대여 시작일이 11월 30일 이전이어야 한다. = 대여가 11월 말 이전에 시작하면 11월 중에 대여가 진행될 가능성이 있다.

대여 종료일이 11월 1일 이후여야 한다. = 대여가 11월 초 이후 에 끝나면 11월 중에 대여가 진행될 가능성이 있다.

 

즉, 대여 기록의 시작일이 11월 30일 이전이고, 종료일이 11월 1일 이후라면 대여 기간과 11월이 어느부분이라도 겹친다는 것을 의미한다. 이전 포스팅은 특정한 단일 날짜가 대여 기록에 포함되는지 확인했던 것이고, 이번 문제는 특정한 기간과 대여 기록이 겹치는지를 확인하기 때문에 접근 방식이 다른 것 이다.

 

정답 쿼리

WITH CHECK_CAR_STATUS AS(
SELECT
    CAR_ID,
    MAX(START_DATE < "2022-11-30" AND END_DATE > "2022-11-01") 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 = 0) AND
    C3.DURATION_TYPE = "30일 이상") SUB
WHERE FEE BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

날짜를 제외한 다른 부분은 문제가 없었다. 추가로, MySQL은 Boolean 값을 내부적으로 1(참)과 0(거짓)으로 처리하지만 일부 DBMS(PostgreSQL 등)에서는 명시적인 변환이 필요할 수 있다. 

 

MAX(
  CASE
    WHEN START_DATE < '2022-11-30' AND END_DATE > '2022-11-01' THEN 1
    ELSE 0
  END
) AS conflict

그련 경우 CASE 문을 사용하여 1, 0으로 처리해주면 동일한 결과를 확인할 수 있다.