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) 3개 테이블을 조인한다
2) 조건절을 작성한다
2-1) car_type이 '세단' or 'SUV'
2-2) start_date와 end_date가 하나라도 2022년 11월인 경우가 아닌 경우
2-3) duration_type이 '30일 이상'인 경우
* 2-4) 대여 금액(fee) 칼럼을 만들고 이것이 50만원 이상, 200만원 미만인 경우 --> 3단계 이후에 진행
3) 대여 금액 칼럼을 만든다. (daily_fee * datediff(end_date, start_date)) + (discount_rate * daily_fee * datediff(end_date, start_date)) as 'fee'
3-1. 1~3까지 단계는 서브쿼리로. 상위 쿼리에서 2-4에서 예정했던 조건을 작성한다
초안
SELECT
*
FROM (
SELECT
car_id
, car_type
, round((1-(discount_rate/100)) * 30 * daily_fee,0) as 'fee'
FROM (
SELECT
c.car_id
, c.car_type
, c.daily_fee
, d.discount_rate
FROM
CAR_RENTAL_COMPANY_CAR c
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.car_id = h.car_id
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON c.car_type = d.car_type
WHERE
(c.car_type in ('세단', 'SUV'))
and not((year(h.start_date)=2022 and month(h.start_date)=11)
or (year(h.end_date)=2022 and month(h.end_date)=11))
and (d.duration_type = '30일 이상')
) aa
) bb
WHERE
(fee >= 500000)
and (fee < 2000000)
ORDER BY
fee desc
, car_type
, car_id desc
;다시 생각해보니 2-2, 3번 내용이 잘못되었다
먼저 3번은 어차피 30일동안 대여하겠다고 문제에 명시되어 있으므로 datediff() 함수를 사용할 필요가 없이 일수는 30으로 적용하면 된다
그리고 2-2번. 내가 생각했던 조건은 11월이 되기 전에 대여를 시작해서 11월 이후에 끝나는 경우를 제외하지 못한다
11월에 해당 차량이 대여 중일 수 있는 경우의 수는 (1) 11월에 대여를 시작, (2) 11월에 대여가 끝, (3) 11월 전에 대여를 시작해 11월 이후에 대여가 종료됨, 이렇게 3가지가 있겠다
마지막으로,
정확한 이유는 모르겠지만 결과를 출력하면 동일한 row가 여러 개 나오더라.
그래서 일단은 car_id에 distinct를 주었다
2안
SELECT
*
FROM (
SELECT
distinct car_id
, car_type
, round((1-(discount_rate/100)) * 30 * daily_fee,0) as 'fee'
# , start_date
# , end_date
FROM (
SELECT
c.car_id
, c.car_type
, c.daily_fee
, d.discount_rate
, h.start_date
, h.end_date
FROM
CAR_RENTAL_COMPANY_CAR c
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.car_id = h.car_id
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON c.car_type = d.car_type
WHERE
(c.car_type in ('세단', 'SUV'))
and not((year(h.start_date)=2022 and month(h.start_date)=11)
or (year(h.end_date)=2022 and month(h.end_date)=11)
or (h.start_date < '2022-11-01' and h.end_date > '2022-11-30')
)
and (d.duration_type = '30일 이상')
) aa
) bb
WHERE
(fee >= 500000)
and (fee < 2000000)
ORDER BY
fee desc
, car_type
, car_id desc
;다시 처음부터 짜보기로 했다.
무조건 세 테이블을 다 조인하기 보다,
각각에서 필요한 연산을 한 뒤 조인을 해보는 걸로
(1) 자동차 종류가 세단 or SUV
(2) 2022년 11월 1일 ~ 2022년 11월 30일까지 대여 가능 = 아래 3가지 경우를 제외
(2-1) 2022년 11월 중 대여 시작
(2-2) 2022년 11월 중 대여 종료
(2-3) 2022년 11월 전 대여 시작 & 2022년 11월 후 대여 종료
(3) 30일 대여 금액이 50만원 이상 200만원 미만
(3-1) 대여 금액 = 일일 대여 요금 * (100-할인율) / 100 * 30
(4) 자동차 id, 자동차 종류, 대여 금액 출력
(5) 대여 금액 desc, 자동차 종류, 자동차 id desc
1) CAR_RENTAL_COMPANY_CAR c 테이블은 car_id, car_type, daily_fee
2) CAR_RENTAL_COMPANY_RENTAL_HISTORY h 테이블은 car_id, start_date, end_date
3) CAR_RENTAL_COMPANY_DISCOUNT_PLAN d 테이블은 car_type, duration_type, discount_rate
SELECT
car_id
, car_type
, fee
FROM (
SELECT
distinct c.car_id
, c.car_type
, round(c.daily_fee * (100-d.discount_rate)/100*30,0) as 'fee'
FROM (
SELECT
car_id
, car_type
, daily_fee
FROM
CAR_RENTAL_COMPANY_CAR
WHERE
car_type in ('세단', 'SUV')
) c
INNER JOIN
(
SELECT
car_id
, start_date
, end_date
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE
not(
#2022년 11월 중 대여 시작
(year(start_date)=2022 and month(start_date)=11)
#2022년 11월 중 반납
or (year(end_date)=2022 and month(end_date)=11)
# 2022년 11월 이전 대여 시작 & 2022년 11월 이후 반납
or ((start_date < '2022-11-01')
and (end_date > '2022-11-30'))
)
) h ON c.car_id = h.car_id
INNER JOIN
(
SELECT
car_type
, discount_rate
FROM
CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE
duration_type = '30일 이상'
) d ON c.car_type = d.car_type
) aa
WHERE
(fee >= 500000)
and (fee < 2000000)
ORDER BY
fee desc
, car_type
, car_id desc
;하지만 이 코드가 정답으로 처리되는 일은 없었다
※ 조건 조건 ON절에 넣어주는 칼럼은 하위 테이블의 SELECT에는 넣어줘야 한다
SQL 실행 순서는 서브쿼리가 전부 끝난 뒤 메인 쿼리로 넘어가는데, ON절은 상위쿼리에서 돌아가는 순서다
그렇기에 하위쿼리에서 SELECT를 해놓기는 해야 ON절에서 사용할 수 있게 된다
할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.
==> 30일 이상일 때의 discount_rate가 잘 적용되고 있는지 확인 필요. car_type에 따라 다르게 적용되고 있는지도.
