여러 개의 CTE가 있는 복잡한 쿼리 예시

이번 강의에서는 진짜 쿼리 마법을 부려볼 거야! 여러 개의 CTE를 사용해서 서로 어떻게 연결해서 복잡하고 여러 단계로 된 쿼리를 만들 수 있는지 예시를 만들어볼 거야. 이런 예시는 실제로 복잡한 분석 작업에서 진짜 유용하게 쓸 수 있어.

예시 1: 학생 성적 분석

우리한테 대학 데이터베이스가 있고, 세 개의 테이블이 있다고 상상해봐:

students 테이블:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name TEXT NOT NULL
);

grades 테이블:

CREATE TABLE grades (
    grade_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT NOT NULL,
    grade NUMERIC(3, 1) NOT NULL
);

courses 테이블:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT NOT NULL
);

문제: 평균 점수가 85점이 넘는 학생들의 리스트를, 그 학생의 평균 점수와 듣고 있는 과목 이름과 함께 가져와야 해.

쿼리:

WITH high_achievers AS (
    -- 평균 점수가 높은 학생 찾기
    SELECT 
        student_id, 
        AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 85
),
student_courses AS (
    -- 각 학생이 수강 중인 과목 찾기
    SELECT 
        s.student_id, 
        c.course_name
    FROM grades g
    JOIN courses c ON g.course_id = c.course_id
    JOIN students s ON g.student_id = s.student_id
)
-- 결과 합치기
SELECT 
    s.student_name, 
    ha.avg_grade, 
    sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;

설명:

1. 첫 번째 CTE(high_achievers)에서는 각 학생의 평균 점수를 계산하고, 85점 넘는 학생만 골라.
2. 두 번째 CTE(student_courses)에서는 학생과 그 학생이 듣는 과목을 매칭해.
3. 메인 쿼리에서는 두 CTE의 데이터를 합쳐서, 학생 이름, 평균 점수, 듣는 과목 리스트를 가져와.

예시 2: 온라인 쇼핑몰 판매 리포트

이번엔 온라인 쇼핑몰 데이터베이스가 있고, 이런 테이블들이 있다고 해보자:

orders (주문) 테이블:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

customers (고객) 테이블:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL
);

order_items (주문 상품) 테이블:

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

문제: 각 고객에 대해 다음을 보여주는 리포트를 만들어야 해:

• 총 주문 개수
• 지난 한 달 동안의 전체 주문 금액
• 그 고객이 구매한 모든 유니크한 상품 리스트

쿼리:

WITH recent_orders AS (
    -- 지난 한 달 동안의 주문 선택
    SELECT 
        order_id, 
        customer_id, 
        total_amount 
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
    -- 각 고객의 총 주문 개수와 금액 계산
    SELECT 
        ro.customer_id,
        COUNT(ro.order_id) AS total_orders,
        SUM(ro.total_amount) AS total_spent
    FROM recent_orders ro
    GROUP BY ro.customer_id
),
customer_products AS (
    -- 각 고객이 구매한 유니크한 상품 선택
    SELECT DISTINCT
        ro.customer_id,
        oi.product_id
    FROM recent_orders ro
    JOIN order_items oi ON ro.order_id = oi.order_id
)
-- 결과 합치기
SELECT 
    c.customer_name,
    cs.total_orders,
    cs.total_spent,
    ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;

설명:

1. 첫 번째 CTE(recent_orders)에서는 지난 한 달 동안의 주문을 뽑아.
2. 두 번째 CTE(customer_summary)에서는 각 고객의 총 주문 개수와 금액을 계산해.
3. 세 번째 CTE(customer_products)에서는 각 고객이 구매한 유니크한 상품을 가져와.
4. 최종 쿼리에서는 데이터를 합치고, ARRAY_AGG()로 유니크한 상품 리스트를 만들어.

예시 3: 직원 계층 구조 분석

우리한테 직원 테이블이 있어:

employees 테이블:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    manager_id INT NULL
);

문제: 사장(최상위 관리자)부터 시작해서 직원 계층 구조를 만들고, 각 직원의 계층 레벨을 표시해야 해.

쿼리:

WITH RECURSIVE employee_hierarchy AS (
    -- 매니저가 없는 직원(사장)부터 시작
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 현재 레벨의 부하 직원 추가
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- 계층 구조 출력
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    level
FROM employee_hierarchy
ORDER BY level, employee_id;

1. 재귀 쿼리에서는 사장(매니저 없는 직원)부터 시작해.
2. 각 단계마다 현재 레벨의 부하 직원을 추가하고, level을 1씩 올려.
3. 메인 쿼리에서는 전체 계층 구조를 레벨과 직원 ID로 정렬해서 보여줘.

꿀팁 & 흔한 실수

• CTE 남발: 서브쿼리로 충분한 곳에 굳이 CTE를 쓰지 마. CTE는 데이터가 materialize돼서 오히려 성능이 떨어질 수도 있어.
• CTE 이름: CTE 이름은 짧고 직관적으로 지어서 쿼리가 읽기 쉽게 만들어.
• 실행 순서: CTE는 선언한 순서대로 실행된다는 걸 기억해.
• 데이터 그룹핑: GROUP BY는 꼭 필요한 곳에서만 써서 불필요한 연산을 줄여.

이 모든 예시를 보면, CTE를 사용해서 복잡한 문제를 단계별로 나누고 쿼리의 가독성과 유지보수를 확실히 높일 수 있다는 걸 알 수 있어. 이제 PostgreSQL로 복잡한 분석 문제도 거뜬히 해결할 수 있을 거야!