CTE(Common Table Expression-공통 테이블 표현식) 사용법
SQL 70번) MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.
WITH cte_review_counts AS (
SELECT member_id, COUNT(*) AS review_cnt
FROM rest_review
GROUP BY member_id
),
cte_top_members AS (
SELECT member_id
FROM cte_review_counts
WHERE review_cnt = (SELECT MAX(review_cnt) FROM cte_review_counts)
)
SELECT
m.member_name,
r.review_text,
DATE_FORMAT(r.review_date, '%Y-%m-%d') AS review_date
FROM
rest_review r
JOIN
member_profile m ON r.member_id = m.member_id
WHERE
r.member_id IN (SELECT member_id FROM cte_top_members)
ORDER BY
r.review_date, r.review_text;
암시적 조인 (Comma Join)의 작동 방식
FROM table1, table2와 같이 콤마로 테이블(또는 CTE)을 나열하면, 이는 INNER JOIN을 명시적으로 사용하지 않고 조인을 수행하는 방식
이때 WHERE 절에 조인 조건을 명시하지 않으면 카테시안 곱 발생
➕ rank() + 서브쿼리 사용
select b.member_name, a.review_text,
date_format(a.review_date, '%Y-%m-%d') as review_date
from rest_review a
join (
select rank() over(order by count(m.member_id) desc) as ranking, m.member_id, m.member_name
from member_profile m join rest_review r on m.member_id=r.member_id
group by m.member_id) b
on a.member_id=b.member_id
where b.ranking=1
order by review_date, a.review_text
재귀(recursive) CTE
SQL 73번) ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
처음 시도
select hour(datetime) as hour,
count(distinct animal_id) as count
from animal_outs
group by 1
order by 1
ㅎㅎ 이렇게 쉬울리 없었고~ 0~23시에 입양이 발생하지 않았더라도 보여줘야했다 ㅎ
재귀 cte : 자기 자신을 계속 호출해서 결과를 누적하는 형태의 cte
with recursive timetable#임시테이블 이름(hour#칼럼명) as ( select 0 #시작값 union select hour+1 #recursive part: 반복확장 from timetable where hour<23 )select hour, count(a.animal_id) from timetable t left join animal_outs a on t.hour=hour(a.datetime) group by hour order by hour;
추가 궁금증... 시간 테이블 생성해서 조인하면 안되나?
like...
1. 시간 테이블 생성 create table hours (hour INT);2. 0~23 시간값 삽입 insert into hours (hour) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23);3. 입양 건수를 시간별로 조회 select h.hour, count(a.animal_id) as count from hours h left join animal_outs a on hour(a.datetime) = h.hour GROUP BY h.hour ORDER BY h.hour;
문래 튜터님의 답변🐣
시간 테이블을 생성하는 방식은 유지 관리가 어렵고 권장되지 않으며, 대부분의 DB는 CTE를 잘 지원하므로 CTE 방식이 가장 적절합니다. 추가로 JSON_TABLE 등을 활용한 방법도 있으나 일반적으로는 DB에서 최소한의 데이터만 추출하고, 시각화나 후처리는 Python이나 Excel에서 처리하는 것이 좋습니다. 결론적으로 이 문제는 CTE 방식이 가장 합리적입니다. 결론... 가능은 하나 추천하지는 않는 방식 ㅋㅋㅋ with구문 쓰기가 싫어서 생각해봤던건데 더 복잡해졌다! ㅎ
