[MYSQL] PROGRAMMERS_자동차 대여 기록 별 대여 금액 구하기

With History2 as (Select *,
            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 NULL
            end
            as DURATION_TYPE
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)
 
SELECT distinct HISTORY_ID as HISTORY_ID,
    CASE WHEN h.DURATION_TYPE IS NULL THEN FLOOR(DAILY_FEE*(Datediff(END_DATE,START_DATE)+1))
    else FLOOR(DAILY_FEE*(Datediff(END_DATE,START_DATE)+1)*(1-DISCOUNT_RATE/100))
    end as FEE

FROM (History2 as h inner join CAR_RENTAL_COMPANY_CAR as c USING(CAR_ID)) inner join CAR_RENTAL_COMPANY_DISCOUNT_PLAN as p USING (CAR_TYPE)
WHERE c.CAR_TYPE = "트럭"
AND (h.DURATION_TYPE= p.DURATION_TYPE OR h.DURATION_TYPE IS NULL)
ORDER BY 2 desc , 1 desc

문제 

 CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬

 

진짜 빡셌습니다... 하나를 고치니 다른 하나가 문제고... 다음번에 좀더 쿼리를 정리해서 깔끔한 쿼리로 추가해서 해설남기겠습니다.

 

이번 문제는 한번에 여러개를 고민해야할 문제가 많습니다. 일단 문제를 풀다보면 처리 해야할게 계속나오는 문제다 보니 먼저 아래와 같이 그림을 그려서 처리를 하는것을 추천합니다.

ERD도식과 상관없이 보기 편하게 필요한 부분만 그려봤습니다. 먼저 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블 부터 처리해 주어야 합니다. 해당 테이블에 START_DATE과 END_DATE이 담겨있는데 이를 통해 자동차 대여 기간을 구할 수 있습니다. 이값을 가지고 이후에 Discount_TYPE을 비교해 DISCOUNT_RATE를 구해야 하므로 해당 값이 테이블에 기입 될 수 있도록합니다.

 

그러려면 테이블 자체를 수정 할 수는 없으므로 With절로 임시 새테이블을 만들어줍니다. 저는 이것을 History2 라고 Alias 명을 적어 주었습니다. 여기서 날짜만 구하기보단 바로 DURATION_TYPE과 비교 가능하게 형태도 같이 바꾸어서 만들어 줍니다. 조건에 따라 값을 설정하는 구문은 CASE WHEN 구문이므로 해당 구문으로 작성해 줍니다.

 

 주어진 조건에는 90이상, 30일이상, 7일이상 , 그외 7일 미만 의 조건이 있습니다. 이런 조건문의 경우에는 맨 앞에 쓰인 조건 문을 기준으로 순서대로 처리가 되기때문에 만약 7일이상 30일이상 90일 이상 순으로 하면 30일이상 90일 이상 데이터 조차 7일 이상으로 모조리  처리되어 버리는 불상사가 발생하니 유의 바랍니다.

With History2 as (Select *,
            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 NULL
            end
            as DURATION_TYPE
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)

 DateDiff 함수의 경우에는 날짜의 숫자끼리 빼는 함수라 당일은 포함이 되지 않습니다. 총 기간을 구하는경우 항상 +1을 추가해줍니다. 이 함수로 기간을 구해서 DURATION_TYPE과 비교할 값을 만들어 주는데 이때 7일 미만은 NULL값으로 설정해 둡니다. 그 후  CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에 존재하는 모든 데이터와 함께 쿼리를 만들어 줍니다.

 

 다음으로 처리해야 할 부분은 "트럭" 조건입니다. 순서가 그런이유는 CAR_RENTAL_COMPANY_RENTAL_HISTORY과 직접 연결되어 처리가 가능 한 테이블 이었기 때문입니다.(이제는 HISTORY2 로 대체된..) 여기서 필터 처리를 한번 해준 뒤 다음 테이블을 처리해야 효율적입니다. (그다음 테이블을 LEFT JOIN 처리 해야하기 때문입니다.)

FROM History2 as h inner join CAR_RENTAL_COMPANY_CAR as c USING(CAR_ID)
WHERE c.CAR_TYPE = "트럭"

 

 여기서는 별 크게 문제없이 CAR_TYPE이 트럭인 대상만 남아야 하기때문에 공통으로 겹치는 데이터만 남아야 합니다. 따라서 INNER JOIN으로 처리해 줍니다. 그러면 이외의 운송수단은 모두 걸러집니다. INNER JOIN을 한뒤 메인 쿼리에서 WHERE 조건에 "트럭" 을 추가해줍니다. (참고로 USING의 경우에는 양쪽 테이블의 컬럼명이 같아야지만 사용이 가능합니다.) 그러면 History2에 있던 데이터들은 이제 운송 수단이 트럭인 애들만 남게 됩니다.

 

