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

 

 

목차

 

1. 문제

프로그래머스

 

2. 해결 과정

WITH RECURSIVE HISTORY_DURATION AS(
    SELECT *, 
    CASE WHEN(DATEDIFF(END_DATE, START_DATE)+1) >= 7 THEN "7일 이상"
         WHEN(DATEDIFF(END_DATE, START_DATE)+1) >= 30 THEN "30일 이상"
         WHEN(DATEDIFF(END_DATE, START_DATE)+1) >= 90 THEN "90일 이상"
    END
    AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

SELECT H.HISTORY_ID, DATEDIFF(H.END_DATE, H.START_DATE)+1 AS DU, H.DURATION_TYPE, ROUND((DATEDIFF(H.END_DATE, H.START_DATE)+1 * C.DAILY_FEE) * ((1 - IFNULL(P.DISCOUNT_RATE,0) / 100)),0) AS FEE
FROM HISTORY_DURATION H LEFT JOIN CAR_RENTAL_COMPANY_CAR C
ON H.CAR_ID = C.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON H.DURATION_TYPE = P.DURATION_TYPE
WHERE C.CAR_TYPE = "트럭";

• CTE 가상 테이블 설정
• DURATION_TYPE이 잘못되어있는 문제.
  • 30일 이상이면 30일 이상으로 출력되어야하는데, 7일 이상으로 인식되고 있음.
• HISTORY_ID가 중복으로 출력되는 문제.
• 잘못된 FEE 계산.

 

1. DURATION_TYPE이 잘못되어있는 문제

WITH RECURSIVE HISTORY_DURATION AS(
    SELECT *, 
    CASE WHEN(DATEDIFF(END_DATE, START_DATE)+1) BETWEEN 7 AND 29 THEN "7일 이상"
         WHEN(DATEDIFF(END_DATE, START_DATE)+1) BETWEEN 30 AND 89 THEN "30일 이상"
         WHEN(DATEDIFF(END_DATE, START_DATE)+1) >= 90 THEN "90일 이상"
    ELSE 0
    END
    AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

• CTE 테이블의 코드를 위와 같이 변경.

 

2. 자꾸 HISTORY_ID가 여러 개 출력되는 문제

SELECT H.HISTORY_ID, C.CAR_ID, C.DAILY_FEE, C.CAR_TYPE, 
	DATEDIFF(H.END_DATE, H.START_DATE)+1 AS DU, H.DURATION_TYPE, 
	ROUND((1-(IFNULL(P.DISCOUNT_RATE,0)/100)),2) AS RATE 
FROM HISTORY_DURATION H LEFT JOIN CAR_RENTAL_COMPANY_CAR C 
ON H.CAR_ID = C.CAR_ID LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
ON H.DURATION_TYPE = P.DURATION_TYPE
WHERE C.CAR_TYPE = "트럭";

• ON 절에 기간 조건만 있고, 차종을 같이 묶지 않았기 때문에 발생.

 

SELECT H.HISTORY_ID, C.CAR_ID, C.DAILY_FEE, C.CAR_TYPE, 
	DATEDIFF(H.END_DATE, H.START_DATE)+1 AS DU, H.DURATION_TYPE, 
	ROUND((1-(IFNULL(P.DISCOUNT_RATE,0)/100)),2) AS RATE 
FROM HISTORY_DURATION H LEFT JOIN CAR_RENTAL_COMPANY_CAR C 
ON H.CAR_ID = C.CAR_ID LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
ON H.DURATION_TYPE = P.DURATION_TYPE
AND C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE = "트럭";

• AND C.CAR_TYPE = P.CAR_TYPE 을 추가해서 해결.

 

 

3. 조건 확인을 위해 CAR_TYPE=”트럭” 인 경우의 테이블 전체 조회.

SELECT *
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE CAR_TYPE = "트럭";

 

SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = "트럭";

 

 

4. 잘못된 FEE 계산.

SELECT  H.HISTORY_ID, C.CAR_ID, C.DAILY_FEE, C.CAR_TYPE, 
	DATEDIFF(H.END_DATE, H.START_DATE)+1 AS DU, H.DURATION_TYPE, 
	ROUND((1-(IFNULL(P.DISCOUNT_RATE,0)/100)),2) AS RATE, 
	ROUND((DATEDIFF(H.END_DATE, H.START_DATE)+1) * 
	(C.DAILY_FEE * ROUND((1-(IFNULL(P.DISCOUNT_RATE,0)/100)),2)),0) AS FEE
FROM HISTORY_DURATION H LEFT JOIN CAR_RENTAL_COMPANY_CAR C
ON H.CAR_ID = C.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON H.DURATION_TYPE = P.DURATION_TYPE
AND C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE = "트럭"
ORDER BY 2 DESC, 1 DESC;

• 할인율이 적용될 경우에는 DAILY_FEE * (1 - (DISCOUNT_RATE / 100)) 를 먼저 계산한 후에 대여일자를 곱해야함.
• 정수로 나올 수 있도록 ROUND 사용.

 

 

 

3. 최종 코드

WITH HISTORY_DURATION AS(
    SELECT *, 
    CASE WHEN(DATEDIFF(END_DATE, START_DATE)+1) BETWEEN 7 AND 29 THEN "7일 이상"
         WHEN(DATEDIFF(END_DATE, START_DATE)+1) BETWEEN 30 AND 89 THEN "30일 이상"
         WHEN(DATEDIFF(END_DATE, START_DATE)+1) >= 90 THEN "90일 이상"
    ELSE 0
    END
    AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

SELECT  H.HISTORY_ID, ROUND((DATEDIFF(H.END_DATE, H.START_DATE)+1) 
		* (C.DAILY_FEE * ROUND((1-(IFNULL(P.DISCOUNT_RATE,0)/100)),2)),0) AS FEE
FROM HISTORY_DURATION H LEFT JOIN CAR_RENTAL_COMPANY_CAR C
ON H.CAR_ID = C.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON H.DURATION_TYPE = P.DURATION_TYPE
AND C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE = "트럭"
ORDER BY 2 DESC, 1 DESC;