[프로그래머스, SQL] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

✏️ 아이디어

CAR_ID, CAR_TYPE, 계산식 AS 'FEE' 형태로 출력하자

자동차 종류는 세단, SUV만 필요하니까 -> CAR_TYPE IN ('세단', 'SUV')

2022-11-01 ~ 2022-11-30 사이 조건은 -> BETWEEN '2022-11-01' AND '2022-11-30'으로 걸면 될 것 같다.

30일간 대여금액이 50만원 이상 200만원 미만이어야 하니까 -> DAILY_FEE * 30 * (1 - DISCOUNT_RATE / 100) BETWEEN 500000 AND 2000000
그리고 DURATION_TYPE = '30일 이상'인 DISCOUNT_RATE를 가져와서 계산하면 될 것 같다.

정렬은 ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC로.

💻 코드1 (실패)

SELECT C.CAR_ID, C.CAR_TYPE, (C.DAILY_FEE * 30 * (1 - D.DISCOUNT_RATE / 100)) AS 'FEE'
FROM CAR_RENTAL_COMPANY_CAR C
         JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
              ON C.CAR_ID = H.CAR_ID
         JOIN (SELECT CAR_TYPE, DISCOUNT_RATE
               FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
               WHERE CAR_TYPE IN ('세단', 'SUV')
                 AND DURATION_TYPE = '30일 이상') D
              ON C.CAR_TYPE = D.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV')
  AND (H.START_DATE > '2022-11-30' OR H.END_DATE <= '2022-11-01')
  AND C.DAILY_FEE * 30 * (1 - D.DISCOUNT_RATE / 100) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

🔍 왜 실패했을까

핵심 원인은 히스토리 테이블을 JOIN하고 그 결과 행에 조건을 건 부분이었다.

 

자동차 한 대는 대여 기록이 여러 개 있을 수 있는 1:N 관계다.

JOIN을 하면 자동차마다 히스토리 개수만큼 행이 늘어나는데, 코드1은 이렇게 늘어난 행 하나하나에 대해

"이 기록이 11월과 안 겹치니?"라는 조건을 걸었다. 이러면 자동차가 가진 여러 히스토리 중 단 하나라도 안 겹치는 기록이 있으면 그 행이 조건을 통과해서 자동차가 결과에 포함돼 버린다.

 

실제로 예시 데이터의 CAR_ID 2를 보면 히스토리가 두 개다.

하나(10/5~10/20)는 11월과 안 겹치고, 다른 하나(10/10~11/12)는 겹친다.

코드1의 조건대로면 안 겹치는 쪽 행만 조건을 통과해서 CAR_ID 2가 "11월에 대여 가능"으로 잘못 포함된다.

실제로는 11/12까지 대여 중이라 11월 1일엔 대여할 수 없는 차인데도 말이다...

 

"이 자동차는 11월과 겹치는 기록이 하나도 없어야 한다"는 조건은 행 단위 필터로는 표현이 안 되고,

NOT EXISTS로 "겹치는 기록이 하나도 없다"를 통째로 확인해야 정확했다.

 

여기에 더해 FLOOR()도 빠져 있었다. 문제에 "FEE는 정수부분만 출력"이라는 조건이 있었는데, 코드1은 계산식 결과를 그대로 출력해서 소수점이 남아있을 수 있었다.

 

상당히 어렵다 ㅠ

💻 코드2 (성공)

SELECT C.CAR_ID, C.CAR_TYPE, FLOOR(C.DAILY_FEE * 30 * (1 - D.DISCOUNT_RATE / 100)) AS 'FEE'
FROM CAR_RENTAL_COMPANY_CAR C
         JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
              ON C.CAR_TYPE = D.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV')
  AND D.DURATION_TYPE = '30일 이상'
  AND C.DAILY_FEE * 30 * (1 - D.DISCOUNT_RATE / 100) BETWEEN 500000 AND 1999999
  AND NOT EXISTS(
    SELECT 1
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    WHERE C.CAR_ID = H.CAR_ID
      AND H.START_DATE <= '2022-11-30'
      AND H.END_DATE >= '2022-11-01')
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

📖 새로 배운 부분

1. IN은 값을 비교하는 거고, EXISTS는 서브쿼리에 행이 있는지 없는지만 확인하는 거라는 걸 구분해서 이해했다.
EXISTS (SELECT 1 ...)에서 1은 아무 의미가 없고, 관례적으로 사용한다고 한다.

2. NOT EXISTS 안에서 바깥 쿼리의 컬럼(C.CAR_ID)을 참조하는 걸 상관 서브쿼리(Correlated Subquery)라고 부른다는 걸 처음 알았다. 바깥 쿼리의 현재 행을 기준으로 안쪽 쿼리가 매번 다시 실행되는 구조였다.

3.
22.11.01 ~ 22.11.30 과 안겹치려면
(시작 ~ 끝) --- 22.11.01 ~ 22.11.30 --- (시작 ~ 끝)
두가지 경우의 수밖에 없다

START > '2022-11-30' 
OR
END < '2022-11-01'

 

이와 정 반대되는 (무조건 실패하는) 경우의 수를 식으로 구한다면

START <= '2022-11-30'
AND
END >= '2022-11-01'

 

이 경우이다!! (부등호와 AND 연산자를 그대로 뒤집으면 된다)

 

4. 1:N 관계인 테이블을 JOIN하면 행이 늘어나고, 그 늘어난 행에 WHERE로 조건을 걸면 "전체 중 하나라도 조건을 만족"하는 것처럼 동작해버릴 수 있다는 걸 이번에 직접 겪었다. "조건을 만족하는 게 하나도 없어야 한다"를 표현하려면 JOIN보다 NOT EXISTS가 맞았다.

 

5. BETWEEN은 양쪽 값을 모두 포함한다. "200만원 미만"처럼 초과/미만 조건을 표현하려면 BETWEEN 500000 AND 1999999처럼 값 자체를 조정하거나, >= 500000 AND < 2000000으로 따로 써야 한다. FEE가 FLOOR로 정수가 된 상태라 1999999를 상한으로 잡을 수 있었다.

참고

• 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 - 프로그래머스

프로그래머스