SQL null의 처리

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

프로그래머스

 

 

# # -- 코드를 입력하세요
WITH LABELED_HISTORY AS (
    SELECT (DATEDIFF(END_DATE, START_DATE) + 1) AS DURATION, 
    HISTORY_ID, h.CAR_ID, DAILY_FEE, CAR_TYPE,
    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 h
    INNER JOIN CAR_RENTAL_COMPANY_CAR c
    ON c.CAR_ID = h.CAR_ID
)

SELECT lh.HISTORY_ID, 
ROUND(lh.DURATION * lh.DAILY_FEE * (1 - IFNULL(p.DISCOUNT_RATE, 0) / 100), 0) AS FEE
FROM LABELED_HISTORY lh
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p
ON lh.DURATION_TYPE = p.DURATION_TYPE AND lh.CAR_TYPE = p.CAR_TYPE
WHERE lh.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC
;

-- 틀린 코드
# WITH LABELED_HISTORY AS (
#     SELECT 
#         (DATEDIFF(END_DATE, START_DATE) + 1) AS DURATION, 
#         h.HISTORY_ID, 
#         h.CAR_ID, 
#         c.DAILY_FEE, 
#         c.CAR_TYPE,
#         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 h
#     INNER JOIN CAR_RENTAL_COMPANY_CAR c ON c.CAR_ID = h.CAR_ID
# )
# SELECT 
#     lh.HISTORY_ID, 
#     ROUND(lh.DURATION * lh.DAILY_FEE * (1 - p.DISCOUNT_RATE/100), 0) AS FEE
# FROM LABELED_HISTORY lh
# INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p 
#     ON lh.DURATION_TYPE = p.DURATION_TYPE 
#     AND lh.CAR_TYPE = p.CAR_TYPE
# WHERE lh.CAR_TYPE = '트럭'
# ORDER BY FEE DESC, HISTORY_ID DESC;

 

 

 

 

문제의 핵심은 INNER JOIN과 LEFT JOIN의 차이에 있습니다. INNER JOIN을 사용하면 두 테이블의 매칭되는 데이터만 가져오기 때문에, 만약 DISCOUNT_PLAN에 매칭되는 할인 정보가 없을 경우 해당 대여 기록은 결과에 포함되지 않습니다. 반면, LEFT JOIN을 사용하면 DISCOUNT_PLAN에 매칭되는 항목이 없더라도 대여 기록을 가져올 수 있으며, 그 경우 할인율을 NULL로 처리할 수 있습니다.

왜 LEFT JOIN을 사용해야 했는지:

1. 할인 없는 대여 기록 처리: CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 모든 DURATION_TYPE에 대한 할인 정책이 존재하지 않을 수 있습니다. 예를 들어, 1일짜리 대여 기록에 대해서는 할인 정책이 없을 수 있습니다. 이 경우 INNER JOIN을 사용하면 해당 기록이 아예 결과에서 빠지게 됩니다. 하지만 LEFT JOIN을 사용하면 할인 정보가 없는 경우에도 해당 대여 기록을 결과에 포함시킬 수 있습니다.
2. IFNULL 처리: LEFT JOIN을 사용하면 DISCOUNT_PLAN에 매칭되는 데이터가 없을 때 할인율(DISCOUNT_RATE)이 NULL이 됩니다. 이를 방지하기 위해 IFNULL 함수를 사용하여 할인율이 NULL일 경우 0으로 처리합니다. 즉, 할인 정책이 없으면 할인율이 0%가 적용되는 셈입니다.

코드 비교:

잘못된 INNER JOIN 사용

sql

코드 복사

INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p ON lh.DURATION_TYPE = p.DURATION_TYPE AND lh.CAR_TYPE = p.CAR_TYPE

• 이 경우, 할인 정책이 없는 대여 기록은 결과에 포함되지 않습니다.
• 예를 들어, 대여 기간이 1일인 경우에 대한 할인 정보가 없다면 해당 대여 기록은 결과에서 제외됩니다.

수정된 LEFT JOIN과 IFNULL 처리

sql

코드 복사

LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p ON lh.DURATION_TYPE = p.DURATION_TYPE AND lh.CAR_TYPE = p.CAR_TYPE

• LEFT JOIN을 사용하면 DISCOUNT_PLAN에 매칭되지 않는 데이터도 포함됩니다.
• 그리고 IFNULL(p.DISCOUNT_RATE, 0)를 사용하여 할인율이 NULL일 경우 0%로 처리합니다.

최종 쿼리:

sql

코드 복사

WITH LABELED_HISTORY AS ( SELECT h.HISTORY_ID, h.CAR_ID, c.CAR_TYPE, c.DAILY_FEE, (DATEDIFF(h.END_DATE, h.START_DATE) + 1) AS DURATION, -- 대여 기간에 따른 DURATION_TYPE 라벨링 CASE WHEN (DATEDIFF(h.END_DATE, h.START_DATE) + 1) >= 90 THEN '90일 이상' WHEN (DATEDIFF(h.END_DATE, h.START_DATE) + 1) >= 30 THEN '30일 이상' WHEN (DATEDIFF(h.END_DATE, h.START_DATE) + 1) >= 7 THEN '7일 이상' ELSE '1일 이상' END AS DURATION_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h INNER JOIN CAR_RENTAL_COMPANY_CAR c ON h.CAR_ID = c.CAR_ID WHERE c.CAR_TYPE = '트럭' -- '트럭'인 자동차만 필터링 ) SELECT lh.HISTORY_ID, -- 할인율이 없으면 기본적으로 0% 적용 ROUND(lh.DURATION * lh.DAILY_FEE * (1 - IFNULL(p.DISCOUNT_RATE, 0) / 100), 0) AS FEE FROM LABELED_HISTORY lh LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p ON lh.DURATION_TYPE = p.DURATION_TYPE AND lh.CAR_TYPE = p.CAR_TYPE ORDER BY FEE DESC, lh.HISTORY_ID DESC;

설명:

1. LABELED_HISTORY:
   • 대여 기간을 계산하고, 해당 기간을 기준으로 DURATION_TYPE을 설정합니다.
   • 이 단계에서는 INNER JOIN을 사용하여 자동차 정보를 가져오고, CAR_TYPE이 '트럭'인 대여 기록만 필터링합니다.
2. 할인율 적용:
   • LEFT JOIN을 사용하여 대여 기간과 자동차 종류에 맞는 할인 정책을 조인합니다. 할인 정책이 없는 경우에도 대여 기록을 결과에 포함시키기 위해 LEFT JOIN을 사용했습니다.
   • 할인율이 NULL일 때는 IFNULL(p.DISCOUNT_RATE, 0)를 사용하여 기본적으로 할인율을 0%로 처리합니다.
3. 정렬:
   • 계산된 대여 금액(FEE)을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 HISTORY_ID로 추가적인 내림차순 정렬을 합니다.

결론:

• 문제의 핵심은 INNER JOIN이 아닌 LEFT JOIN을 사용하여 할인 정보가 없는 대여 기록도 포함시키고, 그 경우 할인율을 0%로 처리하는 것이었습니다.
• IFNULL을 사용한 것은 NULL 값을 0으로 바꿔서 할인율이 없는 경우에 대한 처리를 올바르게 해주기 위한 것입니다.