[JOIN] 특정 기간동안 대여 가능한 자동차들

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

프로그래머스

---

문제 설명
다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 입니다.

CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며, CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.

Column name	Type	Nullable
CAR_ID	INTEGER	FALSE
CAR_TYPE	VARCHAR(255)	FALSE
DAILY_FEE	INTEGER	FALSE
OPTIONS	VARCHAR(255)	FALSE

자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진' 이 있습니다.

자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(예: ''열선시트,스마트키,주차감지센서'')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

Column name	Type	Nullable
HISTORY_ID	INTEGER	FALSE
CAR_ID	INTEGER	FALSE
START_DATE	DATE	FALSE
END_DATE	DATE	FALSE

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은 아래와 같은 구조로 되어있으며, PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE 는 각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다.

Column name	Type	Nullable
PLAN_ID	INTEGER	FALSE
CAR_TYPE	VARCHAR(255)	FALSE
DURATION_TYPE	VARCHAR(255)	FALSE
DISCOUNT_RATE	INTEGER	FALSE

할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.

---

문제
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

---

예시
예를 들어 CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블이 다음과 같다면

 

<CAR_RENTAL_COMPANY_CAR> 테이블

CAR_ID	CAR_TYPE	DAILY_FEE	OPTIONS
1	SUV	25000	가죽시트,열선시트,후방카메라
2	세단	14000	스마트키,네비게이션,열선시트
3	트럭	32000	주차감지센서,후방카메라,가죽시트
4	세단	12000	열선시트,후방카메라
5	세단	22000	스마트키,주차감지센서

 

<CAR_RENTAL_COMPANY_RENTAL_HISTORY> 테이블

HISTORY_ID	CAR_ID	START_DATE	END_DATE
1	1	2022-08-27	2022-09-02
2	1	2022-10-03	2022-10-04
3	2	2022-10-05	2022-10-20
4	2	2022-10-10	2022-11-12
5	3	2022-10-16	2022-10-17

 

<CAR_RENTAL_COMPANY_DISCOUNT_PLAN> 테이블

PLAN_ID	CAR_TYPE	DURATION_TYPE	DISCOUNT_RATE
1	트럭	7일 이상	5%
2	트럭	30일 이상	7%
3	트럭	90일 이상	10%
4	세단	7일 이상	5%
5	세단	30일 이상	10%
6	세단	90일 이상	15%
7	SUV	7일 이상	3%
8	SUV	30일 이상	8%
9	SUV	90일 이상	12%

자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일 부터 2022년 11월 30일까지 대여가능한 자동차는 자동차 ID가 1, 4, 5인 자동차입니다.

일일 대여 요금에 자동차 종류 별 대여기간이 30일 이상인 경우의 할인율을 적용하여 30일간의 대여 금액을 구하면,

• 자동차 ID가 1인 경우, 일일 대여 금액 25,000원에서 8% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 690,000원
• 자동차 ID가 4인 경우, 일일 대여 금액 12,000원에서 10% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 324,000원
• 자동차 ID가 5인 경우, 일일 대여 금액 22,000원에서 10% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 621,000원이고, 대

여 금액이 50만원 이상 200만원 미만인 경우에 대해서 대여 금액을 기준으로 내림차순, 자동차 종류를 기준으로 오름차순 및 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같아야 합니다.

CAR_ID	CAR_TYPE	FEE
5	세단	690000
1	SUV	621000

---

주의사항
FEE의 경우 예시처럼 정수부분만 출력되어야 합니다.

---

2022년 11월 1일부터 2022년 11월 30일까지 대여 가능한 세단과 SUV의 30일 대여 금액을 구하는 문제입니다.

 

이 문제... 생각보다 까다롭습니다. LEFT OUTER JOIN을 활용해 기간별로 기록을 찾은 뒤, 기록이 비어있는 행만을 추출해서 가져와야 대여 가능한 차량을 정확히 구할 수 있습니다.

차량의 대여가 여러 번 이뤄지기 때문에 기간을 구할 때 INNER JOIN으로는 풀 수가 없습니다. 범위 내에 '없는 값'들을 찾아야 하는 건데 LEFT OUTER JOIN의 사용이 필수적입니다.

 

기간을 구하는 것 이외에는 어려운 부분은 없습니다. 반대로 말하면 기간에 맞는 값을 구하는 게 문제의 90%를 차지한다는 뜻이죠.

 

