마이위키

테마

난이도 문제은행 4

서브쿼리·CTE·윈도우 함수 2 ​

서브쿼리·CTE·윈도우 함수에서 본 서브쿼리(subquery)를 더 읽기 쉽게, 더 강하게 쓰는 도구인 CTE(Common Table Expression, 공통 테이블 식)를 다룬다. CTE는 서브쿼리 결과에 이름을 붙여 쿼리 맨 앞에 두는 문법이고, 재귀로 자기 자신을 불러 조직도 같은 계층 구조를 한 쿼리로 펼칠 수 있다.

CTE ​

sql
WITH paid AS (
  SELECT user_id, SUM(amount) AS total FROM orders
  WHERE status = 'PAID' GROUP BY user_id
)
SELECT u.name, p.total FROM users u JOIN paid p ON p.user_id = u.id;

WITH 이름 AS (서브쿼리)로 만든 결과는 그 문장 안에서만 테이블처럼 쓸 수 있다. 같은 일을 FROM 절의 파생 테이블(derived table)로도 할 수 있지만 CTE가 나은 점이 있다.

  • 여러 번 참조할 수 있다. 파생 테이블은 적힌 자리에서 한 번만 쓸 수 있어, 같은 결과가 두 번 필요하면 서브쿼리를 두 번 적어야 한다. CTE는 이름으로 여러 번 부를 수 있다.
  • 앞의 CTE를 뒤의 CTE가 참조할 수 있다. WITH a AS (...), b AS (SELECT ... FROM a)처럼 단계를 나눠 쓰면 긴 쿼리를 위에서 아래로 읽을 수 있다.
  • 재귀가 된다. 파생 테이블로는 할 수 없다.

성능 면에서 CTE는 파생 테이블과 같은 규칙으로 처리된다. 옵티마이저(optimizer)는 둘 중 한 방식을 고른다. 하나는 병합(merge)으로, CTE의 SELECT를 바깥 쿼리에 풀어 넣어 한 쿼리로 합친다. 다른 하나는 구체화(materialization)로, CTE 결과를 임시 테이블(temporary table)에 실제로 만들어 두고 바깥 쿼리가 그 테이블을 읽는다. 여러 번 참조된 CTE를 구체화할 때는 한 번만 만든다. CTE로 바꿨다고 저절로 빨라지지는 않는다는 뜻이다.

재귀 CTE ​

재귀 CTE(recursive CTE)는 자기 자신을 참조하는 CTE다. 조직도의 상하 관계나 카테고리 트리처럼 깊이가 정해지지 않은 계층을 한 쿼리로 펼칠 때 쓴다.

sql
WITH RECURSIVE chain (id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 1 FROM emp WHERE manager_id IS NULL  -- 비재귀 부분
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1                    -- 재귀 부분
  FROM emp e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;
다이어그램 로딩…

WITH RECURSIVE 안은 UNION ALL(또는 UNION)을 사이에 두고 두 부분으로 나뉜다. 비재귀 부분이 시작 행을 만들고, 재귀 부분은 바로 앞 단계에서 새로 나온 행만 받아 다음 단계 행을 만든다. 새 행이 더 나오지 않으면 끝난다. UNION을 쓰면 이미 나온 행과 같은 행은 버리므로, 같은 행을 되풀이해 만들지 않는다.

  • 무한 반복 방지: 데이터에 순환(A의 상사가 B, B의 상사가 A)이 있으면 끝나지 않는다. 서버는 반복 단계가 cte_max_recursion_depth(기본 1000)를 넘으면 문장을 중단한다. 조건(WHERE depth < 10)이나 LIMIT으로 직접 멈추게 하는 것이 안전하다.
  • 칼럼 타입은 비재귀 부분이 정한다. 비재귀 부분에서 'abc'로 시작한 칼럼은 세 글자짜리 문자열 칼럼이 된다. 재귀 부분이 여기에 더 긴 문자열을 넣으면, 8.4 기본값인 엄격 모드(strict mode)에서는 "Data too long"이라는 에러 1406으로 문장이 실패하고 엄격 모드가 아니면 값이 잘린다. 'CEO > 팀장 > 사원'처럼 단계마다 길어지는 경로 문자열을 만들 때는 비재귀 부분에서 CAST(id AS CHAR(200))처럼 넉넉히 잡는다.
  • 재귀 부분에는 SUM()·COUNT() 같은 집계 함수(aggregate function), 윈도우 함수(window function), GROUP BY, ORDER BY, DISTINCT를 쓸 수 없다. LIMIT은 8.0.19부터 쓸 수 있다.

이어지는 내용은 서브쿼리·CTE·윈도우 함수 3에서 다룬다. 행을 합치지 않고 각 행 옆에 순위·이전 행 값·누적합을 붙이는 윈도우 함수를 본다.

면접 포인트 ​

  • "CTE와 파생 테이블 차이?" → 여러 번 참조, 앞 CTE 참조, 재귀 가능. 성능 처리 규칙(병합·구체화)은 같다.
  • "재귀 CTE가 안 끝나요" → 데이터 순환. cte_max_recursion_depth(기본 1000)가 막는다. 깊이 조건·LIMIT으로 직접 멈춘다.
  • "재귀 CTE로 만든 경로가 잘리거나 실패해요" → 칼럼 타입은 비재귀 부분이 정한다. CAST(... AS CHAR(200))처럼 넉넉히 잡는다.

관련 문서

서서브쿼리·CTE·윈도우 함수소주제›서서브쿼리·CTE·윈도우 함수 3소주제›정정렬·그룹·임시 테이블소주제›
🎙 인터뷰 연습퀴즈 풀기 4문항