코테 준비 | 프로그래머스 자동차 대여 기록 별 대여 금액 구하기(PIVOT,DATEDIFF)

 

[문제] 
3개의 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: 
FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
[조건] 
: CAR_TYPE='트럭' 중 대여기록 별 대여 금액 (FEE)을 구하여 기록 ID, 대여 금액 리스트 출력
- 필터링 : CAR_TYPE='트럭' 
- 정렬 : FEE DESC,HISTORY_ID DESC
- 출력 컬럼 :  HISTORY_ID, FEE ( DAILY_FEE * 대여기간 * (1-DISCOUNT_RATE)*0.01 )
- 출력 양식 : FEE는 정수

 

▶ 문제 해결 & 피드백 POINT : PIVOT & DATEDIFF 

 

[풀이 과정]

• d 서브쿼리 : FEE 컬럼 요소 1 - 할인율 
  • `CAR_RENTAL_COMPANY_DISCOUNT_PLAN` 테이블은 각 대여기간별 할인율이 행단위로 저장 되어있음
  •  `PIVOT` 으로 돌려 `CAR_RENTAL_COMPANY_RENTAL_HISTORY` 과 조인 후 조건문으로 필터링 해줄수있게 함 
• sub 서브쿼리 : FEE 컬럼 요소2 - 대여기간
  • DATEDIFF 함수 활용하여 대여 기간 조회 (단, DATEDIFF는 시작점을 포함하지않음으로 `+1` 필수)
  • `CAR_RENTAL_COMPANY_CAR` 과 `d` 테이블 각각 조인키로 연결 후 where 절에서 `트럭` 일때 필터링
  • CASE 문으로 대여기간별 할인율 연결 
• sub2 서브쿼리 : HISTORY_ID 그룹바이 및 MAX 집계
  • 서브쿼리 d 와 조인하면서 history_id 3개씩 생성 됨 car_id별 (7일/30일/90일)  그룹바이로 중복 제거
  • 대여기간 (diff)가 할인범위(피벗) 안에 없을 경우 0값 기재 -> max 로 할인율 연결
•  아우터 쿼리 : fee 계산 및 출력 양식 맞춤 & 정렬 조건 적용 

WITH d AS ( # 할인율 피벗 서브쿼리(FEE 요소1:할인율)
    SELECT car_type
            ,if(duration_type='7일 이상',discount_rate,0) as discount_7
            ,if(duration_type='30일 이상',discount_rate,0) as discount_30
            ,if(duration_type='90일 이상',discount_rate,0) as discount_90
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE car_type='트럭'
),sub AS ( # 대여기간 (FEE 소요2:대여기간)
    SELECT h.history_id
          ,datediff(h.end_date,h.start_date)+1 as diff
          ,c.daily_fee
          ,case when datediff(h.end_date,h.start_date)+1<7 then 0
                when datediff(h.end_date,h.start_date)+1<30 then d.discount_7
                when datediff(h.end_date,h.start_date)+1<90 then d.discount_30
                else d.discount_90
            end as rate
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
        INNER JOIN CAR_RENTAL_COMPANY_CAR c ON h.car_id =c.car_id 
        INNER JOIN d on c.car_type =d.car_type 
    WHERE c.car_type='트럭'
), sub2 AS ( # 가독성 있게 계산하기위한 서브쿼리
    SELECT history_id
          ,max(diff) as diff
          ,max(rate) as rate
          ,max(daily_fee) as d_fee
    FROM sub
    GROUP BY history_id
)
SELECT history_id
      ,ROUND(diff*d_fee*(1-rate*0.01),0) AS FEE
FROM sub2
ORDER BY fee DESC,history_id DESC

[오류 사항 & 해결 ]

• 문제 조건에서 날짜 필터링 할 경우, 조건을 제대로 확인해야함
• `DATEDIFF` 함수는 두 날짜 사이기간을 출력해줌 
• 문제에서 정의하는 대여기간은 양 끝값을 포함해야함 
  • EX. START_DATE = 2022-08-03, END_DATE = 2022-08-04 이라면 대여기간은 2일로 출력 되어야 함
• DATEDIFF 함수 사용한다면, `+1`일 해줘야 문제에서 정의하는 대여 기간을 구할 수있다

☑️ DATEDIFF 함수의 이해 

• DATEDIFF(끝값,시작값) : 두 시각 사이의 `일`자 구하는 함수 
  • 주의) 파라미터로 시작값, 끝값 을 받으면 마이너스 (음수)로 출력 됨.  즉, 파라미터1 > 파라미터2 

SELECT *
      ,DATEDIFF(END_DATE,START_DATE) AS diff #두 날짜 사이의 기간(일) 조회 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY

• DATEDIFF 출력값은  `1일`임 ( 시작 지점 포함 안함)