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

예시

코드

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

풀이

1. 조건에 맞는 컬럼 생성
1. 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가
2. BETWEEN 은 앞 뒤 날짜 포함

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



• 각 대여 날짜를 기준으로 값이 만들어 짐
• 아이디를 기준으로 그룹핑 해야 함

2. CAR_ID 를 기준으로 그룹핑 해서 집계
1. 1단계에서 사용한 테이블을 FROM 절 서브쿼리로 사용
2. ‘대여 가능’, ‘대여중’ 두 개의 필드 값만 존재한다면 각 ROW 를 1과 0으로 변환한 뒤 SUM, MAX로 압축 가능. 중요한 값을 1로 아닌 값을 0으로 설정

SELECT
    CAR_ID,
    CASE
        WHEN SUM(CASE WHEN SUB.AVAILABILITY = '대여중' THEN 1 ELSE 0 END) = 1 THEN '대여중'
        ELSE '대여 가능'
    END
        AS AVAILABILITY
FROM
    (
        SELECT
            CAR_ID,
            CASE
                WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN '대여중'
                ELSE '대여 가능'
            END
                AS AVAILABILITY
        FROM
            CAR_RENTAL_COMPANY_RENTAL_HISTORY
    ) AS SUB
GROUP BY
    CAR_ID

3. 자동차 ID를 기준으로 내림차순 정렬

ORDER BY CAR_ID DESC;

LEFT JOIN + COALESCE 패턴은 자주 쓰는 "존재 여부 체크 + 기본값 처리" 방식

SELECT
    DISTINCT O.CAR_ID,
    COALESCE(AVAILABILITY, '대여 가능') AS AVAILABILITY
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY  AS O
LEFT JOIN
    (
        SELECT
            CAR_ID, 
            '대여중' AS AVAILABILITY
        FROM
            CAR_RENTAL_COMPANY_RENTAL_HISTORY 
        WHERE
            '2022-10-16' BETWEEN START_DATE AND END_DATE
    ) AS RENTED
ON 
    O.CAR_ID = RENTED.CAR_ID
ORDER BY 
    O.CAR_ID DESC;

풀이

1. 해당 날짜에 해당하는 ROW 를 찾아 ‘대여중’ 컬럼을 만들어 준다

SELECT
    CAR_ID, 
    '대여중' AS AVAILABILITY
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE 
    '2022-10-16' BETWEEN START_DATE AND END_DATE

2. ‘대여중’으로 만든 테이블을 원본테이블에 LEFT JOIN 한다
1. 값이 없으면 NULL 이 들어간다
2. 같은 CAR_ID 에 대해 모든 ROW 에 ‘대여중’으로 표기된다. 날짜가 ‘대여중’ 날짜가 아니더라도 한번이라도 ‘대여중’ 날짜에 해당하면 모두 ‘대여중’으로 표기한다

SELECT
    *
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY  AS O
LEFT JOIN
    (
        SELECT
            CAR_ID, 
            '대여중' AS AVAILABILITY
        FROM
            CAR_RENTAL_COMPANY_RENTAL_HISTORY 
        WHERE
            '2022-10-16' BETWEEN START_DATE AND END_DATE
    ) AS RENTED
ON 
    O.CAR_ID = RENTED.CAR_ID

5. CAR_ID 를 중복제거하고 빈 칸에 ‘대여 가능’을 표기한다
1. DISTINCT 를 사용해 중복된 CAR_ID 를 제거한다. 어차피 CAR_ID 에 대해 모두 ‘대여중’, NULL 이 들어가 있기 때문에 중복제거해도 된다
2. COALESCE 는 첫 번째 값이 NULL 이면 다음 값을 넣는다

SELECT
    DISTINCT O.CAR_ID,
    COALESCE(AVAILABILITY, '대여 가능') AS AVAILABILITY
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY  AS O
LEFT JOIN
    (
        SELECT
            CAR_ID, 
            '대여중' AS AVAILABILITY
        FROM
            CAR_RENTAL_COMPANY_RENTAL_HISTORY 
        WHERE
            '2022-10-16' BETWEEN START_DATE AND END_DATE
    ) AS RENTED
ON 
    O.CAR_ID = RENTED.CAR_ID
ORDER BY 
    O.CAR_ID DESC;