뚝딱뚝딱

CAST(A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01) AS SIGNED) AS FEE

💡 CAST 연산자
: 계산된 FEE값을 정수로 변환

AS SIGNED : 부호를 유지

AS UNSIGNED : 부호 제거

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

💡 문제

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 부터 2022-11-30 까지 대여가능
• 30일간의 대여금액 >=500000 AND <2000000
• 위를 만족하는 자동차의 CAR_ID, CAR_TYPE, FEE 출력
• 단, 대여금액 기준 내림차순, 자동차 종류 기준 오름차순, 자동차 ID기준 내림차순 정렬

✅ 문제 풀이

• 대여 가능 여부를 판단한다.
  HISTORY 테이블에서 START_DATE와 END_DATE가 있다. 11월 달에 대여한 적이 있는지를 보려면, START_DATE는 11월 30일 이전이어야 하고, END_DATE는 11월 1일 이후여야 한다. SQL문으로 나타내면 다음과 같다.

CAR_RENTAL_COMPANY_RENTAL_HISTORY C 
    WHERE C.START_DATE<='2022-11-30' AND C.END_DATE>='2022-11-01'

• 그럼 이 날짜에 RENTAL한 적이 있는 자동차는 제외하면 된다. 
• 이렇게 생각할 수도 있다. 11월 달에 대여한 적 없는 자동차만 고르면 되는 거 아닌가? 로직을 어떻게 짜냐에 다르긴 하지만, 단순하게 각 레코드 마다 11월달에 대여한 적 없는 경우를 고른다면, 11월에도 대여했었지만 다른 날짜에도 대여했던 동일한 자동차도 포함되어 버릴 수 있기 때문에, 11월에 대여한 자동차의 ID 자체를 제외하는 식으로 접근하였다. 이는 NOT IN 연산자를 통해 제외할 수 있다.

A.CAR_ID NOT IN (
    SELECT DISTINCT C.CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C 
    WHERE C.START_DATE<='2022-11-30' AND C.END_DATE>='2022-11-01'
)

• 첫 WHERE 조건을 하나 완성하였다.

• 30일간의 대여금액을 계산하기 위해 테이블을 조인한다.
  이를 구하기 위해서는 CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블을 INNER 조인해주어야 한다. 그래서 각 자동차마다 CAR_TYPE에 따라 DISCOUNT 정보를 갖도록 할것이다.

FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
ON A.CAR_TYPE=B.CAR_TYPE

• 두 테이블의 공통 속성인 CAR_TYPE을 기준으로 JOIN한다.

• 조건에 따라 WHERE 절에 조건을 추가로 작성해준다.

AND A.CAR_TYPE IN('SUV', '세단') 
AND B.DURATION_TYPE = '30일 이상' 
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)>=500000 
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)<2000000

• CAR_TYPE이 'SUV', '세단' 중에 해당하면 조회한다. IN 연산자를 사용하였다.
• 조인 후에는 각 자동차 마다 CAR_TYPE에 따라 3개의 DISCOUNT_RATE 정보가 엮여있을 것이다. 이 중에서 DURATION_TYPE이 '30일 이상' 인것 만 사용하도록 한다.
• 대여금액은 조건에 해당하는 자동차의 DAILY_FEE*30*할인률 이기 때문에, 이 값이 50만원 이상 200만원 미만인 것만 추리도록 한다.

• 필요한 정보를 출력한다.

SELECT A.CAR_ID, A.CAR_TYPE, CAST(A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)AS SIGNED) AS FEE

• 위에 조건에 부합하는 자동차의 CAR_ID, CAR_TYPE, FEE를 조회하도록 한다.
• 이때 FEE의 경우 *0.01 때문에 소수점 이하 두자리 형태로 출력되게 되는데, 조건의 주의사항에서 FEE의 경우 정수 형태로 출력하도록 되어있기 때문에, CAST 연산자를 사용하여 정수형태로 출력하도록 한다. AS SIGNED는 소수점을 없애면서 부호는 그대로 유지한다는 것이다.

• 조회결과를 주어진 정렬 조건에 맞도록 설정하기

ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

• 마지막에 ORDER BY 문을 통해서 FEE를 기준으로 내림차순, CAR_TYPE을 기준으로 오름차순, CAR_ID를 기준으로 내림차순 정렬하도록 해준다.

✏ 코드 전문

SELECT A.CAR_ID, A.CAR_TYPE, CAST(A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)AS SIGNED) AS FEE 
FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
ON A.CAR_TYPE=B.CAR_TYPE
WHERE A.CAR_ID NOT IN (
    SELECT DISTINCT C.CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C 
    WHERE C.START_DATE<='2022-11-30' AND C.END_DATE>='2022-11-01'
)
AND A.CAR_TYPE IN('SUV', '세단') 
AND B.DURATION_TYPE = '30일 이상' 
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)>=500000 
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)<2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;