[MySQL] 전체 자료 중 특정 시점에서 현황 파악하기

조인과 유니온 그리고 서브쿼리만 배운 나로 써는 적잖은 충격을 받았다.

우선 문제는 아래와 같다.

코딩테스트 연습 - 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 | 프로그래머스 스쿨 (programmers.co.kr)

프로그래머스

 

[문제]

 

[예시]

필자는 그냥 CAR_ID로 GROUP BY를 하고 그중 MAX(END_DATE) AS AVAILABILITY로 해서 2022-10-16 보다 작으면 대여중, 아니면 대여 가능 으로 접근하려고 했는데,

아뿔사,,, RAW DATA인 CAR_RENTAL_COMPANY_RENTAL_HISTORY 는 2022-10-16을 넘어서는 렌탈 기록 까지 있어서 오답이였다. 즉 2022년 전체 자료가 있기 때문에 MAX() 처리를 한다면 거진 모든 자동차가 '대여중'으로 분류되버리는 것...

우선, 내가 배운 것들로만 머리를 쥐어 짜서 접근해보려고 했으나.. 쉽지 않았다.

그 이유는 CAR_ID가 2022-10-16 을 넘어서는 START_ID가 있고  그룹핑을 하면 대표성을 띄는 날짜로 묶이는데 여기서 난관에 부딫혔다. CAR_ID 당 MAX(END)로 SORTING 하고 진행하면 최신 대여 기록으로 그룹핑이 되나 문제에서 요구하는 '2022-10-16' 시점에서의 대여기록으로 조회되지 않는다.

 

SELECT CAR_ID,
CASE 
    WHEN MAX('2022-10-16' BETWEEN START_DATE AND END_DATE) = 1 THEN '대여중'
    ELSE '대여 가능'
END AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY 1 DESC

 

 

그래서 TRUE / FALSE의 BOOLEAN 자료형을 이용해야한다.

이때 사용할 수 있는게 MAX(X BETWEEN START_DATE AND END_DATE) = 1 조건이다.

1은 TRUE, 0은 FALSE

위의 조건을 사용하여 그룹핑을 하면 가장 큰 값이니 만약 CAR_ID 중 한번이라도 대여기간에 걸린 TRUE 값 1이 있다면 FALSE 0 값 중에는 1 값이 크니까 대여중을 출력하게 된다.

 

[그 밖에 서브쿼리를 이용한 결과들과 WITH 를 이용한 결과]

SELECT distinct CAR_ID,
    case
        when CAR_ID in (select CAR_ID
                       from CAR_RENTAL_COMPANY_RENTAL_HISTORY
                       where START_DATE <= '2022-10-16' and END_DATE >= '2022-10-16')
                       then '대여중'
        else '대여 가능' end as 'AVAILABILITY'
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
order by CAR_ID desc

# 2022-10-16에 대여중인 CAR_ID 임시 테이블
WITH RENT AS (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE
) 


SELECT A.CAR_ID,
    CASE
        WHEN B.CAR_ID IS NOT NULL THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM (SELECT DISTINCT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) A
LEFT JOIN RENT B
ON A.CAR_ID = B.CAR_ID
ORDER BY CAR_ID DESC
;