[LV.5] String, Date > 자동차 대여 기록 별 대여 금액 구하기 ⭐️

문제

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

프로그래머스

---

문제 설명

• CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과
  CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서
  대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성
• 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬
• FEE의 경우 정수부분만 출력되어야 함

---

정답 코드

WITH TRUCK AS (SELECT CAR_ID, DAILY_FEE
               FROM CAR_RENTAL_COMPANY_CAR
               WHERE CAR_TYPE = '트럭'),
DISCOUNT_PLAN AS (SELECT CAST(SUBSTRING_INDEX(DURATION_TYPE, '일', 1) AS UNSIGNED) AS MIN_DAYS,
                         DISCOUNT_RATE
                  FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                  WHERE CAR_TYPE = '트럭'),
RENT_DAYS AS (SELECT HISTORY_ID,
                     CAR_ID,
                     DATEDIFF(END_DATE, START_DATE) + 1 AS RENTAL_DAYS
              FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY),
BEST_DISCOUNT AS (SELECT R.HISTORY_ID,
                         R.CAR_ID,
                         T.DAILY_FEE,
                         R.RENTAL_DAYS,
                         P.DISCOUNT_RATE,
                         ROW_NUMBER() OVER (PARTITION BY R.HISTORY_ID
                                            ORDER BY P.MIN_DAYS DESC) AS RN
                  FROM RENT_DAYS R JOIN TRUCK T ON R.CAR_ID = T.CAR_ID
                                   LEFT JOIN DISCOUNT_PLAN P ON R.RENTAL_DAYS >= P.MIN_DAYS)

SELECT HISTORY_ID,
       FLOOR(DAILY_FEE * RENTAL_DAYS * (1 - COALESCE(DISCOUNT_RATE, 0) / 100)) AS FEE
FROM BEST_DISCOUNT
WHERE RN = 1
ORDER BY FEE DESC, HISTORY_ID DESC

---

코드 설명

1. 'TRUCK' CTE
→ 불필요한 차량 제거

WITH TRUCK AS (SELECT CAR_ID, DAILY_FEE
               FROM CAR_RENTAL_COMPANY_CAR
               WHERE CAR_TYPE = '트럭'),

• 차량 테이블에서 '트럭'만 필터링
• 필요한 칼럼 CAR_ID, DAILY_FEE (일일 요금)만 선택

 

2. 'DISCOUNT_PLAN' CTE

→ 숫자 비교 가능한 형식으로 변환해 나중에 대여일과 비교하기 위함

DISCOUNT_PLAN AS (SELECT CAST(SUBSTRING_INDEX(DURATION_TYPE, '일', 1) AS UNSIGNED) AS MIN_DAYS,
                         DISCOUNT_RATE
                  FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                  WHERE CAR_TYPE = '트럭'),

• 할인 정책 중 CAR_TYPE = '트럭'만 필터링
• DURATION_TYPE은 문자열(예: "7일 이상")이므로, SUBSTRING_INDEX로 일 앞 숫자 추출
• 이 숫자를 UNSIGNED INT로 바꿔 MIN_DAYS로 저장

 

3. 'RENT_DAYS' CTE

→ 대여 기간에 따라 적절한 할인율을 찾기 위함

RENT_DAYS AS (SELECT HISTORY_ID,
                     CAR_ID,
                     DATEDIFF(END_DATE, START_DATE) + 1 AS RENTAL_DAYS
              FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY),

• 대여 이력 테이블에서 렌트 일수를 계산
• DATEDIFF는 종료일 - 시작일이므로, 하루짜리 렌트도 포함되도록 +1

 

4. 'BEST_DISCOUNT' CTE

→ 대여일에 가장 적절한 할인율 '하나만' 선택하기 위함

BEST_DISCOUNT AS (SELECT R.HISTORY_ID,
                         R.CAR_ID,
                         T.DAILY_FEE,
                         R.RENTAL_DAYS,
                         P.DISCOUNT_RATE,
                         ROW_NUMBER() OVER (PARTITION BY R.HISTORY_ID
                                            ORDER BY P.MIN_DAYS DESC) AS RN
                  FROM RENT_DAYS R JOIN TRUCK T ON R.CAR_ID = T.CAR_ID
                                   LEFT JOIN DISCOUNT_PLAN P ON R.RENTAL_DAYS >= P.MIN_DAYS)

• RENT_DAYS와 TRUCK을 조인해서 대여 정보에 일일 요금 결합
• LEFT JOIN으로 RENTAL_DAYS >= MIN_DAYS 조건에 맞는 할인율만 연결
  • 즉, 대여 기간이 할인 조건을 만족하면 해당 할인 연결
• ROW_NUMBER() 사용해, 같은 이력에 대해 가장 큰 MIN_DAYS (최우선 할인)을 RN = 1로 매김

 

5. 최종 SELECT 구문

SELECT HISTORY_ID,
       FLOOR(DAILY_FEE * RENTAL_DAYS * (1 - COALESCE(DISCOUNT_RATE, 0) / 100)) AS FEE
FROM BEST_DISCOUNT
WHERE RN = 1
ORDER BY FEE DESC, HISTORY_ID DESC

