[MYSQL] 자동차 대여 기록별 대여 금액 구하기

문제 분석

 

테이블

1. CAR_RENTAL_COMPANY_CAR : 대여가능한 자동차들의 정보

2. CAR_RENTAL_COMPANY_RENTL_HISTORY : 렌탈 시작/종료 일 등의 정보

3. CAR_RENTAL_COMPANY_DISCOUNT_PLAN : 대여기간에 따른 할인률 정보

 

>>문제

요건1 : 자동차 종류는 트럭

요건2. 대여기록별로 대여금액을 출력

요건3. 대여금액 -> 기록ID 순 내림 차순

 

 

>>예시

 

1단계: 트럭 들의 대여기간 구하기

=> datediff 함수를 사용하여 각 대여 기간의 이름을 LEFT JOIN하기 위해

=> CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블의 기간 이름과 일치시킴

 

결과 : 트럭이고 DURATION이 계산됨

 

2단계: 트럭 들의 대여기간 할인율 테이블 만들기

=> 트럭들의 할인율 값만 추출

 

 

3단계 : DURATION을 기준으로 LEFT JOIN 하기

결과

 

4단계 : 각 열을 조합하여 FEE 만들기

FEE : 대여일수 X 대여비용 X (100-할인율)/100 

대여일수 : DATEDIFF(END_DATE, START_DATE)+1)
대여비용 : DAILY_FEE
100-할인율 : (100-DISCOUNT_RATE)

=> 여기서 문제가 발생

=> DISCOUNT_RATE에는 NULL이 포함되어 있어 연산시 이상한 값이 나옴

 

 

=> 해결책 : NULL이라면 DISCOUNT_RATE을 0으로 처리하자!

=> COALESCE 함수 : 가장 먼저 NULL이 아닌 값을 추출

 

EX)

COALESCE(NULL, 'A', NULL 'B') => 'A'
COALESCE(NULL, NULL 'B') => 'B'

=> COALESCE(DISCOUNT_RATE,0)

=> DISCOUNT_RATE이 NOT NULL이면 DISCOUNT_RATE 반환

=> DISCOUNT_RATE이 NULL이면 0반환

 

FEE : 대여일수 X 대여비용 X (100-할인율)/100 

대여일수 : DATEDIFF(END_DATE, START_DATE)+1)
대여비용 : DAILY_FEE
100-할인율 : (100-DISCOUNT_RATE) => (100-COALESCE(DISCOUNT_RATE,0)) [NULL이면 0처리]

 

 

전체코드