데이터 준비를 위한 간단한 CTE: 예제와 실제 케이스

데이터 준비를 위한 간단한 CTE: 예제와 실제 케이스

이미 CTE의 기본은 익혔고, 아마 WITH도 거의 자동으로 쓰고 있을 거야. 오늘은 좀 더 깊게 들어가서, 실제 상황에서 CTE를 데이터 준비에 어떻게 쓰는지 볼 거야. 예를 들어, 리포트나 복잡한 SQL 쿼리를 만들려고 한다면, 먼저 재료를 잘 정리해야 맛있는 분석 ‘국’을 끓일 수 있겠지.

CTE는 중간 단계 작업에 딱 좋은 도구야: 필터링, 카운트, 집계, 평균 계산 등 데이터 준비에 필요한 모든 걸 할 수 있어. 복잡한 쿼리도 논리적으로 쪼개서, 각 블록이 한 가지 일만 하게 만들 수 있지. 예를 들어, 필요한 행만 뽑거나, 평균을 계산하거나, 최종 쿼리를 위한 데이터를 준비하는 식으로. 이렇게 하면 코드 읽기도 쉽고, 반복되는 부분도 줄이고, 임시 테이블을 굳이 만들 필요도 없어.

CTE 방식은 특히 리포트용 데이터 준비, 복잡한 필터링, 데이터 ‘정리’가 필요할 때 진가를 발휘해. 그냥 기술적인 트릭이 아니라, 한 단계씩 논리를 쌓아가면서 전체 흐름을 컨트롤할 수 있는 전략이 되는 거지.

준비됐지? 이제 예제로 넘어가자.

CTE로 데이터 필터링하기

CTE는 전체 테이블에서 필요한 데이터만 ‘뽑아내서’ 그 다음부터는 진짜 필요한 것만 다룰 수 있게 해줘. 복잡한 서브쿼리 대신, 먼저 데이터를 필터링하고 그 단계에 이름을 붙여서, 결과를 일반 테이블처럼 쓸 수 있지.

예를 들어, students 테이블에 학생들의 점수가 저장돼 있다고 해보자:

students 테이블

예를 들어, 점수가 85점 넘는 학생만 뽑고 싶다고 해보자. CTE로 하면 진짜 투명하게 할 수 있어:

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

결과:

여기서 뭐가 편하냐면?

필요한 행을 미리 뽑아서 그 단계에 excellent_students라는 이름을 붙였어. 이제 이 결과를 다른 테이블과 조인하거나, 추가 필터링을 하거나, 평균 점수를 계산하는 데 쓸 수 있지. 쿼리가 길어져도 읽기 쉽고, 헷갈리지 않아.

CTE로 데이터 집계하기

이번엔 레코드 개수를 세거나 평균값을 구해야 하는 경우를 보자. 예를 들어, enrollments 테이블에 어떤 학생이 어떤 과목에 등록했는지 데이터가 있다고 해보자.

enrollments 테이블

각 과목에 몇 명이 등록했는지 알고 싶다고 해보자.

예시 쿼리:

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

결과:

여기서 중요한 점:

• course_id별로 데이터를 그룹화해서 각 과목에 몇 명이 등록했는지 셌어.
• course_enrollments 테이블에 이 정보가 담겨 있고, 추가 분석에도 쓸 수 있어.

리포트용 데이터 준비하기

여러 단계의 데이터 처리가 필요한 상세 리포트를 만들어야 한다면, CTE가 진짜 꿀이야. 전체 논리를 블록별로 쪼개서 쓸 수 있고, 쓸데없는 임시 테이블도 만들 필요 없어. 예를 들어, grades 테이블에 점수, students 테이블에 학생 정보가 있다고 해보자. 평균 점수가 80점 넘는 학생만 리포트에 넣고 싶어.

grades 테이블

students 테이블

복잡한 서브쿼리 대신, 단계별로 차근차근 만들면 돼:

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

첫 번째 단계(avg_grades)에서 각 학생의 평균 점수를 계산하고, 80점 넘는 애들만 남겼어. 두 번째 단계(students_with_grades)에서는 이 데이터를 students 테이블과 조인해서 이름과 성까지 가져왔지. 마지막 SELECT는 바로 리포트에 쓸 수 있는 깔끔한 테이블을 반환해 — 이미 계산, 필터링, 정리까지 다 끝난 상태야.

결과:

이런 방식이 CTE의 진짜 장점이야: 논리와 구조에 집중할 수 있고, 임시 테이블 만들고 지우는 귀찮은 작업에 신경 안 써도 돼.

복잡한 지표 계산하기

가끔은 한 쿼리에서 여러 데이터를 조합해야 할 때도 있어. 예를 들어, 각 과목별로:

1. 학생 수
2. 과목별 평균 점수

이렇게 계산하고 싶을 때:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

피해야 할 실수들

CTE를 쓰다 보면 헷갈리거나 흔히 하는 실수가 있어.

첫 번째는 과도한 materialization이야. CTE를 너무 많이 만들면, PostgreSQL이 결과를 임시 테이블로 저장할 수도 있는데, 이게 한 번만 필요한 거라면 오히려 쿼리가 느려질 수 있어.

두 번째 실수는 필터를 잘못 거는 거야. 필터 순서가 잘못되거나, 단계마다 다르게 적용하면, 최종 결과가 기대와 다를 수 있어. 예를 들어, 중요한 데이터를 너무 일찍 걸러버릴 수도 있지.

그래서 CTE는 데이터가 여러 번 변환되는 복잡한 흐름에서 쓰는 게 좋아 — 그럴 때 이 도구가 진짜 빛나고, 코드도 깔끔하고 이해하기 쉬워져.