한 번에 안 풀리는 요구사항,
단계로 쪼개서 CTE로 묶기
"카테고리별 매출 1위 상품"처럼 한 단계 SQL로는 안 풀리는 요구사항 — 어떻게 AI에게 시킬까요? 답은 단계 분해입니다. 작은 단계로 쪼개고 보면 각 단계는 이미 아는 기본 SQL이고, WITH 절(CTE)이 그 단계들을 한 쿼리로 묶어줍니다. 5-3 안티패턴 ④의 공식 해결책입니다.
이번 섹션의 여정
완료 ✓
지금 여기
전 과정 실연
AI 결과 의심하기
5-3에서 안티패턴 ④ — “한 번에 너무 많이” 를 봤죠. 그때 “진짜 복잡한 요구사항은 5-4에서”라고 미뤄뒀던 숙제가 바로 이번 강의입니다. 요구사항 자체가 여러 단계를 요구할 때 — 쪼개는 사고법과, 쪼갠 단계를 묶는 SQL 문법 두 가지를 가져갑니다.
한 줄 요구사항, 한 단계 SQL로는 안 풀립니다
이런 요구사항을 받았다고 해볼게요.
각 카테고리에서 매출 1위인 상품을 — 카테고리명, 상품명, 매출과 함께 보여줘.한 줄짜리 요구사항입니다. 그런데 “각 카테고리 안에서 1위” 라는 게 — 매출을 집계하고, 카테고리별 최댓값을 구하고, 그 최댓값과 매칭되는 상품을 골라내는 여러 단계의 비교를 요구해요. 한 단계 SQL로는 안 풀립니다.
”카테고리별 매출 1위 상품 보여줘”
AI가 어찌어찌 짜주긴 합니다. 하지만 JOIN + GROUP BY + 서브쿼리(또는 윈도우 함수)가 한 덩어리로 얽히면서 틀릴 가능성이 높고, 틀려도 어디가 틀렸는지 추적이 안 됩니다.
작은 단계 4개로 분해
쪼개고 보면 각 단계는 섹션 3에서 이미 배운 기본 SQL입니다. 단계마다 따로 검증할 수 있고, AI도 한 번에 정확한 쿼리를 줄 확률이 확 올라갑니다.
단계로 쪼개는 사고법 ⭐
요구사항을 작은 SQL 단계로 분해해 봅니다.
단계 1 — 상품별 매출. price × quantity 의 합. 3-4에서 배운 GROUP BY 그대로입니다.
SELECT
oi.product_id,
SUM(p.price * oi.quantity) AS sales
FROM
order_items oi
JOIN products p
ON oi.product_id = p.product_id
GROUP BY
oi.product_id;단계 2 — 각 상품의 카테고리도 같이. 단계 1에 컬럼 두 개만 얹으면 됩니다.
SELECT
p.product_id,
p.category_id,
p.product_name,
SUM(p.price * oi.quantity) AS sales
FROM
order_items oi
JOIN products p
ON oi.product_id = p.product_id
GROUP BY
p.product_id,
p.category_id,
p.product_name
ORDER BY
sales DESC;여기까지는 실제로 돌아갑니다. DBeaver에서 실행해 보면 —
| product_id | category_id | product_name | sales |
|---|---|---|---|
| 8 | 1 | 패딩 점퍼 | 645,000 |
| 9 | 2 | 무선 이어폰 | 534,000 |
| 27 | 5 | 수분 크림 | 256,000 |
| ⋮ | |||
단계 3 — 카테고리별 최대 매출. 그런데 여기서 막힙니다. FROM 자리에 “단계 2의 결과” 를 넣고 싶은데 — 그런 테이블은 없으니까요.
-- 아직은 의사코드 — "단계 2 결과"라는 테이블은 존재하지 않습니다
SELECT
category_id,
MAX(sales) AS max_sales
FROM
(단계 2 결과)
GROUP BY
category_id;단계 4 — 최대 매출과 매칭. 단계 2와 단계 3을 조인해서, 같은 카테고리이면서 sales = max_sales 인 행만 골라내면 끝.
각 단계는 우리가 이미 아는 SQL입니다. 1·2단계는 JOIN + GROUP BY, 3단계는 MAX, 4단계는 JOIN — 전부 섹션 3에서 배운 것. 남은 문제는 딱 하나, “단계의 결과에 이름을 붙여서 다음 단계가 쓰게 하는 방법” 뿐입니다.
CTE (WITH 절) — 단계를 그대로 SQL로
이 4단계를 한 SQL로 묶는 문법이 있습니다. WITH 절 — 정식 명칭은 CTE(Common Table Expression)입니다. WITH 이름 AS (SELECT ...) 형태로 임시 결과에 이름을 붙이고, 그 아래에서 그 이름을 진짜 테이블처럼 쓰는 거예요.
WITH product_sales AS (
-- 단계 1+2: 상품별 매출 + 카테고리
SELECT
p.product_id,
p.category_id,
p.product_name,
SUM(p.price * oi.quantity) AS sales
FROM
order_items oi
JOIN products p
ON oi.product_id = p.product_id
GROUP BY
p.product_id,
p.category_id,
p.product_name
),
category_max AS (
-- 단계 3: 카테고리별 최대 매출
SELECT
category_id,
MAX(sales) AS max_sales
FROM
product_sales
GROUP BY
category_id
)
-- 단계 4: 매칭 + 최종 출력
SELECT
c.category_name,
ps.product_name,
ps.sales
FROM
product_sales ps
JOIN category_max cm
ON ps.category_id = cm.category_id
AND ps.sales = cm.max_sales
JOIN categories c
ON ps.category_id = c.category_id
ORDER BY
ps.sales DESC;category_max 의 FROM 자리를 보세요 — 아까 막혔던 “단계 2 결과” 자리에 product_sales 라는 이름이 들어가 있습니다. DBeaver에서 통째로 실행하면 —
| category_name | product_name | sales |
|---|---|---|
| 의류 | 패딩 점퍼 | 645,000 |
| 전자기기 | 무선 이어폰 | 534,000 |
| 뷰티 | 수분 크림 | 256,000 |
| 도서 | 데이터 모델링 실전 | 192,000 |
| 식품 | 프리미엄 그래놀라 | 60,000 |
CTE = 단계별 사고를 SQL로 그대로 표현하는 도구. 머릿속의 “1단계,
2단계, 3단계”가 — WITH 이름 AS 블록 하나하나로 그대로 옮겨집니다.
CTE 여러 개는 콤마로 이어 붙이고, 마지막 CTE 뒤에는 콤마 없이 바로 본
SELECT 가 옵니다. 위 블록(product_sales)을 아래 블록(category_max)과 본
SELECT 가 테이블처럼 참조 — 위에서 아래로 읽으면 단계가 그대로 보입니다.
CTE vs 서브쿼리 — 같은 결과, 다른 가독성
사실 같은 결과를 서브쿼리(FROM 안에 SELECT 를 우겨넣기)로도 만들 수 있습니다. 직접 보세요 —
SELECT
c.category_name,
ps.product_name,
ps.sales
FROM
(SELECT
p.product_id,
p.category_id,
p.product_name,
SUM(p.price * oi.quantity) AS sales
FROM
order_items oi
JOIN products p
ON oi.product_id = p.product_id
GROUP BY
p.product_id,
p.category_id,
p.product_name) ps
JOIN (SELECT
category_id,
MAX(sales) AS max_sales
FROM
( ...위와 똑같은 서브쿼리를 한 번 더... ) t
GROUP BY
category_id) cm
ON ps.category_id = cm.category_id
AND ps.sales = cm.max_sales
JOIN categories c
ON ps.category_id = c.category_id;읽기 어렵고, 같은 쿼리를 반복
괄호가 괄호를 물고 들어가서 어디가 몇 단계인지 안 보입니다. 게다가 상품별 매출 서브쿼리를 두 번 똑같이 반복해야 하죠. 고칠 때도 두 군데를 같이 고쳐야 합니다.
한눈에 단계가 보이고, 이름을 재사용
product_sales → category_max → 본 SELECT. 읽는 순서가 곧 사고의 순서입니다. 한 번 정의한 이름은 몇 번이든 다시 쓸 수 있어요.
AI가 만든 SQL을 나중에 본인이(또는 동료가) 다시 읽게 될 때 — 단계 이름이 붙어 있는 CTE가 시간을 아껴줍니다. 검증할 때도 블록 단위로 따로 떼어 실행할 수 있고요.
AI에게 단계별로 시키는 법
이제 이 사고법을 AI 협업에 그대로 적용합니다. 핵심은 단계 분해를 AI에게 맡기지 않고 — 여러분이 미리 해서 주는 것.
복잡한 요구사항이라 단계별로 짜줘 — WITH 절로 묶어줘.
[테이블 구조]
... (5-2 에서 만든 shop 포맷 그대로)
[요구사항 — 단계 분해]
- 단계 1: 상품별 매출 (price × quantity 합) — product_id, category_id, sales
- 단계 2: 각 카테고리의 최대 매출
- 단계 3: 단계 1 과 단계 2 를 조인해서 1위 상품 추출
[최종 출력]
- 컬럼: 카테고리명, 상품명, 매출
- 정렬: 매출 많은 순
WITH 절을 활용해서 단계마다 임시 테이블 이름을 붙여줘.이렇게 단계를 미리 분해해서 주면 — AI가 한 번에 정확한 CTE 쿼리를 줍니다. 쿼리의 뼈대(단계)는 사람이 정하고, 살(문법)은 AI가 붙이는 분업이에요.
더 안전한 방법도 있습니다 — 한 단계씩 받고, 받을 때마다 검증하는 것.
첫 메시지는 딱 한 단계 — “단계 1의 SQL만 짜줘 — 상품별 매출.” 받은 SQL을 바로 실행해서 행 수와 값이 말이 되는지 확인합니다.
“단계 1 결과를 활용해서 — 각 카테고리의 최대 매출도 추가해줘.” 또 실행, 또 검증 — 카테고리 5개니까 5행이 나와야겠죠.
“이 두 단계를 WITH 절로 묶어서 최종 쿼리 만들어줘.” 최종 결과(5행, 카테고리별 1위)까지 확인하면 끝.
한 번에 통째로 받으면 틀렸을 때 어디가 틀렸는지 추적이 어렵습니다. 단계마다 검증하면 틀린 단계 하나만 다시 시키면 돼요. 조금 느려 보여도 — 정확한 길이 결국 빠릅니다.
언제 CTE가 빛나나 — 한 장 정리
CTE는 “단계로 쪼개야 풀리는” 모든 SQL에서 빛납니다. 그리고 실무 분석 요구사항의 대부분이 정확히 이 형태예요.
| 실무 시나리오 | CTE 단계 |
|---|---|
| 카테고리별 1위 상품 | 상품별 매출 → 카테고리 max → 매칭 |
| 회원별 가장 비싼 주문 | 주문별 총액 → 회원별 max → 매칭 |
| 월별 신규 + 누적 회원 수 | 월별 신규 → 누적 합 |
| TOP 10% 회원의 매출 | 회원별 매출 → 상위 10% 기준 → 추출 |
| 카테고리 평균보다 잘 팔린 상품 | 카테고리별 평균 → 비교 |
오늘의 사고법을 네 줄로 정리하면 이렇습니다. 캡처해서 저장해두세요.
| # | 단계 | 한 줄 |
|---|---|---|
| 1 | 분해 | 한 줄 요구사항 → 작은 단계 N개로 |
| 2 | 안심 | 각 단계는 이미 아는 기본 SQL |
| 3 | 결합 | WITH 절로 묶고 단계마다 이름 붙이기 |
| 4 | 협업 | AI 에는 단계를 분해해서 주거나, 한 단계씩 |
자주 막히는 지점
가장 흔한 원인 두 가지 — 마지막 CTE 뒤에 콤마를 찍었거나, CTE만 정의하고 본 SELECT 를 빠뜨린 경우입니다.
SELECT VERSION(); — CTE 는 MySQL 8.0부터 지원합니다 (2-1에서 8.x를 설치했다면 OK).WITH 안의 각 SELECT 는 독립 쿼리로도 완전히 동작합니다. CTE 가 검증하기 좋은 이유가 바로 이거예요.
product_sales 는 20행, category_max 는 5행인지 확인.처음엔 어디서 잘라야 할지 막막한 게 정상입니다. 연습 몇 번이면 “집계 한 번 = 단계 하나” 감각이 생겨요.
이제 섹션 5의 도구는 다 모였습니다 — 컨텍스트 3가지, 전달 포맷, 좋은 프롬프트 5원칙, 그리고 단계 분해 + CTE. 다음 강의 5-5 라이브 시연에서는 실무 요구사항 하나를 — 요구사항 분석부터 최종 검증까지 한 사이클 전체를 처음부터 끝까지 직접 보여드립니다. 그 전에 연습 문제로 CTE 를 손에 익혀두세요.