복잡한 쿼리 가독성 향상을 위한 CTE 사용법

상상해봐, 여러 개의 연관된 작업을 한 번에 처리해야 하는 거대한 SQL 쿼리를 작성해야 한다고 해. 그냥 서브쿼리를 잔뜩 중첩해서 쓸 수도 있지만, 그렇게 하면 코드가 완전 스파게티처럼 꼬여버려. 진짜 SQL 미로가 돼서, 나중에 본인도 어디가 어딘지 헷갈릴걸.

CTE는 진짜 구명튜브 같은 존재야! CTE를 쓰면 복잡한 쿼리를 논리적으로 나눠서, 각각을 이름 붙인 섹션으로 만들 수 있어. 그래서 쿼리가 훨씬 읽기 쉽고, 유지보수도 편해져.

비교: 서브쿼리 vs CTE

겉보기엔 둘 다 똑같이 — 과목별로 점수를 필터링하고, 학생별 평균 점수를 계산해. 근데 자세히 보면, 서브쿼리 버전은 로직이 괄호 안에 숨어 있고, CTE는 바깥으로 빼서 filtered_grades라는 이름을 붙였어. 만약 이런 중간 단계가 두 개가 아니라 열 개라면 어떨까?

서브쿼리:

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE:

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

차이점 10개 찾아봐! 확실히, CTE가 읽기 훨씬 편하지!

CTE로 복잡한 쿼리를 단계별로 나누기

CTE를 쓰면 쿼리를 단계별로 쌓아가면서, 각 단계의 결과를 최대한 명확하게 볼 수 있어. 예를 들어, 학생별 과목 평균 점수 리스트를 만들고, 거기에 담당 교수 정보를 추가하고 싶다면, 작업을 여러 부분으로 쪼개면 돼.

예시:

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

이거 진짜 읽기 쉽지 않아? 한 달 뒤에 다시 봐도 쿼리 구조가 바로 눈에 들어올 거야.

여러 개의 CTE로 대형 리포트 만들기

좀 더 복잡한 리포트 예시를 보자. 대학 데이터베이스가 있다고 치고, 가장 성적이 좋은 학생들과 그들의 과목, 교수 정보를 담은 리포트를 만들고 싶어. 플랜은 이래:

1. 먼저 평균 점수가 90점 넘는 학생을 찾는다.
2. 그 학생들을 과목과 매칭한다.
3. 마지막으로 교수 정보를 추가한다.

여러 CTE가 들어간 쿼리:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

이 쿼리의 좋은 점은, 각 작업을 논리적으로 딱딱 끊어서 별도 블록으로 만들었다는 거야. 누가 성적 짱인 학생인지 궁금해? high_achievers CTE를 보면 돼. 과목 연결은? student_courses에 있어. 교수 정보? teachers에 다 들어있지. 이런 방식이면 코드 유지보수나 수정이 훨씬 쉬워져.

복잡한 계산을 단계별로 쪼개기

가끔 쿼리에 복잡한 계산이나 필터링이 들어가. 이럴 때 한 번에 다 때려넣지 말고, 여러 CTE로 나눠서 처리하는 게 좋아.

예시:

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

여기서는 먼저 course_stats에서 과목별 통계를 모으고, popular_courses에서 인기 과목을 필터링한 다음, 마지막에 과목 테이블이랑 합쳐. 이런 식으로 중간 단계를 분리하면 쿼리 이해가 훨씬 쉬워져.

CTE가 필수템이 되는 순간?

CTE가 특히 빛을 발하는 상황 몇 가지를 소개할게:

1. 분석 및 리포트 작업. 예를 들어, 그룹별로 복잡한 지표를 계산할 때.
2. 계층 구조 다루기. 카테고리 트리나 조직 구조 만들 때 재귀 CTE가 짱임.
3. 데이터 재사용. 같은 데이터셋을 쿼리 여러 단계에서 써야 할 때.

CTE 쓸 때 자주 하는 실수

물론, 강력한 도구인 만큼 CTE에도 숨겨진 함정이 있어.

불필요한 데이터 materialization. PostgreSQL에서는 CTE가 기본적으로 "materialize"돼서, 결과가 계산되고 임시로 저장돼. 데이터가 너무 많으면 이게 쿼리 속도를 느리게 할 수 있어. 이럴 땐 인덱스를 잘 쓰고, 꼭 필요한 컬럼만 선택하는 게 좋아.

잘못된 조인. 여러 CTE가 들어간 복잡한 쿼리는 최적화가 어려워질 수 있어. 항상 EXPLAIN이나 EXPLAIN ANALYZE로 쿼리를 체크해봐.

CTE 남발. CTE가 너무 길고 복잡해지면, 쿼리를 여러 개로 쪼개는 게 더 나을 수도 있어.

2

과제

SQL SELF, 레벨 28, 레슨 0

잠금

여러 개의 CTE를 사용하여 학생 수 세기

여러 개의 CTE를 사용하여 학생 수 세기

열림