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

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

난이도: Lv.3 

언어: MySQL

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

프로그래머스

 

실패

 

실패1.

SELECT 
    b.HISTORY_ID
    , b.CAR_ID
    , IF(DATEDIFF(b.END_DATE, '2022-10-16') >= 0 AND DATEDIFF('2022-10-16', b.START_DATE) >= 0, '대여중', '대여 가능') AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY b
ORDER BY b.CAR_ID DESC, BINARY(AVAILABILITY) DESC

굉장히 지저분하게 실패한 코드..(ORDER BY로 BINARY까지 등장했다..)

BINARY를 이용하면 한글을 정렬하게 해줍니다.

실패1 쿼리 실행 결과 

실패했지만 이 쿼리에서 GROUP BY를 하면 당연히 가장 처음에 있는 행의 값으로 그룹화되니까,

30 - 대여 가능, 29 - 대여중, 28 - 대여중으로 뜰 줄 알았습니다...

SELECT a.CAR_ID, c.AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY a
    JOIN (
        SELECT 
            b.HISTORY_ID
            , b.CAR_ID
            , IF(DATEDIFF(b.END_DATE, '2022-10-16') >= 0 AND DATEDIFF('2022-10-16', b.START_DATE) >= 0, '대여중', '대여 가능') AS AVAILABILITY
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY b
        ORDER BY b.CAR_ID DESC, BINARY(AVAILABILITY) DESC
) c ON a.HISTORY_ID = c.HISTORY_ID

그래서 이렇게 위의 쿼리에서 자기 자신을 JOIN하고 출력해보니..

JOIN 이후 출력 확인

가장 위쪽의 29가 대여가능으로 떠있었습니다.

GROUP BY로 합쳐지면 대여중이아니라 대여 가능으로 당연히 뜨게 될텐데...

SELECT a.CAR_ID, c.AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY a
    JOIN (
        SELECT 
        b.HISTORY_ID
        , b.CAR_ID
        , IF(DATEDIFF(b.END_DATE, '2022-10-16') >= 0 AND DATEDIFF('2022-10-16', b.START_DATE) >= 0, '대여중', '대여 가능') AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY b
    ORDER BY b.CAR_ID DESC, BINARY(AVAILABILITY) DESC
) c ON a.HISTORY_ID = c.HISTORY_ID 
GROUP BY a.CAR_ID

역시나 현재 쿼리에서 GROUP BY를 해보니 

GROUP BY 추가 이후 출력 확인

대여 가능으로 떠있었습니다.

여기서 알 수 있는 점으로는 당연하지만서도,, JOIN을 하게되면 이전에 정렬한 정보가 사라진다는 점이 있습니다.

따라서 이런 방식으로 합치는 것은 어렵다는 것을 깨달았습니다. -> 성공1에 제가 생각했던 방식과 비슷한 구현으로 작성한 코드 추가 완료

이 방법만 2-3시간 넘게 생각했는데 ... 어떻게하지 하다가 다른분들의 정답지를 슬쩍 보았습니다...ㅎ

나중에 다시 풀어야지.......

 

성공

성공1. *실패1 코드 보완

SELECT 
     b.CAR_ID
    , MAX(IF(DATEDIFF(b.END_DATE, '2022-10-16') >= 0 AND DATEDIFF('2022-10-16', b.START_DATE) >= 0, '대여중', '대여 가능')) AS AVALIABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY b
GROUP BY b.CAR_ID
ORDER BY b.CAR_ID DESC, BINARY(AVALIABILITY) DESC

놀랍게도 실패1 쿼리에 MAX 함수만 추가하면 됩니다 ...

저는 이 문제를 맞닥뜨리기 전까지.. MAX함수가 이렇게도 사용되는지 몰랐는데..

아무튼 여기서 MAX의 용도는 '2022-10-16' 날짜에 '대여중'이 한번이라도 있는지를 검사합니다.

사전적으로 '대여중'이 '대여 가능'보다 뒤에 있기 때문에,

가장 큰 값을 봤을 때 '대여중'이 하나라도 있으면 '대여중'이 출력되고 하나라도 없으면 '대여 가능'이 출력됩니다.

이 방법을 쓰려면 MAX함수와 GROUP BY가 꼭 세트로 작성되어야 합니다. (하나라도 없으면 제대로된 결과 출력X)

* MAX 함수에 대하여 정리된 블로그

https://extbrain.tistory.com/55

[MySQL] 데이터 최대, 최소 값 가져오기 (MAX, MIN 함수)

 

* MAX를 이용한 해설 설명이 잘 되어있는 블로그

https://coduking.com/entry/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4-SQL-%EC%9E%90%EB%8F%99%EC%B0%A8-%EB%8C%80%EC%97%AC-%EA%B8%B0%EB%A1%9D%EC%97%90%EC%84%9C-%EB%8C%80%EC%97%AC%EC%A4%91-%EB%8C%80%EC%97%AC-%EA%B0%80%EB%8A%A5-%EC%97%AC%EB%B6%80-%EA%B5%AC%EB%B6%84%ED%95%98%EA%B8%B0

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

 

성공2. *채택

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

성공1을 더 깔끔하게 만든 쿼리입니다.

1. DATEDIFF을 두 번 이용, IF문을 한 번 이용한 문장을 CASE WHEN과 BETWEEN으로 가독성 있게 만들었습니다.

2. 불필요한 정렬조건(ORDER BY)인 AVALIBILITY를 제거해주었습니다.

 

성공3.

SELECT 
    DISTINCT a.CAR_ID
    , IF(
        a.CAR_ID IN (
            SELECT b.CAR_ID
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY b
            WHERE '2022-10-16' BETWEEN b.START_DATE AND b.END_DATE
        )
    , '대여중', '대여 가능')
    AS AVAILABILTIY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY a
ORDER BY a.CAR_ID DESC

IN, DISTINCT이용하고, MAX와 GROUP BY를 사용하지 않는 예제입니다.

IN을 이용해 2022-10-16의 데이터 중 CAR_ID를 추출해서 하나라도 CAR_ID가 있으면 '대여중'을 띄웁니다.

중복 제거(DISTINCT)를 하는 이유는 중복 제거를 해주지 않으면 CAR_ID가 이미 '대여중'을 띄웠지만 IF문이 또 검사를 해서 같은 CAR_ID에 대해 '대여중'을 또 한번 띄울 수 있기 때문입니다.

이 쿼리는 SELECT에 SELECT 문을 하나 더 추가하여 두 번 호출한다는 점이 썩 맘에들진 않았습니다.

그렇지만 한눈에 이해하기 쉬운 코드로 생각됩니다.

 

* IN, DISTINCT 쿼리 출처

 

https://school.programmers.co.kr/questions/50735

프로그래머스

 

*참고 chatGPT

없습니다.

 

 

---

코드에 질문이 있으시면 댓글을 달아주세요. 최대한 빠른 시일 내에 답변해드리겠습니다. 

봐주셔서 감사합니다.