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

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

프로그래머스

 

 

 

SELECT C.CAR_ID AS CAR_ID, C.CAR_TYPE AS CAR_TYPE, ROUND(C.DAILY_FEE*30*(100-P.DISCOUNT_RATE)/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON C.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE

WHERE C. CAR_ID NOT IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
# 날짜 이해    
WHERE START_DATE < '2022-12-01' AND END_DATE > '2022-11-01'
)
AND P.DURATION_TYPE = '30일 이상'
GROUP BY C.CAR_ID
HAVING C.CAR_TYPE IN('세단','SUV') 
AND (FEE >= 500000 AND FEE < 2000000)
ORDER BY FEE DESC, CAR_TYPE,CAR_ID DESC

 

 

문제

 

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개 테이블 join

2. 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 => WHERE절

WHERE (C.CAR_TYPE IN ('SUV','세단'))

3. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능 ,  30일간의 대여 금액이 50만원 이상 200만원 미만 => 서브쿼리문

4. 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력 => SELECT문, FEE 대여금액 계산식으로 나타내기

5. ORDER BY절 => 대여 금액을 기준으로 내림차순 정렬, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬

 

 

 

 

수정 코드

SELECT 
    C.CAR_ID AS CAR_ID, 
    C.CAR_TYPE AS CAR_TYPE, 
    ROUND(C.DAILY_FEE * 30 * (100 - P.DISCOUNT_RATE) / 100) AS FEE
FROM 
    CAR_RENTAL_COMPANY_CAR C
JOIN 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    ON C.CAR_ID = H.CAR_ID
JOIN 
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
    ON C.CAR_TYPE = P.CAR_TYPE
WHERE 
    C.CAR_ID NOT IN (
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        
#2022년 11월 1일부터 2022년 11월 30일까지 대여기간이 겹치지 않는 car를 찾아야해                 
        WHERE 
            START_DATE < DATE('2022-12-01') 
            AND END_DATE > DATE('2022-11-01')
    )
    AND P.DURATION_TYPE = '30일 이상'
    AND C.CAR_TYPE IN ('세단', 'SUV')
GROUP BY 
    C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE, P.DISCOUNT_RATE
HAVING 
    ROUND(C.DAILY_FEE * 30 * (100 - P.DISCOUNT_RATE) / 100) BETWEEN 500000 AND 2000000
ORDER BY 
    FEE DESC, CAR_TYPE, CAR_ID DESC;

 

 

 

 

 

 

• 날짜 비교: SQL 표준에서 문자열로 날짜를 비교할 때 문제가 생길 수 있으므로, 항상 DATE 형식으로 처리하는 것이 좋습니다.

1) 대여금액 = 1일 가격 * 기간 * 종류별기간별 할인율

 

2) 

 

 START_DATE < DATE('2022-12-01') 
AND END_DATE > DATE('2022-11-01')

 

조건의 의미

1. START_DATE < DATE('2022-12-01'):
   • 대여가 시작된 날짜(START_DATE)가 2022년 12월 1일 이전이어야 한다는 조건입니다.
   • 즉, 대여가 12월 1일 이전에 이미 시작된 상태임을 의미합니다.
2. END_DATE > DATE('2022-11-01'):
   • 대여가 종료된 날짜(END_DATE)가 2022년 11월 1일 이후여야 한다는 조건입니다.
   • 즉, 대여가 11월 1일 이후까지 진행되고 있음을 의미합니다.

전체 조건의 역할

• 두 조건을 AND로 결합하면:
  • 대여 기간(START_DATE ~ END_DATE)이 2022년 11월 1일부터 2022년 11월 30일까지의 기간과 겹치는지를 확인하게 됩니다.
  • 예를 들어:
    • START_DATE = '2022-10-20'이고 END_DATE = '2022-11-10'이면, 이 대여 기록은 11월 1일 ~ 11월 30일과 겹치므로 조건을 만족합니다.
    • START_DATE = '2022-12-01'이면, 이 기록은 조건을 만족하지 않습니다. (11월과 겹치지 않음)
    • END_DATE = '2022-10-31'이라면, 이 기록도 조건을 만족하지 않습니다. (11월과 겹치지 않음)

 

 

 

• HAVING 절: HAVING 절은 집계 함수와 함께 사용하는 것이 일반적입니다. 집계되지 않은 값을 조건으로 사용할 때는 WHERE 절로 옮기는 것이 바람직합니다.

FEE별칭은 SQL실행 순서상 SELECT문이 마지막에 실행되니 order by 절 이외엔 사용하지 않기!!

• 별칭 사용: SELECT 절에서 별칭을 선언했지만 HAVING에서 다시 참조하고 있어 오류가 발생할 수 있습니다. 이를 개선합니다.
• GROUP BY: GROUP BY 절에서 SELECT에 포함된 모든 열이 포함되어야 합니다.