프로그래머스 | 자동차 대여 기록 별 대여 금액 구하기 (MySQL)

오늘도 오랑이는 문제를 풉니다. 

한 번에 풀 수 있을 것처럼 생겨서 한 번에 풀리지 않아서 머리 싸맨 문제 중 하나입니다. 

역시 오랑이는 아직 갈 길이 멉니다. 

 

문제 설명

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

 

문제

1. 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서

2. 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력

3. 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬

 

테이블 예시

 

해결 과정

대여금액은 일일대여료*대여일수*할인율 로 계산되며, 대여일수에 따라 할인율이 달라지는 구조입니다. 

즉 아래와 같이 각 항목을 쪼개서 생각해볼 수 있습니다. 

대여일수 DATEDIFF(END_DATE,START_DATE)+1 AS DAYS
대여금액 ROUND(DAILY_FEE*DAYS*((100-DISCOUNT_RATE)*0.01)) AS FEE

 

이때 CAR_RENTAL_COMPANY_RENTAL_HISTORY  테이블에서 구한 DAYS 와  CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블의 할인율이 한 번에 연결되면 참 좋겠지만 컴퓨터는 그런 걸 할 줄 모르기 때문에 오랑이가 하나하나 연결해줘야 합니다. 

문제가 복잡해질수록 JOIN으로 3개 이상의 테이블을 한 번에 연결해서 푸는 것은 어려워지는 것 같습니다. 

 

우리가 원하는 결과는 7일 미만 할인율 0, 7일 이상 30일 미만 할인율 5, 30일 이상 90일 이하 할인율 7, 90일 이상 할인율 10 입니다. 

기존의 테이블만으로는 연결이 어렵기 때문에 WITH 절을 사용해서 새로운 테이블을 만들어줍니다.

 

이때 필요한 칼럼은 HISTORY_ID, CAR_TYPE, DAILY_FEE, DAYS, DURATION_TYPE 이며 결과물은 아래와 같습니다. 

     → 처음 코드 작성 시 CAR_TYPE 를 넣지 않고 DURTAION_TYPE 로만 할인 테이블과 연결했더니 할인율에 모두 -1이 된 이상한 결과물이 나왔습니다. 테이블 JOIN 시 어떤 값에 대해서 연결해줄지 항상 제대로 생각해야 하는데 매번 한두개씩 놓칩니다. 

SELECT HISTORY_ID, CAR_TYPE, DAILY_FEE, 
       DATEDIFF(END_DATE,START_DATE)+1 AS DAYS,
       CASE WHEN DATEDIFF(END_DATE,START_DATE)+1 >=90 THEN '90일 이상'
             WHEN DATEDIFF(END_DATE,START_DATE)+1 >=30 THEN '30일 이상'
             WHEN DATEDIFF(END_DATE,START_DATE)+1 >=7 THEN '7일 이상'
             ELSE NULL END DURATION_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID=C.CAR_ID
WHERE CAR_TYPE='트럭'

WITH 절에 들어갈 쿼리에 대한 1차 결과물

 

이제 방금 짠 쿼리를 WITH 절에 넣어줍니다. (일단 생략)

 

최종 결과물을 출력해줄 쿼리를 작생해보기 전에 원하는 값들이 제대로 나오는지 확인해보는 중간 과정을 거쳐갑니다. (사실 최종 결과물을 출력했는데 틀려서 다시 돌아왔습니다.) 

 

아래 두 쿼리 중 뭐가 맞을까요? 

SELECT HISTORY_ID, DAILY_FEE, DAYS, DISCOUNT_RATE
FROM A JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D 
    ON A.CAR_TYPE = D.CAR_TYPE AND A.DURATION_TYPE = D.DURATION_TYPE
ORDER BY 2 DESC, 1 DESC

SELECT HISTORY_ID, DAILY_FEE, DAYS, DISCOUNT_RATE
FROM A LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D 
    ON A.CAR_TYPE = D.CAR_TYPE AND A.DURATION_TYPE = D.DURATION_TYPE
ORDER BY 2 DESC, 1 DESC

 

LEFT JOIN 을 사용한 두번째 쿼리가 맞습니다. (오랑이는 1번으로 작성해서 틀렸고요 ^^)

INNER JOIN 을 할 경우 열심히 NULL 로 분류해둔 7일보다 적게 대여한 차량에 대한 할인율이 출력되지 않습니다!

열심히 쿼리 짜면 뭐합니까 다 된 밥에 스스로 재를 뿌리는데.. 

 

이제 앞에서 계산했던 할인율을 적용해서 전체 쿼리를 완성시킵니다. 

NULL값에 대해 0으로 처리하는 것도 잊지 맙시다.

 

결과

WITH A AS (
    SELECT HISTORY_ID, CAR_TYPE, DAILY_FEE, 
       DATEDIFF(END_DATE,START_DATE)+1 AS DAYS,
       CASE WHEN DATEDIFF(END_DATE,START_DATE)+1 >=90 THEN '90일 이상'
             WHEN DATEDIFF(END_DATE,START_DATE)+1 >=30 THEN '30일 이상'
             WHEN DATEDIFF(END_DATE,START_DATE)+1 >=7 THEN '7일 이상'
             ELSE NULL END DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID=C.CAR_ID
    WHERE CAR_TYPE='트럭'
)

SELECT HISTORY_ID, ROUND(DAILY_FEE*DAYS*((100-IFNULL(DISCOUNT_RATE,0))*0.01)) AS FEE
FROM A LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D 
    ON A.CAR_TYPE = D.CAR_TYPE AND A.DURATION_TYPE = D.DURATION_TYPE
ORDER BY 2 DESC, 1 DESC
;

 

 

프로그래머스

 

 

 

오랑우탄이 영어를 하고 오랑이가 쿼리를 한 번에 짜는 기적을 이루는 그날까지~