WITH 절
WITH 절 (WITH Clause)
복잡한 쿼리를 여러 단계로 나누고 싶을 때 WITH 절을 써요. WITH 절은 공통 테이블 표현식(Common Table Expressions, CTE)을 지정할 수 있게 해줘요. 일반적인(비재귀) 공통 테이블 표현식은 본질적으로 특정 쿼리로 범위가 제한된 뷰(view)이에요. CTE는 서로를 참조할 수 있고 중첩될 수 있어요. 재귀 CTE는 자기 자신을 참조할 수 있죠.
기본 CTE 예제
cte라는 CTE를 만들고 메인 쿼리에서 사용해요:
WITH cte AS (SELECT 42 AS x)
SELECT * FROM cte;
| x |
|---|
| 42 |
cte1과 cte2라는 두 CTE를 만들되, 두 번째 CTE가 첫 번째 CTE를 참조해요:
WITH
cte1 AS (SELECT 42 AS i),
cte2 AS (SELECT i * 100 AS x FROM cte1)
SELECT * FROM cte2;
| x |
|---|
| 4200 |
CTE에 열 이름을 지정할 수 있어요:
WITH cte(j) AS (SELECT 42 AS i)
FROM cte;
CTE 구체화 (Materialization)
DuckDB는 CTE를 기본적으로 _구체화(materialized)_해서 처리해요. 즉, CTE가 한 번 평가되고 결과가 임시 테이블에 저장된다는 뜻이에요. 하지만 특정 조건에서는 DuckDB가 CTE를 메인 쿼리에 _인라인(inline)_할 수 있는데, 이는 CTE가 구체화되지 않고 그 정의가 참조되는 각 위치에 복제된다는 뜻이에요. 인라인은 다음 휴리스틱에 따라 수행돼요:
- CTE가 한 번보다 더 많이 참조되지 않는 경우
- CTE가
VOLATILE함수를 포함하지 않는 경우 - CTE가
AS NOT MATERIALIZED를 사용하고AS MATERIALIZED를 사용하지 않는 경우 - CTE가 그룹화된 집계를 수행하지 않는 경우
구체화는 CTE를 AS MATERIALIZED로 정의하면 명시적으로 활성화할 수 있고, AS NOT MATERIALIZED를 사용하면 비활성화할 수 있어요. 휴리스틱을 충족하더라도 인라인이 항상 가능한 것은 아니라는 점을 참고하세요. 예를 들어 CTE에 read_csv 함수가 포함되면 인라인할 수 없어요.
예를 들어 같은 CTE를 세 번 호출하는 다음 쿼리를 봐요:
WITH t(x) AS (⟨complex_query⟩)
SELECT *
FROM
t AS t1,
t AS t2,
t AS t3;
인라인은 각 참조에 대해 t의 정의를 복제하며, 결과적으로 다음과 같은 쿼리가 돼요:
SELECT *
FROM
(⟨complex_query⟩) AS t1(x),
(⟨complex_query⟩) AS t2(x),
(⟨complex_query⟩) AS t3(x);
complex_query가 비싸다면 MATERIALIZED 키워드로 구체화하면 성능을 개선할 수 있어요. 이 경우 complex_query는 한 번만 평가돼요.
WITH t(x) AS MATERIALIZED (⟨complex_query⟩)
SELECT *
FROM
t AS t1,
t AS t2,
t AS t3;
구체화를 비활성화하려면 NOT MATERIALIZED를 사용해요:
WITH t(x) AS NOT MATERIALIZED (⟨complex_query⟩)
SELECT *
FROM
t AS t1,
t AS t2,
t AS t3;
일반적으로 명시적인 구체화 힌트를 사용하는 것은 권장되지 않아요. DuckDB의 쿼리 최적화기는 쿼리 구조와 위에서 언급한 휴리스틱에 따라 언제 CTE를 구체화하거나 인라인할지 결정할 수 있기 때문이에요. 하지만 어떤 경우에는 MATERIALIZED 또는 NOT MATERIALIZED를 사용해 동작을 명시적으로 제어하는 것이 유용할 수 있어요.
재귀 CTE (Recursive CTEs)
WITH RECURSIVE는 자기 자신을 참조할 수 있는 CTE를 정의할 수 있게 해줘요. 쿼리가 종료를 보장하는 방식으로 구성되어야 한다는 점을 참고하세요. 그렇지 않으면 무한 루프에 빠질 수 있어요.
예제: 피보나치 수열
WITH RECURSIVE는 재귀 계산을 하는 데 사용할 수 있어요. 예를 들어 WITH RECURSIVE를 사용해 처음 10개의 피보나치 수를 계산하는 방법을 볼게요:
WITH RECURSIVE FibonacciNumbers (
RecursionDepth, FibonacciNumber, NextNumber
) AS (
-- Base case
SELECT
0 AS RecursionDepth,
0 AS FibonacciNumber,
1 AS NextNumber
UNION ALL
-- Recursive step
SELECT
fib.RecursionDepth + 1 AS RecursionDepth,
fib.NextNumber AS FibonacciNumber,
fib.FibonacciNumber + fib.NextNumber AS NextNumber
FROM
FibonacciNumbers fib
WHERE
fib.RecursionDepth + 1 < 10
)
SELECT
fn.RecursionDepth AS FibonacciNumberIndex,
fn.FibonacciNumber
FROM
FibonacciNumbers fn;
| FibonacciNumberIndex | FibonacciNumber |
|---|---|
| 0 | 0 |
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 5 |
| 6 | 8 |
| 7 | 13 |
| 8 | 21 |
| 9 | 34 |
예제: 트리 순회
WITH RECURSIVE는 트리를 순회하는 데 사용할 수 있어요. 예를 들어 태그의 계층 구조를 봐요:
CREATE TABLE tag (id INTEGER, name VARCHAR, subclassof INTEGER);
INSERT INTO tag VALUES
(1, 'U2', 5),
(2, 'Blur', 5),
(3, 'Oasis', 5),
(4, '2Pac', 6),
(5, 'Rock', 7),
(6, 'Rap', 7),
(7, 'Music', 9),
(8, 'Movies', 9),
(9, 'Art', NULL);
다음 쿼리는 노드 Oasis에서 트리의 루트(Art)까지의 경로를 반환해요.
WITH RECURSIVE tag_hierarchy(id, source, path) AS (
SELECT id, name, [name] AS path
FROM tag
WHERE subclassof IS NULL
UNION ALL
SELECT tag.id, tag.name, list_prepend(tag.name, tag_hierarchy.path)
FROM tag, tag_hierarchy
WHERE tag.subclassof = tag_hierarchy.id
)
SELECT path
FROM tag_hierarchy
WHERE source = 'Oasis';
| path |
|---|
| [Oasis, Rock, Music, Art] |
그래프 순회
WITH RECURSIVE 절은 임의의 그래프에 대한 그래프 순회를 표현하는 데 사용할 수 있어요. 하지만 그래프에 사이클(cycle)이 있으면 무한 루프를 방지하기 위해 쿼리가 사이클 검출(cycle detection)을 수행해야 해요. 이를 달성하는 한 가지 방법은 순회의 경로를 리스트(list)에 저장하고, 새 간선으로 경로를 확장하기 전에 그 끝점이 이전에 방문되었는지 확인하는 것이에요 (아래 예제 참고).
CREATE TABLE edge (node1id INTEGER, node2id INTEGER);
INSERT INTO edge VALUES
(1, 3), (1, 5), (2, 4), (2, 5), (2, 10), (3, 1),
(3, 5), (3, 8), (3, 10), (5, 3), (5, 4), (5, 8),
(6, 3), (6, 4), (7, 4), (8, 1), (9, 4);
그래프에는 예를 들어 노드 1, 5, 8 사이에 방향성 사이클이 포함되어 있음을 참고하세요.
한 노드에서 모든 경로 열거하기
다음 쿼리는 노드 1에서 시작하는 모든 경로를 반환해요:
WITH RECURSIVE paths(startNode, endNode, path) AS (
SELECT -- 순회의 첫 간선으로 경로 정의
node1id AS startNode,
node2id AS endNode,
[node1id, node2id] AS path
FROM edge
WHERE startNode = 1
UNION ALL
SELECT -- 새 간선을 경로에 연결
paths.startNode AS startNode,
node2id AS endNode,
array_append(path, node2id) AS path
FROM paths
JOIN edge ON paths.endNode = node1id
-- 반복된 노드를 경로에 추가하는 것을 방지하세요.
-- 이렇게 하면 사이클이 발생하지 않습니다.
WHERE list_position(paths.path, node2id) IS NULL
)
SELECT startNode, endNode, path
FROM paths
ORDER BY length(path), path;
| startNode | endNode | path |
|---|---|---|
| 1 | 3 | [1, 3] |
| 1 | 5 | [1, 5] |
| 1 | 5 | [1, 3, 5] |
| 1 | 8 | [1, 3, 8] |
| 1 | 10 | [1, 3, 10] |
| 1 | 3 | [1, 5, 3] |
| 1 | 4 | [1, 5, 4] |
| 1 | 8 | [1, 5, 8] |
| 1 | 4 | [1, 3, 5, 4] |
| 1 | 8 | [1, 3, 5, 8] |
| 1 | 8 | [1, 5, 3, 8] |
| 1 | 10 | [1, 5, 3, 10] |
이 쿼리의 결과는 최단 경로로 제한되지 않는다는 점을 참고하세요. 예를 들어 노드 5에 대해 결과는 [1, 5]와 [1, 3, 5] 경로를 포함해요.
한 노드에서 가중치 없는 최단 경로 열거하기
대부분의 경우 모든 경로를 열거하는 것은 실용적이지 않거나 불가능해요. 대신 (가중치 없는) 최단 경로만 관심 대상이에요. 이를 찾으려면 WITH RECURSIVE 쿼리의 후반부를 조정해서 아직 방문하지 않은 노드만 포함하도록 해야 해요. 이는 이전 경로 중 어떤 것이 그 노드를 포함하는지 확인하는 서브쿼리를 사용해 구현돼요:
WITH RECURSIVE paths(startNode, endNode, path) AS (
SELECT -- 순회의 첫 간선으로 경로 정의
node1id AS startNode,
node2id AS endNode,
[node1id, node2id] AS path
FROM edge
WHERE startNode = 1
UNION ALL
SELECT -- 새 간선을 경로에 연결
paths.startNode AS startNode,
node2id AS endNode,
array_append(path, node2id) AS path
FROM paths
JOIN edge ON paths.endNode = node1id
-- 이전에 어떤 경로로도 방문하지 않은 노드를 추가하는 것을 방지하세요.
-- 이렇게 하면 (1) 사이클이 발생하지 않고 (2) 이전 (더 짧은) 경로에서
-- 방문하지 않은 노드만 경로에 추가됩니다.
WHERE NOT EXISTS (
FROM paths previous_paths
WHERE list_contains(previous_paths.path, node2id)
)
)
SELECT startNode, endNode, path
FROM paths
ORDER BY length(path), path;
| startNode | endNode | path |
|---|---|---|
| 1 | 3 | [1, 3] |
| 1 | 5 | [1, 5] |
| 1 | 8 | [1, 3, 8] |
| 1 | 10 | [1, 3, 10] |
| 1 | 4 | [1, 5, 4] |
| 1 | 8 | [1, 5, 8] |
두 노드 사이의 가중치 없는 최단 경로 열거하기
WITH RECURSIVE는 두 노드 사이의 모든 (가중치 없는) 최단 경로를 찾는 데도 사용할 수 있어요. 종료 노드에 도달하는 즉시 재귀 쿼리가 멈추도록 하기 위해, 새로 추가된 노드들 사이에 종료 노드가 있는지 확인하는 윈도우 함수를 사용해요.
다음 쿼리는 노드 1(시작 노드)과 노드 8(종료 노드) 사이의 모든 가중치 없는 최단 경로를 반환해요:
WITH RECURSIVE paths(startNode, endNode, path, endReached) AS (
SELECT -- 순회의 첫 간선으로 경로 정의
node1id AS startNode,
node2id AS endNode,
[node1id, node2id] AS path,
(node2id = 8) AS endReached
FROM edge
WHERE startNode = 1
UNION ALL
SELECT -- 새 간선을 경로에 연결
paths.startNode AS startNode,
node2id AS endNode,
array_append(path, node2id) AS path,
max(CASE WHEN node2id = 8 THEN 1 ELSE 0 END)
OVER (ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS endReached
FROM paths
JOIN edge ON paths.endNode = node1id
WHERE NOT EXISTS (
FROM paths previous_paths
WHERE list_contains(previous_paths.path, node2id)
)
AND paths.endReached = 0
)
SELECT startNode, endNode, path
FROM paths
WHERE endNode = 8
ORDER BY length(path), path;
| startNode | endNode | path |
|---|---|---|
| 1 | 8 | [1, 3, 8] |
| 1 | 8 | [1, 5, 8] |
recurring으로 유니온 테이블 접근하기
WITH RECURSIVE CTE의 재귀 항(term) 안에서 CTE 이름(예: counter)은 마지막 반복이 생성한 행을 가리켜요. 지금까지 누적된 모든 행(유니온 테이블)에 접근하려면 recurring 스키마 접두사를 사용해요:
WITH RECURSIVE counter(i) AS (
SELECT 1
UNION ALL
SELECT i + 1
FROM counter
WHERE (SELECT max(i) FROM recurring.counter) < 5
)
SELECT *
FROM counter;
| i |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
여기서 recurring.counter는 이전 모든 반복에서 누적된 모든 행에 접근할 수 있게 해주는 반면, FROM 절의 counter는 가장 최근 반복의 행만 포함해요. 이는 종료 조건이나 계산이 직전 반복이 아니라 팔 전체 누적 결과에 의존할 때 유용해요.
USING KEY가 있는 재귀 CTE
더 이상 사용되지 않음: DuckDB 1.5.0은
USING KEYCTE에서 재귀UNION사용을 재귀UNION ALL을 선호하여 더 이상 사용하지 않는 것으로 간주했어요.재귀
UNION은 일반적인 재귀 CTE의 경우처럼 한 반복에서 생성된 모든 행이 다음 반복으로 전달되지 않음을 의미해요. 반대가 사실이므로, 즉 모든 행이 한 반복에서 다음 반복으로 전달되므로, 앞으로 DuckDB의USING KEYCTE는 재귀UNION ALL을 대신 요구할 거예요.DuckDB 1.5.0은 또한
USING KEY문법을 설정하는 새로운 설정을 도입해요.SET deprecated_using_key_syntax = 'DEFAULT'; SET deprecated_using_key_syntax = 'UNION_AS_UNION_ALL';현재
DEFAULT는 두 문법 스타일을 모두 활성화하며, 즉USING KEYCTE에서 재귀UNION과 재귀UNION ALL을 모두 허용해요.DuckDB 1.5.0은 명시적으로 활성화하지 않고도
UNION문법을 지원하는 마지막 릴리스가 될 거예요.DuckDB 2.0.0은 기본적으로
UNION문법을 비활성화해요.DuckDB 2.1.0은
deprecated_using_key_syntax플래그를 제거하고UNION문법을 완전히 폐기할 거예요.
USING KEY는 일반적인 재귀 CTE의 동작을 바꿔요.
각 반복에서 일반적인 재귀 CTE는 결과 행을 유니온 테이블에 추가하며, 이는 궁극적으로 CTE의 전체 결과를 정의해요. 반대로 USING KEY가 있는 CTE는 이전 반복에서 유니온 테이블에 배치된 행을 업데이트할 수 있어요: 현재 반복이 키 k를 가진 행을 생성하면, 유니온 테이블에서 같은 키 k를 가진 행을 대체해요 (사전(dictionary)처럼). 유니온 테이블에 그런 행이 아직 없으면 새 행은 평소대로 유니온 테이블에 추가돼요.
이를 통해 CTE는 유니온 테이블 내용에 대해 세밀한 제어를 할 수 있어요. 추가 전용(append-only) 동작을 피하면 유니온 테이블 크기를 크게 줄일 수 있어요. 이는 쿼리 런타임, 메모리 소비에 도움이 되고, 반복이 아직 진행되는 동안 유니온 테이블에 접근하는 것을 가능하게 해요. WITH RECURSIVE T(...) USING KEY ... CTE에서 테이블 T는 마지막 반복이 추가한 행을 나타내는 반면(재귀 CTE에서 평소와 같이), 테이블 recurring.T는 지금까지 만들어진 유니온 테이블을 나타내요. recurring.T에 대한 참조는 상당히 복잡한 알고리즘을 읽기 쉬운 SQL 코드로 우아하고 관용적으로 변환할 수 있게 해줘요.
예제: USING KEY
이는 USING KEY가 키 열(a)과 페이로드(payload) 열(b)을 가진 재귀 CTE예요. 페이로드 열은 덮어쓸 열에 해당해요. 첫 번째 반복에는 1과 2라는 두 개의 서로 다른 키가 있어요. 이 두 키는 (1, 3)과 (2, 4)라는 두 개의 새 행을 생성해요. 다음 반복에서 우리는 3이라는 새 키를 만들어 새 행을 생성해요. 또한 (2, 3) 행도 생성하는데, 여기서 2는 이전 반복에서 이미 존재하는 키예요. 이는 이전 페이로드 4를 새 페이로드 3으로 덮어써요.
WITH RECURSIVE tbl(a, b) USING KEY (a) AS (
SELECT a, b
FROM (VALUES (1, 3), (2, 4)) t(a, b)
UNION ALL
SELECT a + 1, b
FROM tbl
WHERE a < 3
)
SELECT *
FROM tbl;
| a | b |
|---|---|
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
VALUES 사용하기
CTE의 초기(앵커) 부분에 VALUES 절을 사용할 수 있어요:
WITH RECURSIVE tbl(a, b) USING KEY (a) AS (
VALUES (1, 3), (2, 4)
UNION ALL
SELECT a + 1, b
FROM tbl
WHERE a < 3
)
SELECT *
FROM tbl;
예제: USING KEY 유니온 테이블 참조
유니온 테이블을 사전으로 사용하는 것뿐만 아니라, 이제 쿼리에서 이를 참조할 수도 있어요. 이렇게 하면 직전 반복뿐만 아니라 이전 반복의 결과도 사용할 수 있어요. 이 새로운 기능은 특정 알고리즘을 구현하기 쉽게 만들어줘요.
한 가지 예는 연결 구성 요소(connected components) 알고리즘이에요. 각 노드에 대해 알고리즘은 연결된 가장 낮은 ID를 가진 노드를 결정해요. 이를 위해 유니온 테이블의 항목을 사용해 노드에 대해 찾은 가장 낮은 ID를 추적해요. 새로 들어오는 행에 더 낮은 ID가 포함되면 이 값을 업데이트해요.
CREATE TABLE nodes (id INTEGER);
INSERT INTO nodes VALUES (1), (2), (3), (4), (5), (6), (7), (8);
CREATE TABLE edges (node1id INTEGER, node2id INTEGER);
INSERT INTO edges VALUES
(1, 3), (2, 3), (3, 7), (7, 8), (5, 4), (6, 4);
WITH RECURSIVE connected_components(id, comp) USING KEY (id) AS (
SELECT n.id, n.id AS comp
FROM nodes AS n
UNION ALL (
SELECT DISTINCT ON (previous_iter.id) previous_iter.id, initial_iter.comp
FROM
recurring.connected_components AS previous_iter,
connected_components AS initial_iter,
edges AS e
WHERE ((e.node1id, e.node2id) = (previous_iter.id, initial_iter.id)
OR (e.node2id, e.node1id) = (previous_iter.id, initial_iter.id))
AND initial_iter.comp < previous_iter.comp
ORDER BY initial_iter.id ASC, previous_iter.comp ASC)
)
TABLE connected_components
ORDER BY id;
| id | comp |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 4 |
| 5 | 4 |
| 6 | 4 |
| 7 | 1 |
| 8 | 1 |
제한 사항 (Limitations)
DuckDB는 상호 재귀 CTE(mutually recursive CTEs)를 지원하지 않아요. DuckDB 저장소의 관련 이슈와 토론을 참고하세요.