쿼리가 스파게티가 됐다면? 복잡한 로직을 레고처럼 조립하는 CTE와 서브쿼리 (SQL 독학 #12)

지금까지는 JOIN을 이용해 테이블을 연결하거나, GROUP BY로 통계를 내는 '단편적인' 기술들을 배웠다.
하지만 현업의 비즈니스 질문은 그리 단순하지 않다.

"평균 상영 시간보다 긴 영화만 찾아줘."
"매출 상위 5개 장르의 총수익을 뽑아줘."

이런 질문을 받으면 멘붕이 온다. 평균을 구하는 쿼리도 짜야 하고, 그걸 다시 필터링하는 쿼리도 짜야 하기 때문이다.
쿼리 안에 쿼리를 넣어야 하는 순간, 코드는 스파게티처럼 꼬이기 시작한다. 🤯

오늘은 이런 복잡한 로직을 깔끔하게 해결해 주는 서브쿼리(Subquery)와, 가독성의 끝판왕 CTE(WITH문)를 실습했다.

 

1. 쿼리 안에 쿼리 넣기 (서브쿼리)

가장 먼저 부딪힌 문제는 "평균 상영 시간보다 긴 영화 찾기"였다.
WHERE length > AVG(length)라고 쓰면 참 좋겠지만, SQL은 WHERE 절에서 집계 함수를 바로 쓸 수 없다.

그래서 "평균을 구하는 쿼리"를 먼저 만들고, 그걸 괄호 ( ) 안에 넣어 본문에 심어줘야 한다. 이걸 서브쿼리라고 부른다.

SELECT title, length
FROM film
WHERE length > (
    SELECT AVG(length) -- 115.27... 이라는 숫자가 됨
    FROM film
);

 

괄호 안의 쿼리가 먼저 실행되어 '평균값'을 내뱉고, 바깥 쿼리가 그 값을 받아서 실행된다.
마치 마트료시카 인형처럼 쿼리 안에 쿼리가 들어있는 구조다.

2. 코드를 레고처럼 조립하기 (CTE, WITH문) ⭐

서브쿼리도 좋지만, 괄호가 겹겹이 쌓이면 나중엔 내가 짠 코드도 알아보기 힘들다.
이때 사용하는 것이 CTE(Common Table Expression), 즉 WITH 문이다.

쉽게 말해 "임시 테이블에 이름을 붙여서, 레고 블록처럼 미리 만들어두는 것"이다.

WITH avg_film_length AS (
    SELECT AVG(length) AS avg_len
    FROM film
)
SELECT title, length
FROM film
WHERE length > (SELECT avg_len FROM avg_film_length);

avg_film_length라는 이름표를 붙여놓으니, 아래 본문 쿼리가 훨씬 직관적으로 변했다.
"이 쿼리는 평균 길이를 구하는 거구나" 하고 바로 이해할 수 있다.

3. 끝판왕 도전: 매출 Top 5 장르 찾기

CTE의 진가는 테이블을 여러 개 엮을 때 발휘된다.
"어떤 장르(Action, Comedy 등)가 돈을 가장 많이 벌었을까?"라는 질문을 해결하려면 무려 5개의 테이블을 JOIN 해야 한다.

이걸 한 덩어리로 쓰면 끔찍하겠지만, CTE를 쓰면 논리적으로 풀 수 있다.

WITH genre_sales AS (
    -- 1단계: 장르별 매출액 집계표를 먼저 만든다 (가상의 테이블)
    SELECT c.name AS genre, SUM(p.amount) AS total_sales
    FROM category c
    JOIN film_category fc ON c.category_id = fc.category_id
    JOIN inventory i ON fc.film_id = i.film_id
    JOIN rental r ON i.inventory_id = r.inventory_id
    JOIN payment p ON r.rental_id = p.rental_id
    GROUP BY c.name
)
-- 2단계: 만든 테이블에서 상위 5개를 뽑는다
SELECT genre, total_sales
FROM genre_sales
ORDER BY total_sales DESC
LIMIT 5;

 

복잡한 조인 과정을 genre_sales라는 블록으로 정의해 두고, 실제 조회(SELECT)는 아주 심플하게 처리했다.
이게 바로 현업 분석가들이 복잡한 데이터를 다루는 노하우가 아닐까 싶다.

 

📝 오늘의 문법 요약 (Cheat Sheet)

복잡한 문제를 만났을 때, 무작정 쿼리를 짜기보다 "어떻게 쪼개서 정복할까?"를 고민하게 해주는 도구들이다.

구분	특징	추천 상황
서브쿼리	쿼리 내부에 (SELECT ...)를 삽입	간단한 조건 필터링이나 계산이 필요할 때
CTE (WITH)	쿼리 상단에 임시 테이블 정의	쿼리가 길고 복잡하거나, 재사용이 필요할 때 (가독성 👍)

이제 SQL 문법적인 기초 체력은 모두 다졌다.
다음 시간부터는 대망의 "파이썬(Python) 연동" 단계로 넘어간다. SQL로 뽑은 데이터를 파이썬으로 가져와서 분석하는, 진짜 데이터 사이언티스트의 영역에 발을 들여놓을 예정이다. (설렘 반 걱정 반!)

 

 

[데이터 분석 독학기 #12 끝]