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

 

https://school.programmers.co.kr/learn/courses/30/lessons/151141

정답

WITH HIST AS (
SELECT B.HISTORY_ID AS HISTORY_ID
     , A.CAR_ID AS CAR_ID
     , A.CAR_TYPE AS CAR_TYPE
     , A.DAILY_FEE AS DAILY_FEE
     , B.DURATION AS DURATION
     , CASE WHEN B.DURATION < 7 THEN ''
            ELSE CASE WHEN B.DURATION < 30 THEN '7일 이상'
            ELSE CASE WHEN B.DURATION < 90 THEN '30일 이상'
            ELSE '90일 이상'
            END END END AS DURATION_TYPE
  FROM CAR_RENTAL_COMPANY_CAR A
     , (SELECT HISTORY_ID
             , CAR_ID
             , END_DATE - START_DATE + 1 AS duration
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) B
 WHERE 1 = 1
   AND A.CAR_ID = B.CAR_ID
   AND A.CAR_TYPE = '트럭')

SELECT H.HISTORY_ID
     , H.DAILY_FEE * H.DURATION * (100 - NVL(A.DISCOUNT_RATE, 0)) / 100 AS FEE
FROM HIST H
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN A
  ON H.CAR_TYPE = A.CAR_TYPE
  AND H.DURATION_TYPE = A.DURATION_TYPE
ORDER BY FEE DESC, H.HISTORY_ID DESC
;

풀이

WITH 절

대여 기간 구하기

• 대여 종료일 - 대여 시작일 +1

SELECT HISTORY_ID
             , CAR_ID
             , END_DATE - START_DATE + 1 AS duration
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY

대여 기간 CASE문 사용하기

https://lavender1122.tistory.com/264

[Oracle] CASE 표현식

 

SELECT B.HISTORY_ID AS HISTORY_ID
     , A.CAR_ID AS CAR_ID
     , A.CAR_TYPE AS CAR_TYPE
     , A.DAILY_FEE AS DAILY_FEE
     , B.DURATION AS DURATION
     , CASE WHEN B.DURATION < 7 THEN ''
            ELSE CASE WHEN B.DURATION < 30 THEN '7일 이상'
            ELSE CASE WHEN B.DURATION < 90 THEN '30일 이상'
            ELSE '90일 이상'
            END END END AS DURATION_TYPE
  FROM CAR_RENTAL_COMPANY_CAR A
     INNER JOIN (
              SELECT HISTORY_ID
                         , CAR_ID
                         , END_DATE - START_DATE + 1 AS duration
              FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) B
     ON A.CAR_ID = B.CAR_ID
 WHERE A.CAR_TYPE = '트럭'

WITH 절 안에 넣기

WITH HIST AS (
    SELECT B.HISTORY_ID AS HISTORY_ID
         , A.CAR_ID AS CAR_ID
         , A.CAR_TYPE AS CAR_TYPE
         , A.DAILY_FEE AS DAILY_FEE
         , B.DURATION AS DURATION
         , CASE WHEN B.DURATION < 7 THEN ''
                ELSE CASE WHEN B.DURATION < 30 THEN '7일 이상'
                ELSE CASE WHEN B.DURATION < 90 THEN '30일 이상'
                ELSE '90일 이상'
                END END END AS DURATION_TYPE
      FROM CAR_RENTAL_COMPANY_CAR A
         INNER JOIN (
                  SELECT HISTORY_ID
                             , CAR_ID
                             , END_DATE - START_DATE + 1 AS duration
                  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) B
         ON A.CAR_ID = B.CAR_ID
     WHERE A.CAR_TYPE = '트럭'
 )

WITH HIST AS (
SELECT B.HISTORY_ID AS HISTORY_ID
     , A.CAR_ID AS CAR_ID
     , A.CAR_TYPE AS CAR_TYPE
     , A.DAILY_FEE AS DAILY_FEE
     , B.DURATION AS DURATION
     , CASE WHEN B.DURATION < 7 THEN ''
            ELSE CASE WHEN B.DURATION < 30 THEN '7일 이상'
            ELSE CASE WHEN B.DURATION < 90 THEN '30일 이상'
            ELSE '90일 이상'
            END END END AS DURATION_TYPE
  FROM CAR_RENTAL_COMPANY_CAR A
     , (SELECT HISTORY_ID
             , CAR_ID
             , END_DATE - START_DATE + 1 AS duration
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) B
 WHERE 1 = 1
   AND A.CAR_ID = B.CAR_ID
   AND A.CAR_TYPE = '트럭')

SELECT H.HISTORY_ID
     , H.DAILY_FEE * H.DURATION * (100 - NVL(A.DISCOUNT_RATE, 0)) / 100 AS FEE
FROM HIST H
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN A
  ON H.CAR_TYPE = A.CAR_TYPE
  AND H.DURATION_TYPE = A.DURATION_TYPE
ORDER BY FEE DESC, H.HISTORY_ID DESC
;

WITH HIST AS (
SELECT B.HISTORY_ID AS HISTORY_ID
     , A.CAR_ID AS CAR_ID
     , A.CAR_TYPE AS CAR_TYPE
     , A.DAILY_FEE AS DAILY_FEE
     , B.DURATION AS DURATION
     , CASE WHEN B.DURATION < 7 THEN ''
            ELSE CASE WHEN B.DURATION < 30 THEN '7일 이상'
            ELSE CASE WHEN B.DURATION < 90 THEN '30일 이상'
            ELSE '90일 이상'
            END END END AS DURATION_TYPE
  FROM CAR_RENTAL_COMPANY_CAR A
     , (SELECT HISTORY_ID
             , CAR_ID
             , END_DATE - START_DATE + 1 AS duration
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) B
 WHERE 1 = 1
   AND A.CAR_ID = B.CAR_ID
   AND A.CAR_TYPE = '트럭')

SELECT *
FROM HIST H
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN A
  ON H.CAR_TYPE = A.CAR_TYPE
  AND H.DURATION_TYPE = A.DURATION_TYPE
;

• DISCOUNT_RATE 컬럼이 NULL 일 경우 NVL 함수 사용해서 0으로 변환