[MYSQL] PROGRAMMERS_자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

 

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  AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    GROUP BY 1
    ORDER BY 1 desc

문제

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

 

 다뤄야할 조건이 조금 많아 보인다(색깔로 표시) 이렇게 하나의 컬럼에 조건이 많을경우는 CASE WHEN을 사용한다. 주의 해야할 점이 하나의 자동차에 다양한 START_DATE와 END_DATE 가 있다는 점인데 따라서 하나의 값이라도 22년 10월 16일 이라면 대여중이라는 값이 나와야한다. 따라서 서브쿼리를 사용 했다. 꽤 복잡하다. 

 

 2열부터 보면 만약에 CAR ID가 서브쿼리 를 만족하는 CAR_ID들과 (in) 일치하면 (then) "대여중" 이라는 구문이다. 여기서 서브쿼리는 테이블 내의 모든 행에 대해서 각각의 행안에 시작일과 끝나는 날의 범위 안에 22년 10월 16일 이 있는 데이터의 필터링하여 CAR_ID를 추출해 오는것이다. 그 게 존재하는 애들이 있다면 대여중으로 뜬다. (이렇게 하면 같은 CAR ID에 10월 16일이 없더라도 한번이라도 있으면 값으로 추출 되어 비교하게 해준다.) 그리고 이외는 대여가능으로 작성한다. 그리고 나머지는 GROUP BY해서 값 중복을 제거해주거나 GROUP BY 대신 distinct를 사용해도 무방하다. (이미 같은 CAR ID 값을 가진 데이터들의 AVAILABILITY 값은 모두 일치가 되었기 때문이다)

 

추가로 다른 사람이 풀어낸 코드를 한번 봤는데

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 CAR_ID
ORDER BY CAR_ID DESC;

 

이런 코드가 있었습니다. 조금 야매(?) 의 느낌이 나는 코드인데 기능은 하니까 한번 살펴 보았습니다.

서브쿼리 안에 CASE WHEN을 사용 하였고 BETWEEN 부분은 동일하게 진행하였습니다. 그리고 마무리로 MAX를 사용후 GROUP BY 처리를 하였습니다. 여기서 MAX의 의미는 무엇이나면 일단 서브쿼리를 봐야합니다. 서브쿼리 내에는 A조건이면 "대여중" 그외는 "대여 가능" 으로 하여 처리 했습니다. 그 컬럼의 이름은 AVAILABILITY 이구요.   AVAILABILITY 컬럼 내에는 단 2개의 값만 존재 하게 된 것 입니다. (대여중, 대여 가능) 그 때 MAX 함수가 비교할것은 두 문자의 크기 비교입니다. (MAX는 문자열도 비교가능합니다.) 이것의 비교는 ASCIl 이나 UNICODE로 비교하게 되는데 사실상 UNICODE 표는 너무많으니 이해하기 쉽게 아스키 코드로 봅니다. 

위 사진을 보면 Space(공백) 이 다른 어떤 문자 보다 맨 앞에 있다는것을 볼 수 있습니다. 즉 공백은 어떤 값보다 가장 작은 값을 가지는 문자(?) 라고 볼 수있습니다. 

 

 따라서 MAX( AVAILABILITY) 의 경우에는 "대여중" 이 최대값이 됩니다. 

이를 CAR_ID와 GROUP BY로 묶어 데이터를 뽑게 되면 CAR_ID별로 "대여중"의 값을 가지면 최대값인 "대여중"을 표기 그렇지 않으면 "대여 가능"을 표기하게 됩니다. 왜 그런가하면 단 한번이라도 "대여중" 이 있다면 "대여중" 으로 표기해야 정답이 되기때문입니다.

 

 표현이 조금 이상한데 "대여 가능" 의 상태가 존재하는 여러 값이 있더라도 "대여중" 인 상태면 결국 대여중인 차량이기 때문입니다. 과거 이력이 어쨋건 미래에 대여될 상황이 어쨋건 그건 다 "대여 가능" 상태로 뜨지만 10월 16일인 상황에 "대여중" 인 것이니 말입니다. 

결과적으로 그 값을 표현 하기 위해 MAX() 함수를 사용하여 약간은 야매(?) 로 푼 쿼리 같습니다.