74번
문제 설명
다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은
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 테이블에서
자동차 종류가 '세단' 또는 'SUV' 인 자동차 중
2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고
30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서
자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 대여 금액을 기준으로 내림차순 정렬하고,
대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬,
자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.
문제 길이부터 심상치 않은 문제였다.
문제 설명부터 구조를 파악하기 힘들었기에 이해하기 쉽게
# car_type '세단', 'suv'
# 2022-11-01 and 2022-11-30 대여내역 없고
# daily_fee * (1 - discount_rate*0.01) * 30 가 50만원 이상, 200만원 미만
# car_id, car_type, fee(대여 금액)
# order 3 desc, 2, 1 desc
# round(fee, 0) / int(fee) 가능?
이렇게 필요한 내용을 정리해뒀다.
전 문제가 cte 문제였고 이 문제 또한 여러 테이블에서 필요한 조건에 해당하는 항목들을 찾아와야하기 때문에
WITH 구문을 써야겠다고 생각을 했지만 아직 미숙한 상태에서 혼자 다 풀어내다간 오늘 하루종일 걸릴 거 같아서
이 문제도 결국 다른 분의 풀이를 참고하게 되었다.
https://school.programmers.co.kr/questions/76645
프로그래머스
코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요.
programmers.co.kr
이분의 풀이를 참고했다.
with available as (
select distinct car_id
from CAR_RENTAL_COMPANY_RENTAL_HISTORY h
where not exists (
select 1
from car_rental_company_rental_history r
where r.car_id = h.car_id
and start_date <= '2022-11-30'
and end_date >= '2022-11-01')
)
, dis_plan as (
select *
from CAR_RENTAL_COMPANY_DISCOUNT_PLAN
where duration_type = '30일 이상'
)
select a.car_id
, c.car_type car_type
, round((c.daily_fee * 30 * (1- dp.discount_rate*0.01)), 0) as fee
from available a
join CAR_RENTAL_COMPANY_CAR c
on a.car_id = c.car_id
join dis_plan dp
on c.car_type = dp.car_type
where c.car_type in ('SUV', '세단')
and (c.daily_fee * 30 * (1- dp.discount_rate*0.01)) >= 500000
and (c.daily_fee * 30 * (1- dp.discount_rate*0.01)) < 2000000
order by 3 desc, 2, 1 desc
정리를 위해 뤼튼에 상세하게 정리해달라고 부탁했다.
1. CTE 정의
WITH available AS (
SELECT DISTINCT car_id
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
WHERE NOT EXISTS (
SELECT 1
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY r
WHERE r.car_id = h.car_id
AND start_date <= '2022-11-30'
AND end_date >= '2022-11-01'
)
)
- CTE available: 이 부분에서는 사용 가능한 차량의 ID를 선택합니다.
- DISTINCT를 사용하여 중복된 차량 ID를 제거합니다.
- NOT EXISTS 서브쿼리를 사용하여 특정 기간(2022년 11월 1일 ~ 11월 30일) 동안 대여된 기록이 없는 차량만 선택합니다. 즉, 이 기간 동안 대여된 적이 없는 차량들을 찾습니다.
2. 두 번째 CTE 정의
, dis_plan AS (
SELECT *
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE duration_type = '30일 이상'
)
- CTE dis_plan: 차량의 할인 계획을 가져오는 부분입니다.
- duration_type이 '30일 이상'인 할인 계획만 선택합니다. 이 데이터는 차량의 장기 대여에 적용되는 할인 정보를 포함합니다.
3. 메인 쿼리
SELECT a.car_id
, c.car_type AS car_type
, ROUND((c.daily_fee * 30 * (1 - dp.discount_rate * 0.01)), 0) AS fee
FROM available a
JOIN CAR_RENTAL_COMPANY_CAR c ON a.car_id = c.car_id
JOIN dis_plan dp ON c.car_type = dp.car_type
WHERE c.car_type IN ('SUV', '세단')
AND (c.daily_fee * 30 * (1 - dp.discount_rate * 0.01)) >= 500000
AND (c.daily_fee * 30 * (1 - dp.discount_rate * 0.01)) < 2000000
ORDER BY 3 DESC, 2, 1 DESC;
- SELECT 절:
- a.car_id: 사용 가능한 차량의 ID.
- c.car_type AS car_type: 차량의 종류.
- ROUND((c.daily_fee * 30 * (1 - dp.discount_rate * 0.01)), 0) AS fee: 차량의 30일 대여 요금을 계산합니다.
- c.daily_fee * 30는 30일 기준의 기본 요금입니다.
- (1 - dp.discount_rate * 0.01)는 할인율을 적용하여 최종 요금을 계산합니다.
- ROUND 함수를 사용하여 소수점을 반올림합니다.
- JOIN 절:
- JOIN CAR_RENTAL_COMPANY_CAR c ON a.car_id = c.car_id: 사용 가능한 차량 ID와 차량 정보를 연결합니다.
- JOIN dis_plan dp ON c.car_type = dp.car_type: 차량 종류를 기준으로 할인 계획과 연결합니다.
- WHERE 절:
- c.car_type IN ('SUV', '세단'): SUV 또는 세단 차량만 선택합니다.
- (c.daily_fee * 30 * (1 - dp.discount_rate * 0.01)) >= 500000: 30일 대여 요금이 500,000 이상인 차량만 선택합니다.
- (c.daily_fee * 30 * (1 - dp.discount_rate * 0.01)) < 2000000: 30일 대여 요금이 2,000,000 미만인 차량만 선택합니다.
- ORDER BY 절:
- ORDER BY 3 DESC, 2, 1 DESC: 결과를 요금(fee) 기준으로 내림차순 정렬하고, 같은 요금 내에서 차량 종류(car_type)는 오름차순으로, 차량 ID(car_id)는 내림차순으로 정렬합니다.
결론
이 쿼리는 특정 기간 동안 대여되지 않은 SUV 및 세단 차량을 찾아, 30일 대여 요금이 500,000 이상 2,000,000 미만인 차량의 목록을 생성합니다. 이 목록은 할인 요금을 계산하여 결과를 출력하며, 요금이 높은 순서로 정렬됩니다.
* 추가적인 질문
1. cte 정의 부분에서 왜 SELECT 1, WHERE r.car_id = h.car_id 가 들어가야 하는지 알려줘
답변
CTE 정의 부분에서 SELECT 1과 WHERE r.car_id = h.car_id가 사용되는 이유를 설명하겠습니다.
1. NOT EXISTS 사용 목적
NOT EXISTS는 서브쿼리의 결과가 존재하지 않을 때 TRUE를 반환하는 조건입니다.
이를 통해 특정 조건을 만족하는 레코드가 존재하지 않는지를 확인할 수 있습니다.
2. SELECT 1의 의미
SELECT 1은 서브쿼리에서 반환할 값이 무엇인지에 대한 명시적인 내용입니다.
사실 SELECT 1 대신 SELECT *를 사용해도 결과는 동일합니다.
그러나 SELECT 1은 불필요한 데이터 반환을 피하고, 서브쿼리의 목적을 명확히 하기 위해 자주 사용됩니다.
즉, 이 서브쿼리에서는 어떤 값을 반환할 필요가 없고, 단순히 존재 여부만 체크하는 것이기 때문에 1을 선택합니다.
3. WHERE r.car_id = h.car_id의 역할
WHERE r.car_id = h.car_id 조건은 서브쿼리가 h 테이블의 현재 행(h.car_id)과
동일한 car_id를 가진 행이 r 테이블에 존재하는지를 확인합니다.
이 조건이 없으면, 모든 car_id에 대해 서브쿼리가 평가되므로, 원하는 결과를 얻을 수 없습니다.
즉, 특정 차량이 특정 기간 동안 대여된 기록이 있는지를 확인하기 위해 car_id를 비교해야 합니다.
전체적인 로직
기본적으로 이 NOT EXISTS 서브쿼리는 다음의 의미를 가집니다:
h 테이블(렌탈 히스토리)에서 현재 처리 중인 차량(h.car_id)에 대해,
r 테이블(렌탈 히스토리)에서 동일한 차량 ID(r.car_id)를 가진 레코드가 존재하고,
그 레코드의 대여 시작일이 2022년 11월 30일 이전이며 대여 종료일이 2022년 11월 1일 이후인 경우,
즉 해당 차량이 이 기간 동안 대여된 적이 있다면, 그 차량은 사용 가능 목록에 포함되지 않습니다.
결론적으로, SELECT 1과 WHERE r.car_id = h.car_id는 NOT EXISTS 조건이 올바르게 작동하도록 하여
특정 기간 동안 대여되지 않은 차량만을 선택할 수 있도록 합니다.round(column_name, 0) 말고 정수로 표현하려면?
답변
SQL에서 소수점을 제거하고 정수를 표현하려면 ROUND(column_name, 0)
대신 CAST나 CONVERT 함수를 사용할 수 있습니다. 두 가지 방법을 소개하겠습니다.
1. CAST 함수 사용
CAST(column_name AS INT)
CAST 함수를 사용하여 column_name을 정수형으로 변환합니다. 이 경우 소수점 이하의 값은 버려집니다.
2. CONVERT 함수 사용
CONVERT(INT, column_name)
CONVERT 함수도 동일하게 column_name을 정수형으로 변환합니다.
예시
아래는 두 가지 방법을 사용하여 소수점을 제거하는 예시입니다:
SELECT CAST(column_name AS INT) AS integer_value
FROM your_table;
-- 또는
SELECT CONVERT(INT, column_name) AS integer_value
FROM your_table;
이렇게 하면 column_name의 값이 정수로 변환되어 소수점 이하가 제거된 결과를 얻을 수 있습니다.
# 괜히 가독성 높인다고 500,000 쓸 필요 없다. 어차피 오류 뜬다.
# DISCOUNT_RATE 가 %로 되어있기 때문에 0.01을 곱해줘야 한다.
75번
문제
CAR_RENTAL_COMPANY_CAR 테이블과
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과
CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서
자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서
대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여
대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요.
결과는 대여 금액을 기준으로 내림차순 정렬하고,
대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
위 문제와 같은 테이블을 공유하는 문제이다.
select history_id
, case when (end_date - start_date) < 7
then round((daily_fee * (end_date - start_date)), 0)
when (end_date - start_date) between 7 and 29
then round(daily_fee * (1-0.05)*(end_date - start_date), 0)
when (end_date - start_date) between 30 and 89
then round(daily_fee * (1-0.08)*(end_date - start_date), 0)
else round(daily_fee * (1-0.15)*(end_date - start_date), 0)
end as fee
from CAR_RENTAL_COMPANY_RENTAL_HISTORY h
inner join CAR_RENTAL_COMPANY_CAR c
on h.car_id = c.car_id
join CAR_RENTAL_COMPANY_DISCOUNT_PLAN dp
on c.car_type = dp.car_type
where c.car_type = '트럭'
group by history_id
order by 2 desc, 1 desc
풀었는데 역시나 틀렸다.
with discount as (
select duration_type
, (1 - (discount_rate * 0.01)) as rate
from car_rental_company_discount_plan dp
where car_type = '트럭'
)
,
dur as (
select h.history_id, c.car_id, c.daily_fee
, (datediff(end_date, start_date) + 1) duration
, case when datediff(end_date, start_date) + 1 >= 90 then '90일 이상'
when datediff(end_date, start_date) + 1 >= 30 then '30일 이상'
when datediff(end_date, start_date) + 1 >= 7 then '7일 이상'
end as duration_type
from car_rental_company_rental_history h
join car_rental_company_car c
using (car_id)
where car_type = '트럭'
)
select history_id
, case when dur.duration_type = '90일 이상'
then round((dur.duration * dur.daily_fee * discount.rate) , 0)
when dur.duration_type = '30일 이상'
then round((dur.duration * dur.daily_fee * discount.rate) , 0)
when dur.duration_type = '7일 이상'
then round((dur.duration * dur.daily_fee * discount.rate) , 0)
else round(dur.duration*dur.daily_fee)
end as fee
from dur
left join discount
on dur.duration_type = discount.duration_type
order by 2 desc, 1 desc;
와 풀고 이렇게 기쁜 문제는 처음이다...
왠지 오기가 생겨서 몇시간을 붙잡고 있었는데..!
뤼튼에 아무것도 안물어보고 이렇게 어려운 문제를 풀다니 스스로 대견하다 ㅎㅎ
물론 다른 분들의 풀이를 참고하긴 했지만 아예 방식이 달랐고 내가 생각한 방법대로 문제가 풀리니 의아할 따름이다.
이 문제 때문에 저녁 시간 날아가게 생겼는데 ㅋㅋ
이 쿼리는 CTE(Common Table Expression)를 사용하여 두 개의 서브 쿼리와 메인 쿼리로 구성되어 있습니다. 각 단계별로 흐름을 설명하겠습니다.
1. CTE 정의
1.1. discount CTE
with discount as (
select duration_type
, (1 - (discount_rate * 0.01)) as rate
from car_rental_company_discount_plan dp
where car_type = '트럭'
)
- 목적: 할인 계획을 가져오는 부분입니다.
- 컬럼:
- duration_type: 대여 기간 유형을 나타냅니다.
- rate: 할인율을 계산하여 1 - (discount_rate * 0.01)로 표현합니다. 예를 들어, discount_rate가 10이라면 rate는 0.90이 됩니다.
- 조건: 자동차 종류가 '트럭'인 경우에만 해당 할인 계획을 가져옵니다.
1.2. dur CTE
dur as (
select h.history_id, c.car_id, c.daily_fee
, (datediff(end_date, start_date) + 1) duration
, case when datediff(end_date, start_date) + 1 >= 90 then '90일 이상'
when datediff(end_date, start_date) + 1 >= 30 then '30일 이상'
when datediff(end_date, start_date) + 1 >= 7 then '7일 이상'
end as duration_type
from car_rental_company_rental_history h
join car_rental_company_car c
using (car_id)
where car_type = '트럭'
)
- 목적: 대여 기록과 관련된 정보를 가져오는 부분입니다.
- 컬럼:
- history_id: 대여 기록 ID.
- car_id: 자동차 ID.
- daily_fee: 일일 대여 요금.
- duration: 대여 기간을 계산합니다. datediff(end_date, start_date) + 1로 계산하여 대여 시작일과 종료일 사이의 일수(포함)를 구합니다.
- duration_type: 대여 기간에 따라 '90일 이상', '30일 이상', '7일 이상'으로 분류합니다.
- 조건: 자동차 종류가 '트럭'인 대여 기록만 가져옵니다.
2. 메인 쿼리
select history_id
, case when dur.duration_type = '90일 이상'
then round((dur.duration * dur.daily_fee * discount.rate) , 0)
when dur.duration_type = '30일 이상'
then round((dur.duration * dur.daily_fee * discount.rate) , 0)
when dur.duration_type = '7일 이상'
then round((dur.duration * dur.daily_fee * discount.rate) , 0)
else round(dur.duration*dur.daily_fee)
end as fee
from dur
left join discount
on dur.duration_type = discount.duration_type
order by 2 desc, 1 desc;
- 목적: 대여 기록 ID와 대여 금액을 계산하여 결과를 출력합니다.
- 컬럼:
- history_id: dur CTE에서 가져온 대여 기록 ID.
- fee: 대여 금액을 계산하는 부분입니다.
- 대여 기간 유형에 따라 할인된 금액을 계산합니다.
- 대여 기간이 '90일 이상', '30일 이상', '7일 이상'인 경우에는 할인율을 적용한 금액을 계산합니다.
- 할인율을 적용한 후 ROUND 함수를 사용하여 소수점을 반올림합니다.
- 만약 대여 기간이 이 범위에 해당하지 않는 경우에는 할인 없이 대여 금액을 계산합니다.
- JOIN: dur와 discount CTE를 LEFT JOIN하여 대여 기간 유형에 맞는 할인율을 가져옵니다. 만약 대여 기간 유형이 discount에 없는 경우에도 모든 대여 기록이 출력됩니다.
- 정렬: 결과는 대여 금액(fee)을 기준으로 내림차순 정렬하고, 대여 기록 ID(history_id)를 기준으로 내림차순 정렬합니다.
전체 흐름 요약
- 할인 정보를 가져온다: discount CTE에서 '트럭'에 해당하는 할인율을 가져옵니다.
- 대여 기록을 가져온다: dur CTE에서 '트럭'의 대여 기록과 대여 기간을 계산합니다.
- 대여 금액을 계산하고 출력한다: 메인 쿼리에서 대여 기록 ID와 대여 금액을 계산하고, 결과를 정렬하여 출력합니다.
이렇게 구성된 쿼리는 자동차 종류가 '트럭'인 대여 기록에 대해 대여 금액을 정확하게 계산하고, 이를 정렬하여 보여주는 역할을 합니다.
오늘 어제 풀다 만 문제까지 3문제를 풀었는데 5시간 지나갔다...
77번부터? 쉬워진다 하니 설렌다.
그보다 문제를 풀면서 스스로 성장하는 게 느껴져서 참 기쁘다.
알고리즘 풀러 간다...
