문제
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.
💡 조건
- 22년 10월 16일에 대여중인 경우 '대여 중' 표시, 아닌 경우 '대여 가능' 표시
- 대여중, 대여가능 칼럼은 'AVAILABILITY'
- 자동차 ID, 대여가능여부 출력
- 자동차 ID기준 내림차순 정렬
sql코드
SELECT CAR_ID,
MAX(CASE
WHEN (START_DATE <= '2022-10-16' AND END_DATE >='2022-10-16') THEN '대여중'
ELSE '대여 가능'
END) AS AVAILABILTY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC- 22년 10월 16일에 대여중인 경우 '대여 중' 표시, 아닌 경우 '대여 가능' 표시
- 16일 해당하는 경우도 대여 중인 것으로 표현하므로 시작일은 16일 이하, 끝나는 일자는 16일이후로 잡게 되면 16일이 들어있으면 대여중으로 표시한다
MAX(CASE
WHEN (START_DATE <= '2022-10-16' AND END_DATE >='2022-10-16') THEN '대여중'
ELSE '대여 가능'
END) AS AVAILABILTYCASE-WHEN-END를 사용해서 조건을 걸어주었다. 여기서 가장 까다로웠던 부분은 MAX를 사용하는 부분이었는데 GROUP BY로 car_id를 묶어주고, MAX를 사용해서 같은 car_id중에 가장 큰 값을 출력해준다.
MAX를 사용하는 부분에 대해서는 GPT를 참고했다.
CASE 문의 결과를 MAX() 함수로 감싸면, 그룹화된 각 자동차별로 가장 큰 값 (여기서는 "대여중"이 우선 순위가 높습니다)을 선택합니다. 따라서 각 자동차에 대해 최신 대여 기록의 상태가 선택됩니다.
"대여중"의 우선순위가 높은 이유는 해당 쿼리에서 조건을 만족하는 경우 "대여중"을 먼저 표시하고, 그 후에 "대여 가능"을 표시하기 위함입니다. 즉, 해당 자동차가 2022년 10월 16일에 대여 중인 경우에는 그것을 우선적으로 표시하려는 것입니다.
MAX() 함수를 사용하면 결과 집합에서 가장 큰 값을 선택하게 됩니다. 여기서 "대여중"이라는 값이 "대여 가능"보다 큰 값을 갖게 되었습니다. 이런 우선순위 설정을 통해, 최신 대여 기록 중에서 "대여중"인 상태가 있다면 해당 자동차의 전체 상태를 "대여중"으로 표시하고, 그렇지 않은 경우에는 "대여 가능"을 표시하도록 한 것입니다.
결과적으로 "대여중"이 "대여 가능"보다 우선순위가 높다는 것은 최신 대여 기록을 기준으로 자동차의 현재 상태를 표시할 때, 대여 중인 상태가 더 중요하다고 판단한 것입니다.
대여중인 경우가 중요하게 여겨지므로 (대여중이면 대여할 수가 없으므로) MAX를 사용해서 각 ID마다 대여중인게 있다면 그것을 출력해준다.
** 추가로 between을 사용해서 쿼리를 작성할 수도 있다.
if('2022-10-16' between START_DATE and END_DATE, '대여중', '대여 가능')- 대여중, 대여가능 칼럼은 'AVAILABILITY'
- AS를 사용해서 칼럼 이름 적용
- 자동차 ID, 대여가능여부 출력
- SELECT문에 작성
- 자동차 ID기준 내림차순 정렬
- ORDER BY CAR_ID DESC
