프로그래머스 Lv. 4 자동차 대여 기록 별 대여 금액 구하기 (my sql)

🤔문제

CAR_RENTAL_COMPANY_CAR 테이블과
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서
자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서
대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여
대여 기록 ID와 대여 금액 리스트를 출력하는 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 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.

Column	name	Type
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일 미만인 경우 할인정책이 없습니다.

---

😎문제 풀이

문제 해결을 위해서 기본적인 차정보+히스토리(대여기간)을 묶고 그 결과로 대여 금액을 정하는 것으로 순서 결정

with문 ☺️

나누기 위해서 with문을 사용해서 테이블을 만들기로 함

• 🧐생각 순서
  1. 차 정보와 history 정보를 join
  2. 가져온 내용에서 지속기간을 만들어냄
  3. 대여금액과 연결시키기 위해서 date_duration 컬럼을 생성하고 CAR_RENTAL_COMPANY_DISCOUNT_PLAN에서 DURATION_TYPE 같은 이름으로 설정해주기 (case문을 사용해서 뽑아냄)

👩‍💻코드

with duration as (
    select h.HISTORY_ID, car.DAILY_FEE, car.CAR_TYPE,
        datediff(h.end_date,h.start_date) + 1 as PERIOD,
        case 
            when datediff(h.end_date,h.start_date) + 1 >= 90 then '90일 이상'
            when datediff(h.end_date,h.start_date) + 1 >= 30 then '30일 이상'
            when datediff(h.end_date,h.start_date) + 1 >= 7 then '7일 이상'
        else '' end as date_duration
    from CAR_RENTAL_COMPANY_CAR as car
    join CAR_RENTAL_COMPANY_RENTAL_HISTORY as h 
    on car.CAR_ID = h.CAR_ID
    where car_type = '트럭'
    group by h.HISTORY_ID
)

car_type은 트럭이라 했고, history_id 별로 구하라 했으니 where문과 groupby 문을 사용해주면됨

결과내기 😏

다음은 만들어준 duration과 금액 계산을 위해 합쳐주기

• 🧐생각 순서
  1. CAR_RENTAL_COMPANY_DISCOUNT_PLAN 에 없는 것도 있으니까 inner join보단 outer join을 사용해야하고, 기준이 duration이 되어줘야한다. ⇨ left outer join
  2. sqld 공부하면서 알게된 nvl 사용해서 같은 값인걸 가져와서 FEE에 추가해주기
  3. 기본키 설정 해주기 (date_duration이랑 DURATION_TYPE 묶고,
  CAR_TYPE 이랑 묶어주면 별도의 where문 필요없음!)
  

NVL
해당 칼럼의 값이 NULL 값인 경우 특정값으로 출력하고 싶으면 NVL 함수를 사용하고, NULL 값이 아닐 경우 특정값으로 출력하고 싶으면 NVL2 함수를 사용하면 된다.

단 oracle에서만 사용 가능!

내가 지금 풀고 있는 환경은 mysql이기 때문에 COALESCE 함수를 사용해서 해결!

COALESCE(A,'A의값이 없는 경우의 값')
모든 데이터베이스에서 사용 가능

👩‍💻코드

select dr.HISTORY_ID, 
    ROUND(dr.DAILY_FEE * dr.PERIOD * (100 - COALESCE(dp.DISCOUNT_RATE,0)) / 100) AS FEE
from duration as dr
left outer join CAR_RENTAL_COMPANY_DISCOUNT_PLAN as dp
on dr.date_duration = dp.DURATION_TYPE
and dr.CAR_TYPE = dp.CAR_TYPE
order by FEE desc, HISTORY_ID desc

---