• 할인율은 0일 수도 있으니 COALESCE(DISCOUNT_RATE, 0)로 처리
• 전체 요금 = 일일요금 × 대여일수 × (1 - 할인율)
• FLOOR()는 소수점 아래 버림 (정수 요금 처리)
• RN = 1 조건으로 각 렌트 이력마다 가장 적합한 할인만 반영
• 정렬 기준:
  • 요금 높은 순
  • 요금 동일 시, 이력 번호(HISTORY_ID)가 큰 순

---

함수 설명

SUBSTRING_INDEX() 함수

• 기본 문법

SUBSTRING_INDEX(str, delimiter, count)

• 문자열 str에서 delimiter(구분자)를 기준으로 count번째 앞부분까지 잘라냄
• count > 0: 왼쪽부터 자름
• count < 0: 오른쪽부터 자름

---

CAST(... AS UNSIGNED)

• 기본 문법

CAST(expression AS UNSIGNED)

• expression을 정수형 숫자 (양의 정수) 로 바꾸기
• 문자열에서 숫자만 있는 부분을 변환할 때 유용

---

ROW_NUMBER() 함수

ROW_NUMBER() OVER (PARTITION BY [컬럼]
                   ORDER BY [컬럼] [정렬방향]) AS [별칭]

• 각 행에 대해 고유한 순번 생성
• 순번은 PARTITION BY로 나눈 그룹 안에서, ORDER BY 기준으로 매겨짐
• 순번은 1부터 시작하며, 그룹마다 다시 1부터 시작

---

COALESCE()

COALESCE(value1, value2, ..., valueN)

• 인자들을 왼쪽에서 오른쪽으로 차례대로 검사해서 NULL이 아닌 첫 번째 값을 반환
• 만약 모든 인자가 NULL이면, 결과도 NULL

---

FLOOR()

FLOOR(number)

• 입력한 숫자보다 작거나 같은 정수 중 가장 큰 정수를 반환
  • number : 실수형 숫자 입력값
• 즉, 소수점 아래를 내림 처리

---

첫 번째 시도 (실패)

WITH TRUCK AS (SELECT C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE, D.DURATION_TYPE, D.DISCOUNT_RATE
               FROM CAR_RENTAL_COMPANY_CAR C
                    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
               WHERE C.CAR_TYPE = '트럭'),
RENT_DAYS AS (SELECT HISTORY_ID, CAR_ID, (DATEDIFF(END_DATE, START_DATE)+1) AS DAYS
              FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)

SELECT R.HISTORY_ID, FLOOR((T.DAILY_FEE * (1 - T.DISCOUNT_RATE / 100.0)) * R.DAYS) AS FEE
FROM TRUCK T JOIN RENT_DAYS R ON T.CAR_ID = R.CAR_ID
WHERE SUBSTRING_INDEX(T.DURATION_TYPE, '일', 1) = R.DAYS
ORDER BY FEE DESC, R.HISTORY_ID DESC

---

문제점

• SUBSTRING_INDEX(T.DURATION_TYPE, '일', 1) = R.DAYS 조건
  • DURATION_TYPE에는 "7일 이상", "30일 이상", "90일 이상" 과 같은 문자열이 들어 있는 상황
  • SUBSTRING_INDEX(..., '일', 1)은 문자열 반환이므로 R.DAYS와 비교하려면 정수형 변환이 필요
  • 또한 "이상"이기 때문에 >= 비교를 해야 하고, 최대 할인율을 찾아야 함

---

두 번째 시도 (실패)

WITH TRUCK AS (SELECT C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE, D.DURATION_TYPE, D.DISCOUNT_RATE
               FROM CAR_RENTAL_COMPANY_CAR C
                    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
               WHERE C.CAR_TYPE = '트럭'),
RENT_DAYS AS (SELECT HISTORY_ID, CAR_ID, (DATEDIFF(END_DATE, START_DATE)+1) AS DAYS
              FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY),
DISCOUNT_INFO AS (SELECT CAR_TYPE,
                         CAST(SUBSTRING_INDEX(DURATION_TYPE, '일', 1) AS UNSIGNED) AS MIN_DAYS,
                         DISCOUNT_RATE
                  FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN)

SELECT R.HISTORY_ID, FLOOR((T.DAILY_FEE * (1 - T.DISCOUNT_RATE / 100.0)) * R.DAYS) AS FEE
FROM TRUCK T JOIN RENT_DAYS R ON T.CAR_ID = R.CAR_ID
             JOIN DISCOUNT_INFO I ON T.CAR_TYPE = I.CAR_TYPE
WHERE I.MIN_DAYS <= R.DAYS
ORDER BY FEE DESC, R.HISTORY_ID DESC

---

문제점

• 할인 정책이 중복 적용되는 문제 발생 가능성
  • I.MIN_DAYS <= R.DAYS 조건만 있으면, 한 대여 기간에 대해 여러 할인 조건이 매칭될 수 있기 때문

---

이번 문제 왜이렇게 어렵게 느껴지는거지...? 다음에 한 번 더 풀어보자