[MySQL] WITH문 : 임시테이블 만들기 / COALESCE함수

(※개인 공부 목적의 게시물입니다.)

---

프로그래머스 > SQL > String, Date > 자동차 대여 기록 별 대여 금액 구하기

-> 문제 바로가기

---

<문제설명>

다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 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를 기준으로 내림차순 정렬해주세요.

※ FEE의 경우 예시처럼 정수부분만 출력되어야 합니다.

---

<풀이코드>

WITH NEW_HISTORY AS(
    SELECT C.CAR_TYPE,
            C.DAILY_FEE,
            H.HISTORY_ID,
            DATEDIFF(H.END_DATE, H.START_DATE)+1 AS DIFF,
            CASE
            WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 7 THEN '7일 이상'
            END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_CAR C JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
        ON C.CAR_ID = H.CAR_ID
    WHERE C.CAR_TYPE = '트럭'
)

SELECT N.HISTORY_ID,
        ROUND(DAILY_FEE * DIFF * (1-COALESCE(D.DISCOUNT_RATE, 0)/100)) AS FEE      
#할인정책이 없는 경우도 고려해야하기 때문에 LEFT JOIN함
FROM NEW_HISTORY N LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
    ON N.CAR_TYPE = D.CAR_TYPE AND N.DURATION_TYPE = D.DURATION_TYPE
ORDER BY FEE DESC, N.HISTORY_ID DESC;

---

[WITH문 이란?]

CTE(Common Table Expression, 공통테이블 식) 이라고 불린다.
쉽게 말해 복잡한 쿼리를 작성할 때 임시결과 집합을 정의하여 마치 테이블 처럼 사용할 수 있게 해주는 기능이다.
WITH문 안에 정의한 메인쿼리를 여러번 참조할 수 있기 때문에 쿼리의 반복으로 인해 프로그램 성능이 느려지는 것을 해결한다. 또한 코드의 가독성을 높인다는 장점이 있다.

---

[WITH문의 기본구조]

WITH CTE이름 AS(
#반복할 서브쿼리 작성
  SELECT col1, col2,...
  FROM table1
)
#정의한 임시테이블을 일반테이블처럼 사용가능  
SELECT * 
FROM CTE이름; 

[다중 WITH문]

#여러개의 임시테이블을 한번에 정의할 수 있음
WITH A AS(...),
	B AS(...),
    C AS(...)
   ...
  
SELECT *
FROM A JOIN B

[계층형 쿼리(재귀 WITH문) : WITH RECURSIVE문]

#예시 - 1~5 숫자 만들기
WITH RECURSIVE NUMS AS(
	#초기값
   SELECT 1 AS n
   
   UNION ALL
   
   #반복부분
   SELECT n+1
   FROM NUMS
   WHERE n < 5
)
SELECT *
FROM NUMS;

#결과 - 1, 2, 3, 4, 5

※ WITH RECURSIVE문 주의사항

• 반드시 UNION ALL 사용(UNION 사용시 성능 느려짐, 결과 달라질 수 있음)
• 종료조건 필수(없으면 무한루프)
• 컬럼개수/타입 동일해야함

---

• WITH NEW_HISTORY AS(
      SELECT C.CAR_TYPE,
              C.DAILY_FEE,
              H.HISTORY_ID,
              DATEDIFF(H.END_DATE, H.START_DATE)+1 AS DIFF,
              CASE
              WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 90 THEN '90일 이상'
              WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 30 THEN '30일 이상'
              WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 7 THEN '7일 이상'
              END AS DURATION_TYPE
      FROM CAR_RENTAL_COMPANY_CAR C JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
          ON C.CAR_ID = H.CAR_ID
      WHERE C.CAR_TYPE = '트럭'
  )

  • 필요한 컬럼만 담은 임시테이블 NEW_HISTORY 생성
  • 대여일수(DIFF) = DATEDIFF(H.END_DATE, H.START_DATE)+1
  • 자동차 종류가 '트럭' 인 자동차의 대여기록을 알기 위해서 WHERE조건문 추가

• CASE
  #WHEN (DATEDIFF(H.END_DATE, H.START_DATE)+1) >= 7 AND (DATEDIFF(H.END_DATE, H.START_DATE)+1) < 30 
  #THEN '7일 이상'
  #WHEN (DATEDIFF(H.END_DATE, H.START_DATE)+1) >= 30 AND (DATEDIFF(H.END_DATE, H.START_DATE)+1) < 90
  #THEN '30일 이상'
  #WHEN (DATEDIFF(H.END_DATE, H.START_DATE)+1) >= 90
  #THEN '90일 이상'
  WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 90 THEN '90일 이상'
  WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 30 THEN '30일 이상'
  WHEN DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 7 THEN '7일 이상'
  END AS DURATION_TYPE

  • CASE 조건문은 위에서부터 순서대로 조건을 검사하고, 처음 만족하는 것만 실행됨(위 조건에서 만족했으면 거기서 끝내고, 아니면 순서대로 내려오면서 조건을 검사)

  • 가장 큰 범위부터 작성해야 순서대로 걸러짐

      #이건 안됨xxx
     CASE
      WHEN RENT_DATE >= 7 THEN '7일 이상'
      WHEN RENT_DATE >= 30 THEN '30일 이상'
      WHEN RENT_DATE >= 90 THEN '90일 이상'
    END

  • 따라서 (DATEDIFF(H.END_DATE, H.START_DATE)+1) >= 7 AND (DATEDIFF(H.END_DATE, H.START_DATE)+1) < 30 이렇게 말고, DATEDIFF(HISTORY.END_DATE, HISTORY.START_DATE) + 1 >= 7 이렇게만 적어도 됨!
    
    

• SELECT N.HISTORY_ID,
         ROUND(DAILY_FEE * DIFF * (1-COALESCE(D.DISCOUNT_RATE, 0)/100)) AS FEE  

  • 대여금액 계산식
    = DAILY_FEE * DIFF * (1-COALESCE(D.DISCOUNT_RATE, 0)/100)

  • 정수부분만 출력하기 위해 ROUND()를 씌워줌

  • 할인정책이 없는 경우를 위해 COALESCE함수 사용하기
    -> D.DISCOUNT_RATE가 NULL이면 0을 반환

    ---

    [COALESCE 함수]

  • 입력된 값중 NULL이 아닌 첫번째 값을 반환하는 함수

  • 모든 인자가 NULL이면 NULL을 반환

  • 주로 SELECT문에서 데이터가 NULL일 때 대체값을 지정하거나 NULL값을 처리할 때 사용

  • 2개 이상의 인수를 받을 수 있음(<-> IFNULL()은 2개만 받을 수 있음)

    [기본구조]

    COALESCE(expr1, expr2, ...)

    -> expr1이 NULL이 아니면 expr1을 반환하고, NULL이면 expr2를 반환
    -> expr1, expr2가 모두 NULL이면 다음 인수로 넘어가서 NULL이 아닌 값을 반환하고, 이 과정을 인수가 끝날 때까지 반복함

    ---

• FROM NEW_HISTORY N LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
      ON N.CAR_TYPE = D.CAR_TYPE AND N.DURATION_TYPE = D.DURATION_TYPE

  • NEW_HISTORY 테이블에는 할인정책이 없는(대여기간이 7일미만인) 행도 존재하지만, CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에는 할인정책이 있는 행만 존재함으로 할인정책이 없는 경우도 고려하기 위해 NEW_HISTORY 테이블을 기준으로 LEFT JOIN 함
  • 차 종류, 대여기간 종류를 기준으로 조인

---