[SQL][프로그래머스]자동차 대여 기록 별 대여 금액 구하기 - SUBQUERY + ROUND + DATEDIFF + INNER JOIN + WITH MYSQL ORACLE

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

프로그래머스

코드설명

SUBQUERY + ROUND + DATEDIFF + INNER JOIN 를 활용합니다.

 

문제에서 처음에는 INNER JOIN을 모든 테이블을 묶어서 진행하려고 했지만, SQL에서는 IF문 사용이 불가하기에 2개의 테이블을 JOIN하고, CASE WHEN 을 SELECT문에 두고, 해당 CASE WHEN 에 서브쿼리를 두어 처리했습니다.

 

유의해야할 사항은 DATEDIFF(END_DATE, START_DATE) + 1 을 해야만 같은날에 빌린것도 1일처리가 됩니다.

2번째는, CASE WHEN 이 올바르게 분기되기 위해서 >= 90, >= 30  >= 7 이런 순으로 작성해서 가장 큰 날짜 차이에 먼저 들어가도록 처리합니다.

코드

처음에 푼 정답코드입니다.

-- 코드를 입력하세요
SELECT CRCRH.HISTORY_ID,
    CASE 
        WHEN (DATEDIFF(CRCRH.END_DATE, CRCRH.START_DATE) + 1) >= 90 THEN ROUND(CRCC.DAILY_FEE * (DATEDIFF(CRCRH.END_DATE, CRCRH.START_DATE) + 1) * ((SELECT (1 - DISCOUNT_RATE/100) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '90일 이상')), 0)
        WHEN (DATEDIFF(CRCRH.END_DATE, CRCRH.START_DATE) + 1) >= 30 THEN ROUND(CRCC.DAILY_FEE * (DATEDIFF(CRCRH.END_DATE, CRCRH.START_DATE) + 1) * ((SELECT (1 - DISCOUNT_RATE/100) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '30일 이상')), 0)
        WHEN (DATEDIFF(CRCRH.END_DATE, CRCRH.START_DATE) + 1) >= 7 THEN ROUND(CRCC.DAILY_FEE * (DATEDIFF(CRCRH.END_DATE, CRCRH.START_DATE) + 1) * ((SELECT (1 - DISCOUNT_RATE/100) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '7일 이상')), 0)
        ELSE CRCC.DAILY_FEE * (DATEDIFF(CRCRH.END_DATE, CRCRH.START_DATE) + 1)
    END AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CRCRH
INNER JOIN CAR_RENTAL_COMPANY_CAR AS CRCC
ON CRCRH.CAR_ID = CRCC.CAR_ID AND CRCC.CAR_TYPE = '트럭'
ORDER BY FEE DESC, CRCRH.HISTORY_ID DESC

 

ORACLE

SELECT CRCRH.HISTORY_ID,
    CASE 
        WHEN (CRCRH.END_DATE - CRCRH.START_DATE + 1) >= 90 THEN ROUND((CRCRH.END_DATE - CRCRH.START_DATE + 1) * CRCC.DAILY_FEE * (SELECT (1 - DISCOUNT_RATE/100) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE REGEXP_LIKE(DURATION_TYPE, '^90일 이상') AND CAR_TYPE = '트럭'), 0)
        WHEN (CRCRH.END_DATE - CRCRH.START_DATE + 1) >= 30 THEN ROUND((CRCRH.END_DATE - CRCRH.START_DATE + 1) * CRCC.DAILY_FEE * (SELECT (1 - DISCOUNT_RATE/100) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE REGEXP_LIKE(DURATION_TYPE, '^30일 이상') AND CAR_TYPE = '트럭'), 0)
        WHEN (CRCRH.END_DATE - CRCRH.START_DATE + 1) >= 7 THEN ROUND((CRCRH.END_DATE - CRCRH.START_DATE + 1) * CRCC.DAILY_FEE * (SELECT (1 - DISCOUNT_RATE/100) FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE REGEXP_LIKE(DURATION_TYPE, '^7일 이상') AND CAR_TYPE = '트럭'), 0)
        ELSE (CRCRH.END_DATE - CRCRH.START_DATE + 1) * CRCC.DAILY_FEE 
    END AS FEE
