[프로그래머스] 자동차 대여 기록 별 대여 금액 구하기 - CASE WHEN

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

프로그래머스

WITH B AS (SELECT HISTORY_ID
                 , CAR_ID
                 , DATEDIFF(END_DATE,START_DATE)+1 AS DURATION
                 , (CASE WHEN DATEDIFF(END_DATE,START_DATE)+1<7 
                    THEN '7일 미만'
                    WHEN DATEDIFF(END_DATE,START_DATE)+1>=7 
                    THEN '7일 이상'
                    WHEN DATEDIFF(END_DATE,START_DATE)+1>=30 
                    THEN '30일 이상'
                    WHEN DATEDIFF(END_DATE,START_DATE)+1>=90
                    THEN '90일 이상'
                    END) AS DURATION_TYPE        
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)
            
            
SELECT B.HISTORY_ID
     , ROUND(A.DAILY_FEE * B.DURATION * 
             (100 - IFNULL(C.DISCOUNT_RATE,0)) * 0.01) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS A
    INNER JOIN B ON A.CAR_ID = B.CAR_ID
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS C
    ON A.CAR_TYPE = C.CAR_TYPE
    AND B.DURATION_TYPE = C.DURATION_TYPE
WHERE C.CAR_TYPE = '트럭'

ORDER BY FEE DESC, HISTORY_ID DESC;

 

1차 제출 - 실패

 CASE 문을 이용해 구간에 따라 값을 하는 경우, 높은 숫자부터 순서대로 처리해주어야 한다. 그렇지 않으면 두 번째 조건식에서 7 이상에 해당하는 행들이 모두 '7일 이상'으로 처리되어 그 다음 조건식을 비교하지 않게 되기 때문이다.

 

WITH B AS (SELECT *
                 , DATEDIFF(END_DATE,START_DATE)+1 AS 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일 이상'
                    ELSE '7일 미만'
                    END) AS DURATION_TYPE        
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)
            
            
SELECT B.HISTORY_ID
     , ROUND(A.DAILY_FEE * B.DURATION * 
             (100 - IFNULL(C.DISCOUNT_RATE,0)) * 0.01) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR AS A
    INNER JOIN B ON A.CAR_ID = B.CAR_ID
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS C
    ON A.CAR_TYPE = C.CAR_TYPE
    AND B.DURATION_TYPE = C.DURATION_TYPE
WHERE A.CAR_TYPE = '트럭'

ORDER BY FEE DESC, HISTORY_ID DESC;

 

2차 제출 - 실패

결과 값이 같아 무슨 문제가 있나 싶었지만, 정답은 WHERE 절이 WITH 서브쿼리에 포함되어야 했다. 그렇지 않으면 서브쿼리와 조인을 할 때 필요하지 않은 값까지 조인을 하여 쿼리의 성능이 저하되기 때문에 정답으로 인정하지 않는 것이라 추측한다.

 

WITH SUB AS (
SELECT A.CAR_ID
     , A.CAR_TYPE
     , A.DAILY_FEE
     , B.HISTORY_ID
     , DATEDIFF(END_DATE, START_DATE)+1 AS 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일 이상'
             ELSE '7일 미만' END) AS DURATION_TYPE
FROM CAR_RENTAL_COMPANY_CAR AS A
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS B
	ON A.CAR_ID = B.CAR_ID
WHERE A.CAR_TYPE = '트럭')

SELECT SUB.HISTORY_ID
     , ROUND(SUB.DAILY_FEE * SUB.DURATION * (100 - IFNULL(C.DISCOUNT_RATE,0)) / 100) AS FEE
FROM SUB
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS C
	ON SUB.CAR_TYPE = C.CAR_TYPE
	AND SUB.DURATION_TYPE = C.DURATION_TYPE

ORDER BY FEE DESC, SUB.HISTORY_ID DESC;

 

3차 제출 - 성공

개발자들이 짜는 SQL 쿼리의 경우 성능을 반드시 고려하여야 한다는 것을 알고 있었으나, '데이터 분석을 위한 SQL 쿼리 또한 성능을 고려해야 하는구나.' 라는 생각을 하게 되었다. 그걸 여태 고려하지 않았던 것은 내가 아직까지 SQL에서 큼직한 데이터를 처리해보지 않았기 때문이라고 생각한다. 쿼리의 성능을 위해 공부해야 할 내용이 뭘까에 대해 고민해보면 좋을 것 같다.

 

쿼리를 짜며 배운 내용

 

1. DUPLICATE COLUMN NAME

테이블 간 조인을 한 뒤에 SELECT * 을 사용하는 경우, 키로 사용하였던 컬럼이 중복하여 불러들여진다. 여기까지는 문제가 없지만, 조인한 테이블을 서브쿼리로 사용하는 경우, 해당 에러가 발생한다. 해결 방법은 서브쿼리 내에서 키로 사용하였던 컬럼을 하나만 불러들이는 방법과 각 컬럼에 ALIAS를 붙여 각 컬럼의 이름을 다르게 지정해주는 방법이 있다.

 

 

2. SEARCHED CASE EXPRESSION vs SIMPLE CASE EXPRESSION

 

2-1. SEARCHED CASE EXPRESSION 의 예시

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일 이상'
ELSE '7일 미만'
END

 

searched case expression 은 <, >, >=, <> 와 같은 condition을 이용하여 결과값을 얻고자 하는 경우에 사용한다.

 

2-2. SIMPLE CASE EXPRESSION 의 예시

CASE LEFT(STUDENT_NAME,1)
WHEN '정' THEN '1반'
WHEN '김' THEN '2반'
WHEN '박' THEN '3반'
ELSE '4반'
END

 

상상력이 부족하여 학생의 성이 '정'씨 이면 '1반'에 배정하고 ... 그렇지 않으면 모두 4반에 배정하고자 하는 사례를 예시로 들어보았다. simple case expression 은 위와 같이 expression을 이용하여 결과값을 얻고자 하는 경우에 사용한다.