[day9] history 리스트에서 특정 날짜 기준으로 대여 가능 여부 확인하기

여태 풀었던 문제 중에 제일 어려웠다..

방법도 여러 가지이고, 논리적 사고가 매우 요구되는 문제이다.

주기적으로 찾아와서 공부하려고 글을 작성한다.

---

Q. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

---

방법1. MAX 함수 활용하기

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

 

⭐ MAX 사용 이유

하나의 CAR_ID당 여러 개의 렌탈 내역이 있을 수 있다.

그 상태에서 GROUPING을 진행할 때 AVAILABILITY 열에 '대여중', '대여가능' 값이 모두 있으면 '대여중'을 뽑아내야 한다.

이 때, 문자데이터로서 순서를 매겼을 때 '대여중'이 '대여가능'보다 뒤에 있으므로 MAX값 '대여중'을 사용한다.

(ㄱㄴㄷ순 생각하면 됨)

 

 

방법2. 스칼라 서브쿼리 + IN 활용하기

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

" 2022-10-16에 대여중인 CAR_ID 만 따로 테이블을 만들어 놓고

그 테이블에 속하면 '대여중' 아니면 '대여 가능'으로 변환해줘"

 

 

방법3. 조건이 TRUE 이면 1 FALSE 이면 0

SELECT CAR_ID,
       IF(SUM(DATE('2022-10-16') BETWEEN start_date AND end_date) = 1,'대여중','대여 가능') 
       AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY 1
ORDER BY 1 DESC

같은 팀원분이 푸신 쿼리를 보고 와 이런 방법도 있구나 ... 개성있다! 고 느꼈다.

⭐ 새로 알게된 것

DATE('2022-10-16') BETWEEN start_date AND end_date) 값이

TRUE 이면 1, FALSE 이면 0 이 나온다.

이를 SUM과 함께 활용해서 1 값이 하나라도 있으면 '대여중', 하나도 없으면 '대여 가능'으로 변환하는 형식으로 IF문을 사용한 쿼리이다.

당연히 하나의 CAR_ID '대여중'인 값은 1개밖에 없기 때문에 나는 '=1' 을 사용했지만 '>0'도 가능하다 !