SQL/ORACLE.

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

devz0 2023. 4. 16. 00:00
728x90
반응형

문제

 

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

 

Pseudo Code

1. CAR_RENTAL_COMPANY_CAR  테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 CAR_ID 컬럼값으로 이너 조인을 걸어준다.

2. 이너조인시 CAR_RENTAL_COMPANY_RENTAL_HISTORY테이블에서 대여 기간을 구하기 위해 END_DATE - START_DATE 에서 1을 더해준 컬럼 추출 같이 해준다 (ex. 8월5일-8월 5일 도 대여기간 1일)

3. 트럭만 필터링 한다.

4. 대여 기록 별로 데이터를 추출해야 하기 때문에 HISTORY_ID로 그룹핑한다.

5.  대여 금액 조건을 보면 7일 이상은 5%, 30일 이상은 8%, 90일 이상은 15%, 그이외에는 할인율이 없기 때문에 조건에 맞게 집계함수 작성

6. FEE DESC, HISTORY_ID DESC

 

내 코드

 

SELECT 
    C2.HISTORY_ID,
    SUM(CASE
        WHEN DATE_RANGE BETWEEN 7 AND 30 THEN ROUND(C.DAILY_FEE * 0.95 *C2.DATE_RANGE) 
        WHEN DATE_RANGE BETWEEN 31 AND 90 THEN ROUND(C.DAILY_FEE * 0.92 *C2.DATE_RANGE)
        WHEN DATE_RANGE >= 90 THEN ROUND(C.DAILY_FEE * 0.85 *C2.DATE_RANGE)
        ELSE ROUND(C2.DATE_RANGE * C.DAILY_FEE)
    END) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
INNER JOIN (
    SELECT 
        CAR_ID,
        HISTORY_ID, 
        END_DATE-START_DATE+1 AS DATE_RANGE 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) C2
ON C.CAR_ID = C2.CAR_ID
WHERE CAR_TYPE = '트럭'
GROUP BY C2.HISTORY_ID
ORDER BY FEE DESC, HISTORY_ID DESC;

 

 

테이블 3개를 조인해서 풀려고 하니 헷갈려서 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 필요한 데이터를 보니 직관적인 상수들이라 (DURATION_TYPE, DISCOUNT_RATE) 두 테이블만 조인한 후 필요한 조건절이나 할인율은 계산된 값을 직접 작성해 코드를 완성했다. 

 

그렇기 때문에 할인율 같은 경우에는  CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 또 따로 출력해 트럭 데이터들의 값을 확인해야해 좀 하드코딩이지 않았나 싶다.

728x90
반응형