Post

특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

JOIN

Posted
By Park Juyoung
views 9 min read
특정 기간동안 대여 가능한 자동차들의 대여비용 구하기


문제 설명

다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블, 그리고 자동차 종류별 대여 기간 종류별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블입니다.


CAR_RENTAL_COMPANY_CAR 테이블

Column nameTypeNullableDescription
CAR_IDINTEGERFALSE자동차 ID
CAR_TYPEVARCHAR(255)FALSE자동차 종류
DAILY_FEEINTEGERFALSE일일 대여 요금(원)
OPTIONSVARCHAR(255)FALSE자동차 옵션 리스트

자동차 종류는 ‘세단’, ‘SUV’, ‘승합차’, ‘트럭’, ‘리무진’ 이 있습니다. 자동차 옵션 리스트는 콤마(‘,’)로 구분된 키워드 리스트(예: ‘열선시트, 스마트키, 주차감지센서’)로 되어있으며, 키워드 종류는 ‘주차감지센서’, ‘스마트키’, ‘네비게이션’, ‘통풍시트’, ‘열선시트’, ‘후방카메라’, ‘가죽시트’ 가 있습니다.


CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블

Column nameTypeNullableDescription
PLAN_IDINTEGERFALSE요금 할인 정책 ID
CAR_TYPEVARCHAR(255)FALSE자동차 종류
DURATION_TYPEVARCHAR(255)FALSE대여 기간 종류
DISCOUNT_RATEINTEGERFALSE할인율(%)


CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블

Column nameTypeNullableDescription
HISTORY_IDINTEGERFALSE자동차 대여 기록 ID
CAR_IDINTEGERFALSE자동차 ID
START_DATEDATEFALSE대여 시작일
END_DATEDATEFALSE대여 종료일

할인율이 적용되는 대여 기간 종류로는 ‘7일 이상’ (대여 기간이 7일 이상 30일 미만인 경우), ‘30일 이상’ (대여 기간이 30일 이상 90일 미만인 경우), ‘90일 이상’ (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.



문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 ‘세단’ 또는 ‘SUV’ 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.



풀이 과정
  1. 조건 확인
    • 자동차 종류는 세단 또는(OR) SUV
    • 2022년 11월 1일부터 11월 30일까지 대여 가능해야 함
    • 30일간 대여 금액이 50만 원 이상, 200만 원 미만이어야 함
  2. 대여 가능한 자동차 찾기
    • 먼저 이미 대여 중인 자동차를 제외하기 위해 해당 기간(11월 1일~30일)과 겹치는 기록이 없는 데이터 찾기
    • 이를 위해 LEFT JOIN 사용하고, 대여 기록이 없는 경우를 필터링 (B.CAR_ID IS NULL)
    • LEFT JOIN 선택 이유: 대여 가능한 자동차를 포함하면서 대여 기록이 없는 경우를 찾아야 하기 때문에 기준 테이블의 모든 데이터를 유지하기 위해
  3. 자동차 종류 필터링
    • C.CAR_TYPE IN (‘세단’, ‘SUV’) 조건으로 필요한 데이터를 걸러내자
  4. 30일 대여 금액 계산하기
    • 대여 금액은 DAILY_FEE에 30일을 곱한 뒤, (1 - 할인율/100)을 곱함
    • 할인율은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 가져오고, 30일 이상 대여 조건에 맞는 할인율(DURATION_TYPE = ‘30일 이상’)만 선택
    • 마지막으로 소수점을 제거하기 위해 TRUNC 사용하여 정수화
    • LEFT JOIN 선택 이유: 모든 차량이 할인 정책을 가지는 것은 아니므로, 할인 정책이 없는 차량도 결과에 포함되기 위해
  5. 대여 금액 조건 만족 여부 확인
    • 계산된 금액 중에서 50만 원 이상, 200만 원 미만인 경우만 선택
    • A.FEE BETWEEN 500000 AND 1999999 조건 추가
  6. 결과 정렬
    • 정렬 기준에 따라 ORDER BY로 결과 정렬
      • TOTAL_PRICE를 기준으로 내림차순
      • 대여 금액이 같을 경우 CAR_TYPE 기준으로 오름차순
      • 자동차 종류까지 같으면 CAR_ID 기준으로 내림차순
  7. 최종 결과 출력
    • 최종적으로 CAR_ID, CAR_TYPE, FEE만 출력
  • 교훈
    • 문제의 테이블 순서대로 JOIN 할 필요 없다. 효율적인 흐름… 띵킹 플리즈 주영..
    • 하나하나, 차근차근 조건 만족시키자..



정답

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

SELECT 
    A.CAR_ID,  
    A.CAR_TYPE,
    A.FEE
FROM (
    SELECT 
        C.CAR_ID,
        C.CAR_TYPE,
        TRUNC(C.DAILY_FEE * (1 - (D.DISCOUNT_RATE / 100)) * 30) AS FEE
    FROM 
        CAR_RENTAL_COMPANY_CAR C
    LEFT JOIN 
        CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
    ON 
        C.CAR_TYPE = D.CAR_TYPE
    WHERE 
        C.CAR_TYPE IN ('세단', 'SUV')
        AND D.DURATION_TYPE = '30일 이상'
) A
LEFT JOIN (
    SELECT 
        CAR_ID
    FROM 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE 
        START_DATE <= TO_DATE('20221130', 'YYYYMMDD') -- 시작일이 20221130보다 이전이거나 같아야 함
        AND END_DATE >= TO_DATE('20221101', 'YYYYMMDD') -- 끝나는 일이 20221101보다 크거나 같아야 함
) B
ON 
    A.CAR_ID = B.CAR_ID
WHERE 
    B.CAR_ID IS NULL 
    AND A.FEE BETWEEN 500000 AND 1999999 
ORDER BY 
    A.FEE DESC,
    A.CAR_TYPE ASC, 
    A.CAR_ID DESC;


Tip

  1. 주어진 날짜 범위(2022-11-01 ~ 2022-11-30)와 겹치는 렌탈 기록이 없는 차량을 선택
    • WHERE START_DATE <= TO_DATE(‘희망하는렌탈마지막일자’, ‘YYYYMMDD’)
      AND END_DATE >= TO_DATE(‘희망하는렌탈시작일자’, ‘YYYYMMDD’)
    • CAR_ID IS NULL : CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에 해당 CAR_ID가 존재하지 않으면, 차량이 대여 가능 상태인 것
  2. 할인 요금 계산법
    • 할인된 요금 = 원래 요금 * ( 1 - 할인율(%) / 100 )

SQL Dialect Used: Oracle



개념 확인

  • BETWEEN 연산자
    • 해당 범위 내의 데이터를 필터링
    • WHERE 컬럼명 BETWEEN 500000 AND 1999999
  • LEFT JOIN
    • 기준 테이블의 모든 행을 유지하며, 일치하지 않는 경우에도 결과에 포함. 일치하지 않으면 NULL 반환
  • TO_DATE 함수
    • 문자열을 날짜 데이터로 변환 (YYYY-MM-DD HH24:MI:SS, YYYY-MM-DD, YYYY/MM/DD 등)
  • TRUNC 함수
    • 소수점 이하 값을 제거하거나 원하는 자릿수로 반올림하지 않고 값을 절삭



특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

SQL Coding Test, Programmers
SQL
This post is licensed under Park Juyoung by the author.

Trending Tags

SQL