문제를 풀다보니,내가 SQL을 짤 때, 우선순위별로 짜다보니 서브쿼리를 사용하는 것을 즐겨하다보니, JOIN으로 풀수있는 것도 서브쿼리로 풀수 있다보니 문득 궁금해졌다.
JOIN으로 쿼리를 짜는게 좋을까? 서브쿼리로 쿼리를 짜는게 좋을까?
SQL이 아직 익숙하지 않아서 그런가 조인으로 쉽게 작성할 수 있는 쿼리문을 서브 쿼리로 작성해왔다. 서브 쿼리가 조인에 비해 직관적이라서 이해하기 쉽기 때문에 실제로 많은 SQL 사용자들이 선호한다고 하지만, 때로는 조인이 훨씬 효과적이라 쿼리문이 복잡해지더라도 서브 쿼리에 비해 읽어내기 수월하다. 그래서 가장 첫 번째로 서브 쿼리를 조인으로 대체할 수 있는 경우들을 살펴보고자 한다.
- 기본적으로 서브쿼리보다 조인을 활용했을때, 하드코딩(코드중복)되는 현상을 줄일 수 있다.
문제풀어보기
📌 문제 링크(작성중)
다른 예시
📌 문제 링크
- 내가 푼 정답
-- 자동차의 종류가 트럭(트럭 종류가 2개)
-- 대여금액 (FEE), 대여기록ID, 대여 금액()리스트
-- 내림차순 1순위 대여금액, 2순위 대여기록 ID
-- 트럭 분류 필요!, 대여일 계산 필요
-- 대여일에 따른 할인율 가져오기
SELECT HISTORY_ID, ROUND(DAILY_FEE * rent * discount / 100, 0) AS FEE
FROM (
SELECT HISTORY_ID, DAILY_FEE , DATEDIFF(DATE_ADD(END_DATE, INTERVAL 1 DAY), START_DATE) AS rent,
CASE
WHEN DATEDIFF(DATE_ADD(END_DATE, INTERVAL 1 DAY), START_DATE) >= 90 THEN 100 - CAST((SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE= '90일 이상' AND CAR_TYPE = '트럭') AS UNSIGNED)
WHEN DATEDIFF(DATE_ADD(END_DATE, INTERVAL 1 DAY), START_DATE) >= 30 THEN 100 - CAST((SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE= '30일 이상' AND CAR_TYPE = '트럭') AS UNSIGNED)
WHEN DATEDIFF(DATE_ADD(END_DATE, INTERVAL 1 DAY), START_DATE) >= 7 THEN 100 - CAST((SELECT DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE DURATION_TYPE= '7일 이상' AND CAR_TYPE = '트럭') AS UNSIGNED)
ELSE 100
END AS discount
FROM CAR_RENTAL_COMPANY_CAR car
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY history
ON car.CAR_ID = history.CAR_ID
where CAR_TYPE = '트럭') as t
ORDER BY FEE DESC, HISTORY_ID DESC;
- 위 보면 CASE문이 길어지고, 비슷한 문법이 계속해서 반복되는 하드코딩으로 푼 문제이다.
- 확실히 Join에 대한 숙련도가 부족하고, 분할생각하기 좋은 서브쿼리를 활용해서 풀게 되었다.
최적화 하기
- 위 SQL에서
서브 쿼리와조건문이 중첩되어 있어 복잡성이 증가하고 있다. - 이를 최적화하는 가장 효과적인 방법 중 하나는 '조인'을 통해 필요한 데이터를 미리 추출하고, 이를 기반으로 계산을 수행
SELECT
h.HISTORY_ID,
ROUND(c.DAILY_FEE * h.RENT_DAYS * (100 - IFNULL(d.DISCOUNT_RATE, 0)) / 100, 0) AS FEE
FROM
(SELECT
*,
DATEDIFF(DATE_ADD(END_DATE, INTERVAL 1 DAY), START_DATE) as RENT_DAYS
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY) h
JOIN
CAR_RENTAL_COMPANY_CAR c ON c.CAR_ID = h.CAR_ID
LEFT JOIN
(SELECT
*,
CASE
WHEN DURATION_TYPE = '90일 이상' THEN 90
WHEN DURATION_TYPE = '30일 이상' THEN 30
WHEN DURATION_TYPE = '7일 이상' THEN 7
ELSE 0
END AS DURATION_DAYS
FROM
CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE
CAR_TYPE = '트럭'
) d ON h.RENT_DAYS >= d.DURATION_DAYS
WHERE
c.CAR_TYPE = '트럭'
GROUP BY
h.HISTORY_ID
ORDER BY
FEE DESC,
h.HISTORY_ID DESC;
-
3중 JOIN으로 해결하였다. (하지만, 서브쿼리도 필수로 들어갈 수 밖에..없었다.)
코드 해석은 아래와 같다. -
먼저, 대여기간을 측정하기위해, 가장먼저 서브쿼리로 계산하였다.
RENT_DAYS: 대여기간 ,h: 히스토리 테이블
-
그리고 JOIN 부분을 보면 차정보를 가지고있는 CAR와 대여기록을 가지고 있는 HISTORY 테이블을 CAR_ID 기준으로 조인했다.(이부분은 다들 생각할 수 있다.)
-
그리고, LEFTJOIN 부분을 보면, 할인율부분을 보다 쉽게 접근할 수 있기 위해,
90일 동안->90과 같이 변경하였고, 여기서 조건(WHERE)에트럭으로 필터를 해준 상태로 LEFT조인을 해주었다.
- 여기서 LEFT조인을 하는 이유는 할인율이7일미만이 DISCOUNT_PLAN 테이블에는 표기되어 있지 않기 때문이다!★★★
- 그래서, LEFT조인으로 할인율이 없는 히스토리 기록이 있으면 NULL로 들어가게 된다.!! (그래서 나중에 ifNull()함수를 사용하게 됨.)- LEFT 조인 조건은 대여기간으로 잡아준다.
ON h.RENT_DAYS >= d.DURATION_DAYS
- ★★★이부분으로 인해 여러번 반복되는 하드코딩을 지울 수 있다.!!★★★
- 게다가 이미, 위에서 서브쿼리로 RENT_DAYS도 만들어놔서 활용가능!
- 단, DURATION_DAYS보다 크거나 같으면 이라는 조건때문에,대여기간이 90일이 넘는 경우 모든 곳에 해당하게된다.(즉, 중복이 생김)
[아래는 중복이 생긴 데이터 이미지]
- 그래서,
GROUP BY h.HISTORY_ID로 중복을 제거해준다.
- LEFT 조인 조건은 대여기간으로 잡아준다.
-
그리고
ROUND(c.DAILY_FEE * h.RENT_DAYS * (100 - IFNULL(d.DISCOUNT_RATE, 0)) / 100, 0) AS FEE으로 최종 대여금액을 계산해주고, 집계함수 ROUND로 인해 할인값이 높은 값만 정수로 추출할 수 있으며, IFNULL로 인해 대여기간이 7일 보다작은 값도 계산할 수가 있다.-
여기서 가장 의문인건 집계함수 ROUND로 인해 할인율이 높은게 계산되어 SELECT된 것이다.
-
그래서 이번엔 GROUP BY, ORDER BY 없이 순수하게 출력을 시켜보니
-
history_id 순으로 출력되었다. 아마, history_id가 순차적으로 늘어나는 형태여서 가능한것 같고, history_id반복된들 할인율 높은 row가 먼저 나오기때문에, 중복이 제거되어도 할인율 높은걸로 계산이 되는 것이다.
- (GROUP BY를 통해 중복을 제거한다면, SQL은 기본적으로 그룹의 첫 번째 행을 반환하는것을 알게됨!!★)
-
이로써, 왠만해서는 with()함수[나중에 한번 정리할 함수]를 사용해서 할인되어 나오는 최소금액이 나오게 하는게 좋을 듯하다.
-
