한양과기고 에듀테크 수업도구 대시보드

WITH RECURSIVE EXISTS MATERIALIZED data-modifying CTE

쿼리 안의 쿼리. WITH(CTE)로 이름을 붙이면 복잡한 로직도 위에서 아래로 읽힙니다.

1. 서브쿼리 위치

-- 스칼라 (값 하나)
SELECT name, (SELECT count(*) FROM exams e WHERE e.student_id = s.id) AS 시험수
FROM students s;

-- IN / EXISTS
SELECT * FROM students
WHERE id IN (SELECT student_id FROM exams WHERE score = 100);

SELECT * FROM students s
WHERE EXISTS (SELECT 1 FROM exams e WHERE e.student_id = s.id AND e.score = 100);

IN vs EXISTS

보통 플래너가 알아서 최적화하지만, 서브쿼리에 NULL 이 섞일 수 있으면 NOT IN 은 함정 — NOT EXISTS 를 쓰세요

2. CTE (WITH)

WITH recent AS (
    SELECT * FROM exams WHERE exam_date >= now() - interval '30 days'
),
ranked AS (
    SELECT student_id, score,
           rank() OVER (ORDER BY score DESC) AS rnk
    FROM recent
)
SELECT s.name, r.score, r.rnk
FROM ranked r JOIN students s ON s.id = r.student_id
WHERE r.rnk <= 10;

3. 재귀 CTE

-- 조직도: 특정 사원의 모든 하위 사원
WITH RECURSIVE subtree AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1   -- 시작점
  UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e JOIN subtree t ON e.manager_id = t.id     -- 재귀
)
SELECT * FROM subtree;

4. 최적화·데이터 변경 CTE

구체화(MATERIALIZED)

PG12+ : 단순 CTE 는 본 쿼리에 인라인됨(빠름). 강제하려면 WITH x AS MATERIALIZED (...) / NOT MATERIALIZED

-- 옮기면서 삭제 (원자적)
WITH moved AS (
    DELETE FROM events WHERE created_at < '2025-01-01' RETURNING *
)
INSERT INTO events_archive SELECT * FROM moved;

"커넥션은 비싸다 — 요청마다 열지 말고 풀에서 빌려라"