[LV.4] GROUP BY > 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 ⭐

문제

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

프로그래머스

---

문제 설명

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

---

정답 코드

SELECT CAR_ID,
       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 (SELECT DISTINCT CAR_ID
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) AS CARS
ORDER BY CAR_ID DESC

---

코드 설명

• SELECT DISTINCT CAR_ID
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
  • 서브쿼리) 중복된 대여 기록이 있을 수 있기 때문에 DISTINCT를 사용해
    모든 자동차 ID를 한 번씩만 가져오기
  • 이 목록을 기준으로 각각의 자동차에 대해 대여 상태 판단
• CASE WHEN CAR_ID IN (...)
  • 2022년 10월 16일이 START_DATE와 END_DATE 사이에 포함되는 대여 이력이 있는 자동차만 별도로 조회

---

개선 코드 (IN 대신 EXISTS)

SELECT CAR_ID,
       CASE WHEN EXISTS (SELECT 1
                         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
                         WHERE H.CAR_ID = C.CAR_ID
                         AND '2022-10-16' BETWEEN H.START_DATE AND H.END_DATE) THEN '대여중'
       ELSE '대여 가능' END AS AVAILABILITY
FROM (SELECT DISTINCT CAR_ID
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) AS C
ORDER BY CAR_ID DESC

• 각 자동차 ID(C.CAR_ID)에 대해, 2022년 10월 16일이 START_DATE와 END_DATE 사이에 포함되는 대여 이력이 존재하는지를 확인
• EXISTS는 그런 이력이 하나라도 존재하면 TRUE를 반환하므로 '대여중', 그렇지 않으면 '대여 가능'으로 분류

• 'SELECT 1'을 쓰는 이유?
  • EXISTS는 "해당 조건을 만족하는 행이 존재하느냐"만 판단
  • 따라서 SELECT 뒤에 어떤 값을 넣든 상관없으며, 일반적으로 *가장 단순한 정수 1*을 넣는 것이 관례
    (성능도 SELECT *보다 더 효율적)
• 왜 EXISTS가 좋은가?
  • IN이나 JOIN을 사용할 수도 있지만, 이 문제처럼 조건을 만족하는 "존재 여부만" 확인하는 경우에는 EXISTS가 더 효율적
  • EXISTS는 조건을 만족하는 첫 번째 행이 확인되면 즉시 종료되므로, 불필요한 전체 검색을 피할 수 있어 성능상 유리

---

첫 번째 시도 (실패)

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

---

틀린 이유

• CAR_ID별로 중복된 결과가 여러 행 출력
  • 즉, 하나의 자동차가 여러 대여 기록을 가지면 여러 줄 출력
• 날짜에 포함된 한 건이라도 있으면 '대여중'으로 표시해야 하는데,
  각 대여 기록을 개별 판단해서 줄 단위로 판단

---

두 번째 시도 (실패)

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

---

틀린 이유

• GROUP BY를 쓰면 집계가 필요
  • GROUP BY CAR_ID → 같은 CAR_ID 값을 가진 여러 줄을 하나로 묶겠다는 뜻
  • 근데, 묶을 때 '어떤 START_DATE, END_DATE를 대표로 쓸 건지'를 SQL이 알 수가 없기 때문에 에러날 가능성 존재
• 예시) 다음과 같은 데이터가 있다면

• GROUP BY CAR_ID를 하면
  → 두 줄이 CAR_ID = 2로 묶이는데
  → 어떤 START_DATE, END_DATE를 기준으로 '대여중'을 판단할 건지 애매
• 즉, CASE문이 한 줄만 보고 판단하게 되어 전체 대여 이력을 반영하지 못함 (대여 가능이라고 잘못 판단할 수 있음)