FROM CAR_RENTAL_COMPANY_CAR CRCC
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
ON CRCC.CAR_ID = CRCRH.CAR_ID AND CRCC.CAR_TYPE='트럭'
ORDER BY FEE DESC, CRCRH.HISTORY_ID DESC

 

 

매번 SUBQUERY를 사용하지 않는경우입니다.

즉, INNER JOIN 혹은 다른 JOIN을 통해서 필드값을 추가해주어서 SUBQUERY를 사용하지 않습니다.

WITH DISCOUNT_RATES AS (
    SELECT DURATION_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭'
)
SELECT 
    CRCRH.HISTORY_ID,
    ROUND(
        CRCC.DAILY_FEE * (CRCRH.END_DATE - CRCRH.START_DATE + 1) *
        COALESCE(
            CASE 
                WHEN (CRCRH.END_DATE - CRCRH.START_DATE + 1) >= 90 THEN (1 - DR90.DISCOUNT_RATE/100)
                WHEN (CRCRH.END_DATE - CRCRH.START_DATE + 1) >= 30 THEN (1 - DR30.DISCOUNT_RATE/100)
                WHEN (CRCRH.END_DATE - CRCRH.START_DATE + 1) >= 7 THEN (1 - DR7.DISCOUNT_RATE/100)
                ELSE 1
            END,
            1
        ),
        0
    ) AS FEE,
    DR90.*, DR30.*, DR7.*
FROM 
    CAR_RENTAL_COMPANY_CAR CRCC
    INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH ON CRCC.CAR_ID = CRCRH.CAR_ID
    INNER JOIN DISCOUNT_RATES DR90 ON DR90.DURATION_TYPE = '90일 이상'
    INNER JOIN DISCOUNT_RATES DR30 ON DR30.DURATION_TYPE = '30일 이상'
    INNER JOIN DISCOUNT_RATES DR7 ON DR7.DURATION_TYPE = '7일 이상'
WHERE 
    CRCC.CAR_TYPE = '트럭'
ORDER BY 
    FEE DESC, CRCRH.HISTORY_ID DESC

 

WITH절 사용대신 직접 INNER JOIN

SELECT CRCRH.HISTORY_ID AS HISTORY_ID,
    CASE 
        WHEN (CRCRH.END_DATE - CRCRH.START_DATE + 1) >= 90 THEN (1 - CRCDP90.DISCOUNT_RATE/100) * (CRCRH.END_DATE - CRCRH.START_DATE + 1) * CRCC.DAILY_FEE
        WHEN (CRCRH.END_DATE - CRCRH.START_DATE + 1) >= 30 THEN (1 - CRCDP30.DISCOUNT_RATE/100) * (CRCRH.END_DATE - CRCRH.START_DATE + 1) * CRCC.DAILY_FEE
        WHEN (CRCRH.END_DATE - CRCRH.START_DATE + 1) >= 7 THEN (1 - CRCDP7.DISCOUNT_RATE/100) * (CRCRH.END_DATE - CRCRH.START_DATE + 1) * CRCC.DAILY_FEE   
        ELSE (CRCRH.END_DATE - CRCRH.START_DATE + 1) * CRCC.DAILY_FEE
    END AS FEE
FROM CAR_RENTAL_COMPANY_CAR CRCC
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
ON CRCC.CAR_ID = CRCRH.CAR_ID
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN CRCDP90
ON CRCDP90.CAR_TYPE = '트럭' AND CRCDP90.DURATION_TYPE = '90일 이상'
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN CRCDP30
ON CRCDP30.CAR_TYPE = '트럭' AND CRCDP30.DURATION_TYPE = '30일 이상'
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN CRCDP7
ON CRCDP7.CAR_TYPE = '트럭' AND CRCDP7.DURATION_TYPE = '7일 이상'
WHERE CRCC.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC