[SQL][프로그래머스]특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 - SUBQUERY + ROUND + DATEDIFF + INNER JOIN + NOT IN + WITH MYSQL ORACLE

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

프로그래머스

코드설명

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

 

문제에서 유의할점은, 11월1일부터 12월 1일까지 대여가 가능한 자동차를 가져오는 것이 가장 중요했습니다.

조건문을 시작일은 11월 1일 이상, 종료일은 12월 1일 이하로 하면, 겹치는 부분이 있는 레코드 및 CAR_ID는 포함하지 못하므로 NOT IN을 통해 필터링하여 가져옵니다.

SELECT DISTINCT(CRCRH.CAR_ID)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
WHERE CRCRH.CAR_ID NOT IN ( SELECT DISTINCT(CRCRH.CAR_ID) FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CRCRH WHERE ( DATEDIFF(CRCRH.START_DATE, '2022-12-01') < 0 AND DATEDIFF(CRCRH.END_DATE, '2022-11-01') > 0

코드

SELECT CRCC.CAR_ID, CRCC.CAR_TYPE, ROUND(CRCC.DAILY_FEE * 30 * (1 - (CRCDP.DISCOUNT_RATE / 100 )), 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS CRCC
INNER JOIN ( SELECT DISTINCT(CRCRH.CAR_ID)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
WHERE CRCRH.CAR_ID NOT IN ( SELECT DISTINCT(CRCRH.CAR_ID) FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CRCRH WHERE ( DATEDIFF(CRCRH.START_DATE, '2022-12-01') < 0 AND DATEDIFF(CRCRH.END_DATE, '2022-11-01') > 0 ) ) )AS CRCRH
ON CRCC.CAR_TYPE IN ('SUV', '세단') AND CRCC.CAR_ID = CRCRH.CAR_ID
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS CRCDP
ON CRCC.CAR_TYPE = CRCDP.CAR_TYPE AND DURATION_TYPE = '30일 이상'
WHERE ROUND(CRCC.DAILY_FEE * 30 * (1 - (CRCDP.DISCOUNT_RATE / 100 )), 0) >= 500000 AND ROUND(CRCC.DAILY_FEE * 30 * (1 - (CRCDP.DISCOUNT_RATE / 100 )), 0) < 2000000

 

ORACLE

WITH DISCOUNT_RATE AS (
    SELECT CAR_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE DURATION_TYPE = '30일 이상'
)

SELECT CRCC.CAR_ID, CRCC.CAR_TYPE, ROUND(CRCC.DAILY_FEE * 30 * (1 - D30.DISCOUNT_RATE/100), 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR CRCC
INNER JOIN ( SELECT DISTINCT(CRCRH.CAR_ID)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
WHERE CRCRH.CAR_ID NOT IN ( SELECT DISTINCT(CAR_ID) FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                            WHERE (TO_CHAR(END_dATE, 'YYYY-MM-DD') >= '2022-11-01' AND TO_CHAR(START_DATE, 'YYYY-MM-DD') <= '2022-11-30') ) ) CRCRH
ON (CRCC.CAR_TYPE = 'SUV' OR CRCC.CAR_TYPE='세단') AND CRCC.CAR_ID = CRCRH.CAR_ID
INNER JOIN DISCOUNT_RATE D30 ON D30.CAR_TYPE = CRCC.CAR_TYPE
WHERE ROUND(CRCC.DAILY_FEE * 30 * (1 - D30.DISCOUNT_RATE/100), 0) >= 500000 AND ROUND(CRCC.DAILY_FEE * 30 * (1 - D30.DISCOUNT_RATE/100), 0) <= 2000000
ORDER BY FEE DESC, CRCC.CAR_TYPE ASC, CRCC.CAR_ID DESC

 

이렇게 구하면 서브쿼리가 매번 실행되는게 아닌 테이블 자체에 데이터가 합쳐지므로 연산이 줄어듭니다.

SELECT CRCC.CAR_ID, CRCC.CAR_TYPE, ROUND(CRCC.DAILY_FEE * 30 * (1 - D30.DISCOUNT_RATE/100), 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR CRCC
INNER JOIN ( SELECT DISTINCT(CRCRH.CAR_ID)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY CRCRH
WHERE CRCRH.CAR_ID NOT IN ( SELECT DISTINCT(CAR_ID) FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                            WHERE (TO_CHAR(END_dATE, 'YYYY-MM-DD') >= '2022-11-01' AND TO_CHAR(START_DATE, 'YYYY-MM-DD') <= '2022-11-30') ) ) CRCRH
ON (CRCC.CAR_TYPE = 'SUV' OR CRCC.CAR_TYPE='세단') AND CRCC.CAR_ID = CRCRH.CAR_ID
INNER JOIN (
    SELECT CAR_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
    WHERE DURATION_TYPE = '30일 이상'
) D30 ON D30.CAR_TYPE = CRCC.CAR_TYPE
WHERE ROUND(CRCC.DAILY_FEE * 30 * (1 - D30.DISCOUNT_RATE/100), 0) >= 500000 AND ROUND(CRCC.DAILY_FEE * 30 * (1 - D30.DISCOUNT_RATE/100), 0) <= 2000000
ORDER BY FEE DESC, CRCC.CAR_TYPE ASC, CRCC.CAR_ID DESC