FROM (History2 as h inner join CAR_RENTAL_COMPANY_CAR as c USING(CAR_ID)) inner join CAR_RENTAL_COMPANY_DISCOUNT_PLAN as p USING (CAR_TYPE)
WHERE c.CAR_TYPE = "트럭"
AND (h.DURATION_TYPE= p.DURATION_TYPE OR h.DURATION_TYPE IS NULL)

 

 

다음 조인으로 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을  (CAR_TYPE) 기준으로 Inner JOIN 해 주었습니다. 이렇게되면 3가지 쿼리가 모두 겹치는 대상에 대한 값이 나오게 됩니다. CAR_RENTAL_COMPANY_DISCOUNT_PLAN는 자동차 TYPE을 기준으로 조인이 되는데 해당 컬럼은 자동차 타입별로 3가지의 값( 7일 이상 30일이상 90일 이상) 이 존재하므로 다른 조건이 존재하지 않는 이상 앞의 컬럼과 3가지씩 매칭이 됩니다. 

 

 따라서 이 조건을 처리해 주어야 하는데 이 조건을 처리 하기위해서 앞서 조인된 트럭 조건만 남은 HISTORY2  테이블에서 DURATION_TYPE 조건을 비교해 줍니다. With 절에서 날짜 데이터를 비교 해서 이미 변환 처리해 DURATION_TYPE에 맞게 해 주었으므로 해당 값과 일치 하는 값이 있으면 맞게 연결해 줍니다.(h.DURATION_TYPE= p.DURATION_TYPE) 그러면 (트럭) - (7일,30일,90일이상) - 날짜에 맞는 DISCOUNT_RATE이 매칭이 되게 됩니다.

 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 NULL <---

 

AND (h.DURATION_TYPE= p.DURATION_TYPE 
     OR h.DURATION_TYPE IS NULL)

 그런데 문제는 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에는 7일 미만의 데이터가 NULL로 없습니다. 따라서 해당 데이터는 HISTORY2 테이블의 DURATION_TYPE과 매칭이 되지않아 그대로 7일 30일이상 90일 이상  데이터가 붕뜨게 됩니다. 이때 매칭이 되지 않은 녀석들은 마찬 가지로 NULL로 처리되게 괄호 를 쳐서 OR h.DURATION_TYPE IS NULL로 처리해 줍니다. (괄호를 해주지 않으면 메인쿼리 전체에 대한 OR로 처리가 되어.. 괄호를 쳐서 해주어야 합니다. 2개의 조건중  선택해야 하니까요)

 

 여기까지하면 (트럭) - (7일,30일,90일이상) - 날짜에 맞는 DISCOUNT_RATE/그외 날짜=NULL 로 처리되었습니다. 이제 메인쿼리의 SELECT 문을 처리해야합니다.

 

SELECT distinct HISTORY_ID as HISTORY_ID,
    CASE WHEN h.DURATION_TYPE IS NULL THEN FLOOR(DAILY_FEE*(Datediff(END_DATE,START_DATE)+1))
    else FLOOR(DAILY_FEE*(Datediff(END_DATE,START_DATE)+1)*(1-DISCOUNT_RATE/100))
    end as FEE

 

일단 DURATION TYPE이 NULL인 애들과 NULL이 아닌 애들이 있는데 이둘의 계산을 구분해 주기위해 CASE로 나눕니다. (이유는 NULL인 애들은 DISCOUNT_RATE 값 자체가 없으므로 임으로 조건에 맞게 설정해 주어야 합니다. 그래서 NULL이 아닌애들은 기존의 DURATION_TYPE에 맞는 DISCOUNT_RATE로 계산되게 그리고 NULL인 애들은 DISCOUNT_RATE가 필요없기에 없는 계산식이 적용되게 설정 하고 Alias를 설정해 주었습니다. 그리고 정수까지 표현 하라 하였으므로 FLOOR 함수를 사용하여 내림 해주는데 Default 값은 0 이므로 그대로 사용하면 됩니다.

 

ORDER BY 2 desc , 1 desc

그후 마무리로 정렬 까지 해주시면 끝납니다..

 

문제푸는데 거의 일하는 기분이었습니다.. 익숙하지가 않으니 조금 고생이네요...

좀더 실력을 길러서 깔끔한 쿼리를 짜보겠습니다..