SQL 예제 정리('자동차 대여 기록 별 대여 금액 구하기')(24.05.13)

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

프로그래머스

 

이 예제는 총 3개의 테이블을 JOIN해야합니다.

차에 대한 정보를 나타내는 CAR_RENTAL_COMPANY_CAR 테이블,

렌트 기록 정보를 나타내는 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블,

할인율 정보를 나타내는 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블

이렇게 3개 입니다.

 

예제의 요구사항은 다음과 같습니다.

1. 자동차 종류가 '트럭'인 자동차의
2. 1) 자동차의 대여 기록과 2) 대여 기록별 대여 금액

이 예제에서는

3개 테이블을 JOIN시키고, WHERE절에 조건을 적어주고, GROUP화하는 것은 쉽습니다.

하지만 예제의 요구사항 2-2를 계산하기 위해서 SELECT 절에서 서브쿼리를 사용해야하는데

이 부분이 조금 까다롭습니다.

쿼리 먼저 보겠습니다.

SELECT RH.HISTORY_ID,
       CASE WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 < 7 THEN ROUND(RC.DAILY_FEE * (DATEDIFF(RH.END_DATE, RH.START_DATE) + 1))
            WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 BETWEEN 7 AND 29 THEN ROUND(RC.DAILY_FEE * (DATEDIFF(RH.END_DATE, RH.START_DATE) + 1) * 
                     (1-(SELECT DISCOUNT_RATE
                         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                         WHERE DURATION_TYPE='7일 이상' AND CAR_TYPE='트럭')/100))
            WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 BETWEEN 30 AND 89 THEN ROUND(RC.DAILY_FEE * (DATEDIFF(RH.END_DATE, RH.START_DATE) + 1) * 
                     (1-(SELECT DISCOUNT_RATE
                         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                         WHERE DURATION_TYPE='30일 이상' AND CAR_TYPE='트럭')/100))
            ELSE ROUND(RC.DAILY_FEE * (DATEDIFF(RH.END_DATE, RH.START_DATE) + 1) * 
                     (1-(SELECT DISCOUNT_RATE
                         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                         WHERE DURATION_TYPE='90일 이상' AND CAR_TYPE='트럭')/100))
            END FEE
FROM CAR_RENTAL_COMPANY_CAR RC JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY RH ON RC.CAR_ID=RH.CAR_ID JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP ON RC.CAR_TYPE=DP.CAR_TYPE
WHERE RC.CAR_TYPE='트럭'
GROUP BY RH.HISTORY_ID
ORDER BY FEE DESC, RH.HISTORY_ID DESC;

확실히 SELECT 절에 쓰여진 쿼리가 많죠?

한 줄씩 살펴보겠습니다.

CASE WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 < 7 THEN ROUND(RC.DAILY_FEE * (DATEDIFF(RH.END_DATE, RH.START_DATE) + 1))

대여 기록(HISTORY_ID)별 대여 금액을 구하려면

총 대여 일자 * 일일 대여 요금 * (1-할인율/100)

대로 계산해야합니다.

총 대여 일자는 END_DATE에서 START_DATE를 빼면 되는데요,

할인율이 DURATION_TYPE마다 다르기 때문에 이 부분에서 서브쿼리를 활용해야 합니다.

바로 위 쿼리는 총 대여 기간이 7일 미만인 경우에 해당하는데

7일 미만의 경우 할인이 들어가지 않기 때문에 단순히 위와 같이 쿼리를 작성하면 됩니다.

그럼 7일 이상부터는 어떻게 쿼리를 적어야 하는지 살펴보겠습니다.

WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 BETWEEN 7 AND 29 THEN ROUND(RC.DAILY_FEE * (DATEDIFF(RH.END_DATE, RH.START_DATE) + 1) * 
                     (1-(SELECT DISCOUNT_RATE
                         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                         WHERE DURATION_TYPE='7일 이상' AND CAR_TYPE='트럭')/100))
WHEN DATEDIFF(RH.END_DATE, RH.START_DATE)+1 BETWEEN 30 AND 89 THEN ROUND(RC.DAILY_FEE * (DATEDIFF(RH.END_DATE, RH.START_DATE) + 1) * 
                     (1-(SELECT DISCOUNT_RATE
                         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                         WHERE DURATION_TYPE='30일 이상' AND CAR_TYPE='트럭')/100))
ELSE ROUND(RC.DAILY_FEE * (DATEDIFF(RH.END_DATE, RH.START_DATE) + 1) * 
                     (1-(SELECT DISCOUNT_RATE
                         FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                         WHERE DURATION_TYPE='90일 이상' AND CAR_TYPE='트럭')/100))
END FEE

설명 편의상 4개의 단락이라고 표현하겠습니다.

첫 번째 단락은 대여기간이 7일 이상 30일 미만인 경우입니다.

앞서 살펴본 대여 금액 산식을 적용하지만 

DURATION_TYPE이 '7일 이상'인 DISCOUNT_RATE을 넣어줘야하기때문에

DISCOUNT_RATE 자리에 서브쿼리를 넣어줬습니다.

SELECT DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE DURATION_TYPE='7일 이상' AND CAR_TYPE='트럭'

서브쿼리는 이런식으로 작성됩니다.

 

두 번째, 세 번째 단락도 마찬가지입니다.

두 번째 단락은 총 대여기간이 30일 이상 90일 미만인 경우로

서브쿼리의 WHERE절에 DURATION_TYPE='30일 이상'을,

세 번재 단락은 총 대여기간이 90일 이상인 경우로

서브쿼리의 WHERE절에 DURATION_TYPE='90일 이상'을

적어줍니다.

 

이렇게 쿼리를 작성하면 HISTORY_ID별 대여금액을 확인할 수 있습니다.

이처럼 이번 예제는 서브쿼리를 활용하여

원하는 조건에 해당하는 값만을 추출하고,

그 값에 해당하는 할인율로 대여금액을 계산하는 유형의 예제였습니다.