WEEK 3 TUES

1.서브쿼리에 조건주기, with 이용, table 함수 사용, where에 in 중첩

오늘은 이 문제로 헤매었다. 월별, 자동차 아이디별 총 대여기록을 구하는 것인데, 8월에서 10월사이에 5번 이상 대여기록이 있는 차만 월별 대여횟수를 구하는 것이다. 먼저 서브쿼리고 8월에서 10월사이 5번 이상의 대여기록이 있는지 확인을 해야했다. 그러고 5번 이상인 ID와 원본 데이터와 조인 시켜서 해당 ID를 가진 자동차 기록만 불러온다. 이렇게 되면 다시 새로운 레코드이므로 8월에서 10월의 데이터를 다시 지정해주어야 한다. 이 후, 월별 아이디별 기록을 세어준다.

JOIN시 어떤 데이터가 도출되는지 계속 확인을 하거나, 생각을 해가면서 데이터를 정리하는 것이 필요하다.

SELECT MONTH(start_date) AS 'MONTH', h.car_id, COUNT(h.car_id) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h right join (
    SELECT CAR_ID, HISTORY_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE DATE_FORMAT(start_date,'%Y-%m') BETWEEN '2022-08' AND '2022-10'
    GROUP BY CAR_ID
    HAVING COUNT(CAR_ID) >= 5
    ) a on h.CAR_ID=a.CAR_ID
WHERE DATE_FORMAT(start_date,'%Y-%m') BETWEEN '2022-08' AND '2022-10'
GROUP BY 1,2
ORDER BY 1, 2 DESC

 

같은 문제를 with 을 사용하여 풀어보았다. 위와 같이 길고 복잡한 수식이 짧게 정리된다는게 큰 장점으로 보인다. 두 테이블로 쪼개어 생각하는 부분이 어려웠고, where 에 조건을 쓸 때, car_id를 with절에서 명명한 5up을 TABLE 함수를 통해 가져올 수 있는 것을 처음 알았다. 

테이블 함수는 공통 테이블 표현식에서 다른 테이블 참조와 서로 관련되는 SQL 데이터를 수정하는 SQL 테이블 함수의 호출하는데 여기 식에서는 수정이 아니라 불러오기 위해 사용되었다.

-- with
with 5up as (select car_id
            from CAR_RENTAL_COMPANY_RENTAL_HISTORY
            where year(start_date) = 2022 and month(start_date) between 8 and 10
            group by car_id 
            having count(car_id) >= 5)
    , rent as (select month(start_date) month, car_id, count(car_id) records
               from CAR_RENTAL_COMPANY_RENTAL_HISTORY
               where year(start_date) = 2022 and month(start_date) between 8 and 10
               group by month, car_id)

select *
from rent
where car_id in (table 5up)
order by 1, 2 desc

 

또, where에 서브쿼리를 넣는것도 어렵고, with을 사용하는 것도 어려웠는데 둘의 문장이 같다는 것을 알게 되었다. 

-- with
with rent as (select month(start_date) month, car_id, count(car_id) records
               from CAR_RENTAL_COMPANY_RENTAL_HISTORY
               where year(start_date) = 2022 and month(start_date) between 8 and 10
               group by month, car_id)

select *
from rent
where car_id in (select car_id
            from CAR_RENTAL_COMPANY_RENTAL_HISTORY
            where year(start_date) = 2022 and month(start_date) between 8 and 10
            group by car_id 
            having count(car_id) >= 5)
order by 1, 2 desc

 

위의 풀이를 응용해서 더 복잡한 문제도 해결할 수 있었다.

WITH REVIEW_RANK AS
    (SELECT a.MEMBER_ID, CNT, RANK() OVER(ORDER BY a.CNT DESC) RANKING
    FROM(
        SELECT MEMBER_ID, COUNT(MEMBER_ID) CNT
        FROM REST_REVIEW
        GROUP BY MEMBER_ID) a)
        
    , REVIEW_RANK_1_RECORD AS
    (SELECT r.MEMBER_ID, r.REVIEW_TEXT, DATE_FORMAT(r.REVIEW_DATE,'%Y-%m-%d') REVIEW_DATE
     FROM (SELECT MEMBER_ID
           FROM REVIEW_RANK
           WHERE RANKING = 1) b 
           JOIN REST_REVIEW r ON b.MEMBER_ID= r.MEMBER_ID)

select MEMBER_NAME, REVIEW_TEXT, REVIEW_DATE
FROM REVIEW_RANK_1_RECORD r1, MEMBER_PROFILE p 
where r1.MEMBER_ID=p.MEMBER_ID
ORDER BY 3,2

 

하지만 WHERE에 IN 을 중첩하여 사용한 쿼리도 가능하다.

WITH COUNT_R AS (SELECT MEMBER_ID, COUNT(MEMBER_ID) REVIEW_CNT
                 FROM REST_REVIEW
                 GROUP BY MEMBER_ID)
            
SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') REVIEW_DATE
    FROM REST_REVIEW LEFT JOIN MEMBER_PROFILE USING(MEMBER_ID)
    WHERE MEMBER_ID IN (SELECT MEMBER_ID FROM COUNT_R
                        WHERE REVIEW_CNT IN (SELECT MAX(REVIEW_CNT) FROM COUNT_R))
    ORDER BY REVIEW_DATE, REVIEW_TEXT;