정답 코드를 작성해보겠습니다.

  SELECT C.CAR_ID, C.CAR_TYPE, FLOOR(C.DAILY_FEE * (1 - P.DISCOUNT_RATE * 0.01) * 30) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR C
         LEFT OUTER JOIN
         CAR_RENTAL_COMPANY_RENTAL_HISTORY H
         ON C.CAR_ID = H.CAR_ID
            AND (H.START_DATE <= '2022-11-30' AND H.END_DATE >= '2022-11-01')
         INNER JOIN
         CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
         ON C.CAR_TYPE = P.CAR_TYPE
            AND C.CAR_TYPE IN ('SUV', '세단')
            AND P.DURATION_TYPE LIKE "30일%"
   WHERE H.HISTORY_ID IS NULL
GROUP BY C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE
  HAVING FEE BETWEEN 500000 AND 2000000
ORDER BY 3 DESC, 2 ASC, 1 DESC;

 

자동차 정보를 기준으로 대여 기록과 LEFT OUTER JOIN을 실행합니다.

조건은 자동차 ID가 동일하고, 대여 시작일이 2022년 11월 30일 이전이며 대여 종료일이 2022년 11월 1일 이후인 값들입니다.

 

이러면 2022년 11월동안 대여 중인 차량이 나오는 게 아닌가요? 라고 생각하실 수 있는데, 맞습니다.

모든 차량에 대해서 2022년 11월동안 대여 중인 차량의 정보들을 출력하는데, 기록이 없더라도 출력하도록 설정된 조인입니다.

 

중간 점검용으로 코드를 확인해보면 다음과 같습니다.

  SELECT C.CAR_ID, C.CAR_TYPE, H.START_DATE, H.END_DATE
    FROM CAR_RENTAL_COMPANY_CAR C
         LEFT OUTER JOIN
         CAR_RENTAL_COMPANY_RENTAL_HISTORY H
         ON C.CAR_ID = H.CAR_ID
            AND (H.START_DATE <= '2022-11-30' AND H.END_DATE >= '2022-11-01');

 

코드의 실행 결과입니다.

CAR_ID	CAR_TYPE	START_DATE	END_DATE
1	트럭	2022-10-31 00:00:00	2022-11-30 00:00:00
2	SUV	2022-10-10 00:00:00	2023-01-11 00:00:00
3	세단
4	SUV	2022-10-31 00:00:00	2022-11-01 00:00:00
5	SUV	2022-09-23 00:00:00	2022-11-02 00:00:00

 

보시다시피 자동차별 11월동안 대여 기록이 있는 값들이 모두 추출되었습니다.

다시 말해 이 기록에 값이 없다면, 해당 차량은 11월 30일동안 대여할 수 있는 차량이라는 것을 의미합니다.

이 테이블을 가지고, WHERE 조건절에서 H.HISTORY_ID IS NULL을 사용한다면 비어 있는 기록을 가진 차량들을 추출해낼 수 있습니다.

 

할인률을 검사하는 건 어렵지 않습니다. 차량에 대한 할인 조건은 모두 INNER JOIN을 통해 가져올 수 있고, 그 중 30일만을 대여하기 때문에 ON이든 WHERE이든 DURATION_TYPE이 30일 이상인 행들만을 가져오면 됩니다.

 

30일 간의 총금액을 계산하는 건 FLOOR(C.DAILY_FEE * (1 - P.DISCOUNT_RATE * 0.01) * 30) AS FEE로, 다음과 같은 식으로 계산했습니다. 참고로 이 금액은 50만원 이상 200만원 이하여야 합니다.

30일 간의 총금액 = 일일 대여가 * (1 - 할인율 * 0.01) * 30(일)

 

GROUP BY를 사용하지 않고, 그대로 DISTINCT와 WHERE 절을 사용하는 것으로 FEE 조건을 만족시킬 수도 있습니다.

하지만 굳이? 라는 생각이 드네요. 코드가 매우 길어지고 에일리어싱을 사용할 수 없습니다.

HAVING절에는 에일리어싱한 열을 사용할 수 있고, GROUP BY로 중복값들 처리도 할 수 있으므로 저는 GROUP BY를 사용했습니다.

 

마지막으로 ORDER BY절을 사용해 열 번호를 사용해 정렬해준다면, 문제를 해결할 수 있습니다.

 

기간을 구하는 게 제일 까다로운 문제였습니다. 재밌게 풀었네요.