프로그래머스에서 제공하는 SQL문제 매일 풀기 챌린지
문제 설명
다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 입니다.
CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며,
CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다
| Column name | Type | Nullable |
| CAR_ID | INTEGER | FALSE |
| CAR_TYPE | VARCHAR(255) | FALSE |
| DAILY_FEE | INTEGER | FALSE |
| OPTIONS | VARCHAR(255) | FALSE |
자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진' 이 있습니다. 자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(예: ''열선시트,스마트키,주차감지센서'')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며,
HISTORY_ID, CAR_ID, START_DATE, END_DATE 는
각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다
| Column name | Type | Nullable |
| HISTORY_ID | INTEGER | FALSE |
| CAR_ID | INTEGER | FALSE |
| START_DATE | DATE | FALSE |
| END_DATE | DATE | FALSE |
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은 아래와 같은 구조로 되어있으며,
PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE는
각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다
| Column name | Type | Nullable |
| PLAN_ID | INTEGER | FALSE |
| CAR_TYPE | VARCHAR(255) | FALSE |
| DURATION_TYPE | VARCHAR(255) | FALSE |
| DISCOUNT_RATE | INTEGER | FALSE |
할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.
문제
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
예시
예를 들어 CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블이 다음과 같다면
[ CAR_RENTAL_COMPANY_CAR ]
| CAR_ID | CAR_TYPE | DAILY_FEE | OPTIONS |
| 1 | 트럭 | 26000 | 가죽시트,열선시트,후방카메라 |
| 2 | SUV | 14000 | 스마트키,네비게이션,열선시트 |
| 3 | 트럭 | 32000 | 주차감지센서,후방카메라,가죽시트 |
[ CAR_RENTAL_COMPANY_RENTAL_HISTORY ]
| HISTORY_ID | CAR_ID | START_DATE | END_DATE |
| 1 | 1 | 2022-07-27 | 2022-08-02 |
| 2 | 1 | 2022-08-03 | 2022-08-04 |
| 3 | 2 | 2022-08-05 | 2022-08-20 |
| 4 | 2 | 2022-08-09 | 2022-08-12 |
| 5 | 3 | 2022-09-16 | 2022-10-15 |
[ CAR_RENTAL_COMPANY_DISCOUNT_PLAN ]
| PLAN_ID | CAR_TYPE | DURATION_TYPE | DISCOUNT_RATE |
| 1 | 트럭 | 7일 이상 | 5% |
| 2 | 트럭 | 30일 이상 | 7% |
| 3 | 트럭 | 90일 이상 | 10% |
| 4 | 세단 | 7일 이상 | 5% |
| 5 | 세단 | 30일 이상 | 10% |
| 6 | 세단 | 90일 이상 | 15% |
자동차 종류가 '트럭' 인 자동차의 대여 기록에 대해서 대여 기간을 구하면,
- 대여 기록 ID가 1인 경우, 7일
- 대여 기록 ID가 2인 경우, 2일
- 대여 기록 ID가 5인 경우, 30일입니다.
대여 기간 별로 일일 대여 요금에 알맞은 할인율을 곱하여 금액을 구하면 다음과 같습니다.
- 대여 기록 ID가 1인 경우, 일일 대여 금액 26,000원에서 5% 할인율을 적용하고 7일을 곱하면 총 대여 금액은 172,900
- 대여 기록 ID가 2인 경우, 일일 대여 금액 26,000원에 2일을 곱하면 총 대여 금액은 52,000
- 대여 기록 ID가 5인 경우, 일일 대여 금액 32,000원에서 7% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 892,800원이 되므로, 대여 금액을 기준으로 내림차순 정렬 및 대여 기록 ID를 기준으로 내림차순 정렬하면 다음과 같아야 합니다
따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.
| HISTORY_ID | FEE |
| 5 | 892800 |
| 1 | 172900 |
| 2 | 52000 |
※ 주의사항
FEE의 경우 예시처럼 정수부분만 출력되어야 합니다.
♥ 오라클 정답
select n.history_id,
round(((trunc(n.end_date) - trunc(n.start_date) + 1) * n.daily_fee) * ((100- nvl(d.discount_rate,0))/100)) as fee
from (select h.history_id, c.car_type, c.daily_fee, h.start_date, h.end_date,
case when (trunc(h.end_date) - trunc(h.start_date) + 1) >= 90 then '90일 이상'
when (trunc(h.end_date) - trunc(h.start_date) + 1) >= 30 then '30일 이상'
when (trunc(h.end_date) - trunc(h.start_date) + 1) >= 7 then '7일 이상'
else '0' end as duration
from car_rental_company_rental_history h, car_rental_company_car c
where h.car_id = c.car_id
and c.car_type = '트럭') n
left join (select duration_type, discount_rate
from car_rental_company_discount_plan
where car_type = '트럭') d
on n.duration = d.duration_type
order by fee desc, n.history_id desc;
♥ MySQL 정답
select n.history_id, round(((datediff(n.end_date, n.start_date)+1) * n.daily_fee) * ((100- ifnull(d.discount_rate,0))/100)) fee from
( select h.history_id, c.car_type, c.daily_fee, h.start_date, h.end_date
, case when datediff(h.end_date, h.start_date)+1 >= 90 then '90일 이상'
when datediff(h.end_date, h.start_date)+1 >= 30 then '30일 이상'
when datediff(h.end_date, h.start_date)+1 >= 7 then '7일 이상'
else 0 end as duration
from car_rental_company_rental_history h, car_rental_company_car c
where h.car_id = c.car_id
and c.car_type = '트럭' ) n
left join (select duration_type, discount_rate
from car_rental_company_discount_plan
where car_type = '트럭') d
on n.duration = d.duration_type
order by fee desc, n.history_id desc;
문제를 풀기 위해 알아야 할 내용들
■ EQUI JOIN
조인은 여러 개의 테이블의 컬럼들을 활용하여 한 번에 원하는 것을 출력하고자 할 때 사용하는 문법이다.
그 중 EQUI JOIN 이란,
조인하려는 테이블 사이에 공통된 컬럼이 있을 경우 (컬럼명은 달라도 되고, 데이터가 공통될 때)
EQUAL(=) 조인하는 조인 문법이다.
■ LEFT OUTER JOIN
조인은 여러 개의 테이블의 컬럼들을 활용하여 한 번에 원하는 것을 출력하고자 할 때 사용하는 문법이다.
그 중 LEFT JOIN이란
LEFT 테이블의 모든 레코드는 유지하고, 우측 테이블과 일치하는 레코드가 없을 때
왼쪽 테이블의 레코드는 유지하고 있음
■ CASE WHEN 조건문
CASE WHEN [조건1] TEHN [조건1이 참일 때 출력할 값]
WHEN [조건2] THEN [조건2가 참일 때 출력할 값]
ELSE [조건1,조건2..등 모두 해당되지 않을 때 출력할 값]
END
■ DATEDIFF( A, B )
DATE TYPE의 DATA FORMAT을 가진
값에 대하여 A-B를 구하는 함수이다.
■ ROUND
ROUND([숫자|컬럼명], [표시할 자리수])
예를들어 3.1415라는 수가 있을 때
두번째 인자에 2라는 값을 넣으면, 소수점 두번째 자리까지만 표시되고
3번째 자리의 값을 반올림 한다는 의미이다.
■ TRUNC() 함수
TRUNC( [ 컬럼명 ] , [ 버릴 자릿수 ] )
TRUNC 함수를 사용해서 각 구간에 해당하는 데이터를
특정 값으로 전처리 해준다.
특정 값은 각 구간대 별 최소 금액으로 설정해준다.
TRUNC 함수를 사용할 때 버릴 자릿수에 음수를 넣을 경우
-1 은 1의 자릿수를 버리고 0으로 나타낸다.
( ex. 15,345 → 15,340 )
따라서 - 4를 이용해 천의 자릿수까지 버리고 0으로 나타낸다.
( ex. 15,345 → 10,000)
■ NVL( A, B) - Oracle
이 함수는 A 값이 NULL일 때 B를 출력한다는 함수이다.
따라서 A에 NAME을 넣으면,
이름 컬럼에 NULL값이 있다면 B를 출력하라는 말로 해석할 수 있다.
주의할 점은 싱글 쿼테이션 마크 안에서는 대소문자를 구분하기 때문에
정확히 No name이라고 작성해줘야한다.
■IF NULL(A,B) - MySQL
이 함수는 A값이 NULL일때 B를 출력한다는 함수이다.
따라서 A에 NAME을 넣으면,
이름 컬럼에 NULL값이 있다면 B를 출력하라는 말로 해석할 수 있다.
♥ 정답 해설
차근 차근 조건을 나눠보면,
#1. 자동차 종류가 '트럭' 인 자동차의 대여 기록에 대해서 대여 기간을 구하기
#2. CAR_TYPE이 트럭인 데이터의 DURATION_TYPE, DISCOUNT_RATE 출력하기
#3. 1번과 2번에서 생성한 테이블을 LEFT JOIN 하여 HISTORY_ID, FEE컬럼을 출력한다.
# 1. 자동차 종류가 '트럭' 인 자동차의 대여 기록에 대해서 대여 기간을 구하면,
Oracle의 경우에는 trunc함수를 사용하여 날짜를 정수로 변환한 후 대여 기간을 구한다.
MySQL의 경우에는 datediff를 사용해서 대여 기간을 구하는데, 이때 +1을 해줘야한다.
각 대여 기간의 이름을 DURATION(대여기간)으로 지정하여,
트럭인 자동차의 대여기록에 대해서 대여 기간을 구한다.
select h.history_id, c.car_type, c.daily_fee, h.start_date, h.end_date,
case when (trunc(h.end_date) - trunc(h.start_date) + 1) >= 90 then '90일 이상'
when (trunc(h.end_date) - trunc(h.start_date) + 1) >= 30 then '30일 이상'
when (trunc(h.end_date) - trunc(h.start_date) + 1) >= 7 then '7일 이상'
else '0' end as duration
from car_rental_company_rental_history h, car_rental_company_car c
where h.car_id = c.car_id
and c.car_type = '트럭'select h.history_id, c.car_type, c.daily_fee, h.start_date, h.end_date
, case when datediff(h.end_date, h.start_date)+1 >= 90 then '90일 이상'
when datediff(h.end_date, h.start_date)+1 >= 30 then '30일 이상'
when datediff(h.end_date, h.start_date)+1 >= 7 then '7일 이상'
else 0 end as duration
from car_rental_company_rental_history h, car_rental_company_car c
where h.car_id = c.car_id
and c.car_type = '트럭'
#2. CAR_TYPE이 트럭인 데이터의 DURATION_TYPE, DISCOUNT_RATE 출력하기
1번에서 만든 테이블과 조인하기 위해서 DURATION_TYPE 별 DISCOUNT_RATE를 출력한다.
select duration_type, discount_rate
from car_rental_company_discount_plan
where car_type = '트럭'
#3. 1번과 2번에서 생성한 테이블을 LEFT JOIN 하여 HISTORY_ID, FEE컬럼을 출력한다.
1번 테이블에서 생성한 컬럼 DURATION 과
2번 테이블에서 생성한 컬럼 DURATION_TYPE을 활용하여 JOIN해준다.
이 때 LEFT JOIN을 해야하는 이유는 대여기간이 7일 보다 적은 데이터에 대해서도 대여금액을 구해야하기 때문이다.
조인해서 사용할 수 있는 컬럼들을 활용하여 FEE 컬럼을 생성해준다.
FEE = 대여일수 * 대여비용 * ((100-할인율)/100)
FEE = (end_date - start_date + 1) * daily_fee * ((100 - discount_rate)/100)
그런데, discount_rate의 null값에 의해서 계산이 안되는 컬럼들이 존재하여
Oracle은 nvl함수 => nvl( [컬럼명], [null일 경우 출력할 값] )
Mysql은 ifnull 함수를 사용하여 => ifnull( [컬럼명], [null일 경우 출력할 값] )
discount_rate 컬럼에 null 값이 존재할 경우 0으로 출력하도록 적용해준다.
적용 후 출력된 결과를 정수부분만 출력하기 위해서 round함수를 둘러준다
(두번째 인자에 아무것도 주지 않으면 기본적으로 정수부분만 출력된다)
select n.history_id,
round(((trunc(n.end_date) - trunc(n.start_date) + 1) * n.daily_fee) * ((100- nvl(d.discount_rate,0))/100)) as fee
from (select h.history_id, c.car_type, c.daily_fee, h.start_date, h.end_date,
case when (trunc(h.end_date) - trunc(h.start_date) + 1) >= 90 then '90일 이상'
when (trunc(h.end_date) - trunc(h.start_date) + 1) >= 30 then '30일 이상'
when (trunc(h.end_date) - trunc(h.start_date) + 1) >= 7 then '7일 이상'
else '0' end as duration
from car_rental_company_rental_history h, car_rental_company_car c
where h.car_id = c.car_id
and c.car_type = '트럭') n
left join (select duration_type, discount_rate
from car_rental_company_discount_plan
where car_type = '트럭') d
on n.duration = d.duration_type
order by fee desc, n.history_id desc;select n.history_id, round(((datediff(n.end_date, n.start_date)+1) * n.daily_fee) * ((100- ifnull(d.discount_rate,0))/100)) fee from
( select h.history_id, c.car_type, c.daily_fee, h.start_date, h.end_date
, case when datediff(h.end_date, h.start_date)+1 >= 90 then '90일 이상'
when datediff(h.end_date, h.start_date)+1 >= 30 then '30일 이상'
when datediff(h.end_date, h.start_date)+1 >= 7 then '7일 이상'
else 0 end as duration
from car_rental_company_rental_history h, car_rental_company_car c
where h.car_id = c.car_id
and c.car_type = '트럭' ) n
left join (select duration_type, discount_rate
from car_rental_company_discount_plan
where car_type = '트럭') d
on n.duration = d.duration_type
order by fee desc, n.history_id desc;
'SQL 문제 풀기 > 1. 프로그래머스 SQL : 2023.12.17~2024.2.29' 카테고리의 다른 글
| <프로그래머스 74> 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기(Lv.4) (2) | 2024.02.28 |
|---|---|
| <프로그래머스 73> 입양 시각 구하기 (2) (Lv.4) (5) | 2024.02.27 |
| <프로그래머스 72> 오프라인/온라인 판매 데이터 통합하기(Lv.4) (0) | 2024.02.26 |
| <프로그래머스 71> 그룹별 조건에 맞는 식당 목록 출력하기(Lv.4) (2) | 2024.02.25 |
| <프로그래머스 70> 주문량이 많은 아이스크림들 조회하기(Lv.4) (0) | 2024.02.24 |
