[MySQL] 프로그래머스 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

https://school.programmers.co.kr/learn/courses/30/lessons/157340

프로그래머스

코딩테스트 연습 > GROUP BY > 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

 

문제 분석

난이도: LEVEL3

 

문제 요구사항

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 

이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

---

정답 코드1

WITH CTE AS (
    SELECT DISTINCT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE CAR_ID NOT IN (SELECT DISTINCT CAR_ID 
                         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                         WHERE START_DATE <= '2022-10-16' AND '2022-10-16' <= END_DATE
                         )             
)
SELECT 
H.CAR_ID,
CASE
    WHEN C.CAR_ID IS NULL THEN '대여중'
    ELSE '대여 가능'
END AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H LEFT JOIN CTE C 
ON H.CAR_ID = C.CAR_ID 
GROUP BY H.CAR_ID
ORDER BY 1 DESC

 

CTE를 활용해서 풀었다. 

 

상세한 코드 분석 

WITH CTE AS (
    SELECT DISTINCT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE CAR_ID NOT IN (SELECT DISTINCT CAR_ID 
                         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                         WHERE START_DATE <= '2022-10-16' AND '2022-10-16' <= END_DATE
                         )             
)

먼저 CTE에서 대여기간에 '2022-10-16'이 포함되지 않는 CAR_ID를 구했다. 

 

FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H LEFT JOIN CTE C 
ON H.CAR_ID = C.CAR_ID 
GROUP BY H.CAR_ID
ORDER BY 1 DESC

 

상태가 대여 가능이던 대여중이던 모든 CAR_ID가 나와야 하기 때문에

CAR_RENTAL_COMPANY_RENTAL_HISTORY 와 내가 구한 CTE를 LEFT JOIN 하고 

GROUP BY를 통해서 CAR_ID를 기준으로 묶었다. 

또한 CAR_ID 기준 내림차순 

 

SELECT 
H.CAR_ID,
CASE
    WHEN C.CAR_ID IS NULL THEN '대여중'
    ELSE '대여 가능'
END AVAILABILITY

만약 CTE에 CAR_ID가 존재한다면 해당 CAR_ID는 대여 가능이다. 

---

정답 코드2

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

 

집계 함수 MAX 활용하기 

GROUP BY CAR_ID 했을 때 

각 START_DATE, END_DATE 사이에 2022-10-16 있으면 TRUE OR FALSE를 반환한다. 

여기에 MAX 집계 함수를 사용하면 TRUE 는 1로 변환 FALSE는 0으로 변환 

가장 큰 값이니 만약 한번이라도 대여 기간에 2022-10-16이 있으면 MAX 결과는 1이고 그럼 대여중이다.