WITH 절
WITH 절
이 페이지에서는 SQLite의 WITH 절을 설명해요.
출처: 문서
본문
1. 개요
with-clause:
`WITH RECURSIVE cte-table-name AS NOT MATERIALIZED ( select-stmt ) MATERIALIZED ,`
cte-table-name:
`table-name ( column-name ) ,`
select-stmt:
`WITH RECURSIVE common-table-expression , SELECT DISTINCT result-column , ALL FROM table-or-subquery join-clause , WHERE expr GROUP BY expr HAVING expr , WINDOW window-name AS window-defn , VALUES ( expr ) , , compound-operator select-core ORDER BY LIMIT expr ordering-term , OFFSET expr , expr`
common-table-expression:
`table-name ( column-name ) AS NOT MATERIALIZED ( select-stmt ) ,`
compound-operator:
`UNION UNION INTERSECT EXCEPT ALL`
expr:
`literal-value bind-parameter schema-name . table-name . column-name unary-operator expr expr binary-operator expr function-name ( function-arguments ) filter-clause over-clause ( expr ) , CAST ( expr AS type-name ) expr COLLATE collation-name expr NOT LIKE GLOB REGEXP MATCH expr expr ESCAPE expr expr ISNULL NOTNULL NOT NULL expr IS NOT DISTINCT FROM expr expr NOT BETWEEN expr AND expr expr NOT IN ( select-stmt ) expr , schema-name . table-function ( expr ) table-name , NOT EXISTS ( select-stmt ) CASE expr WHEN expr THEN expr ELSE expr END raise-function`
filter-clause:
`FILTER ( WHERE expr )`
function-arguments:
`DISTINCT expr , * ORDER BY ordering-term ,`
literal-value:
`CURRENT_TIMESTAMP numeric-literal string-literal blob-literal NULL TRUE FALSE CURRENT_TIME CURRENT_DATE`
over-clause:
`OVER window-name ( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )`
frame-spec:
`GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE ROWS UNBOUNDED PRECEDING expr PRECEDING CURRENT ROW expr PRECEDING CURRENT ROW expr FOLLOWING expr PRECEDING CURRENT ROW expr FOLLOWING EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS`
raise-function:
`RAISE ( ROLLBACK , expr ) IGNORE ABORT FAIL`
type-name:
`name ( signed-number , signed-number ) ( signed-number )`
signed-number:
`+ numeric-literal -`
join-clause:
`table-or-subquery join-operator table-or-subquery join-constraint`
join-constraint:
`USING ( column-name ) , ON expr`
join-operator:
`NATURAL LEFT OUTER JOIN , RIGHT FULL INNER CROSS`
ordering-term:
`expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST`
result-column:
`expr AS column-alias * table-name . *`
table-or-subquery:
`schema-name . table-name AS table-alias INDEXED BY index-name NOT INDEXED table-function-name ( expr ) , AS table-alias ( select-stmt ) ( table-or-subquery ) , join-clause`
window-defn:
`( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )`
frame-spec:
`GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE ROWS UNBOUNDED PRECEDING expr PRECEDING CURRENT ROW expr PRECEDING CURRENT ROW expr FOLLOWING expr PRECEDING CURRENT ROW expr FOLLOWING EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS`
공통 테이블 식(Common Table Expression, CTE)은 단일 SQL 문이 실행되는 동안에만 존재하는 임시 뷰처럼 동작해요. 공통 테이블 식에는 "일반(ordinary)"과 "재귀(recursive)" 두 종류가 있어요. 일반 공통 테이블 식은 하위 질의를 주 SQL 문에서 분리해 내어 질의를 더 이해하기 쉽게 만드는 데 유용해요. 재귀 공통 테이블 식은 트리와 그래프에 대한 계층적 또는 재귀적 질의를 수행할 수 있는 기능을 제공해요. 이 기능은 SQL 언어에서 다른 방법으로는 제공되지 않아요.
모든 공통 테이블 식(일반 및 재귀)은 SELECT, INSERT, DELETE 또는 UPDATE 문 앞에 WITH 절을 붙여서 만들어요. 하나의 WITH 절은 하나 이상의 공통 테이블 식을 지정할 수 있으며, 그중 일부는 일반이고 일부는 재귀일 수 있어요.
2. 일반 공통 테이블 식
일반 공통 테이블 식은 단일 문이 실행되는 동안 존재하는 뷰처럼 동작해요. 일반 공통 테이블 식은 하위 질의를 분리해 내고 전체 SQL 문을 더 읽고 이해하기 쉽게 만드는 데 유용해요.
WITH 절은 RECURSIVE 키워드를 포함하더라도 일반 공통 테이블 식을 포함할 수 있어요. RECURSIVE를 사용한다고 해서 공통 테이블 식이 재귀적이어야 하는 것은 아니에요.
3. 재귀 공통 테이블 식
재귀 공통 테이블 식은 트리나 그래프를 탐색하는 질의를 작성하는 데 사용할 수 있어요. 재귀 공통 테이블 식은 일반 공통 테이블 식과 기본 문법이 같지만, 다음과 같은 추가 속성이 있어요:
- "select-stmt"는 복합 SELECT여야 해요. 즉, CTE 본문은 UNION, UNION ALL, INTERSECT 또는 EXCEPT 같은 복합 연산자로 구분된 두 개 이상의 개별 SELECT 문이어야 해요.
- 복합 SELECT를 구성하는 개별 SELECT 문 중 하나 이상은 "재귀적"이어야 해요. FROM 절에 CTE 테이블(AS 절의 왼쪽에 이름이 있는 테이블)에 대한 참조가 정확히 하나 포함되어 있으면 그 SELECT 문은 재귀적인 거예요.
- 복합 SELECT에서 하나 이상의 SELECT 문은 비재귀적이어야 해요.
- 모든 비재귀 SELECT 문은 재귀 SELECT 문보다 먼저 나와야 해요.
- 재귀 SELECT 문은 비재귀 SELECT 문 및 서로 간에 UNION 또는 UNION ALL 연산자로 구분되어야 해요. 재귀 SELECT 문이 두 개 이상이면, 첫 번째 재귀 SELECT 문과 마지막 비재귀 SELECT 문을 구분하는 것과 같은 연산자로 서로 구분되어야 해요.
- 재귀 SELECT 문은 집계 함수나 윈도우 함수를 사용할 수 없어요.
다시 말하면, 재귀 공통 테이블 식은 다음과 같은 형태여야 해요:
recursive-cte:
`cte-table-name AS ( initial-select UNION ALL recursive-select ) UNION`
cte-table-name:
`table-name ( column-name ) ,`
위 다이어그램에서 initial-select는 하나 이상의 비재귀 SELECT 문을 의미하고, recursive-select는 하나 이상의 재귀 SELECT 문을 의미해요. 가장 일반적인 경우는 initial-select가 정확히 하나이고 recursive-select가 정확히 하나인 경우지만, 각각 여러 개여도 허용돼요.
재귀 공통 테이블 식에서 cte-table-name이 이름을 지정하는 테이블을 "재귀 테이블(recursive table)"이라고 불러요. 위의 recursive-cte 다이어그램에서 재귀 테이블은 recursive-select의 각 최상위 SELECT 문의 FROM 절에 정확히 한 번만 나타나야 하며, 하위 질의를 포함하여 initial-select나 recursive-select의 다른 곳에는 나타나서는 안 돼요. initial-select는 복합 SELECT일 수 있지만 ORDER BY, LIMIT 또는 OFFSET을 포함할 수 없어요. recursive-select도 복합 SELECT일 수 있지만, 해당 복합 SELECT의 모든 요소는 initial-select와 recursive-select를 구분하는 것과 동일한 UNION 또는 UNION ALL 연산자로 구분되어야 한다는 제약이 있어요. recursive-select는 ORDER BY, LIMIT 및/또는 OFFSET을 포함할 수 있지만 집계 함수나 윈도우 함수는 사용할 수 없어요.
recursive-select가 복합 SELECT일 수 있는 기능은 버전 3.34.0(2020-12-01)에서 추가되었어요. 이전 버전의 SQLite에서 recursive-select는 단일 단순 SELECT 문만 가능했어요.
재귀 테이블의 내용을 계산하는 기본 알고리즘은 다음과 같아요:
- initial-select를 실행하고 결과를 큐에 추가해요.
- 큐가 비어 있지 않은 동안: a. 큐에서 행 하나를 추출해요. b. 그 행 하나를 재귀 테이블에 삽입해요. c. 방금 추출한 행 하나가 재귀 테이블의 유일한 행이라고 가정하고 recursive-select를 실행해서 모든 결과를 큐에 추가해요.
위의 기본 절차는 다음 추가 규칙에 따라 수정될 수 있어요:
-
initial-select와 recursive-select를 UNION 연산자로 연결하면, 이전에 큐에 추가된 것과 동일한 행이 없을 때만 큐에 행을 추가해요. 반복된 행은 재귀 단계에서 이미 큐에서 추출되었더라도 큐에 추가되기 전에 버려져요. 연산자가 UNION ALL이면 initial-select와 recursive-select 양쪽에서 생성된 모든 행이 반복이더라도 항상 큐에 추가돼요. 행의 반복 여부를 판단할 때 NULL 값은 서로 같게 비교되며 다른 어떤 값과도 같지 않게 비교돼요.
-
LIMIT 절이 있으면 2b 단계에서 재귀 테이블에 추가될 수 있는 최대 행 수를 결정해요. 한도에 도달하면 재귀가 중지돼요. LIMIT가 0이면 재귀 테이블에 행이 전혀 추가되지 않으며, 음수이면 재귀 테이블에 무제한으로 행을 추가할 수 있어요.
-
OFFSET 절이 있고 양수 값 N을 가지면 처음 N개 행이 재귀 테이블에 추가되지 않아요. 처음 N개 행은 여전히 recursive-select에 의해 처리되며, 다만 재귀 테이블에 추가되지 않을 뿐이에요. 모든 OFFSET 행이 건너뛰어지기 전에는 행이 LIMIT 충족에 계산되지 않아요.
-
ORDER BY 절이 있으면 2a 단계에서 큐에서 행을 추출하는 순서를 결정해요. ORDER BY 절이 없으면 행이 추출되는 순서는 정의되지 않아요. (현재 구현에서는 ORDER BY 절을 생략하면 큐가 FIFO가 되지만, 애플리케이션은 이 사실에 의존해서는 안 돼요. 변경될 수 있기 때문이에요.)
3.1. 재귀 쿼리 예제
다음 쿼리는 1부터 1000000 사이의 모든 정수를 반환해요.
WITH RECURSIVE
cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000)
SELECT x FROM cnt;
이 쿼리가 어떻게 동작하는지 생각해 봐요. initial-select가 먼저 실행되어 단일 컬럼 "1"을 가진 한 행을 반환해요. 이 행 하나가 큐에 추가돼요. 2a 단계에서 그 행이 큐에서 추출되어 "cnt"에 추가돼요. 그런 다음 2c 단계에 따라 recursive-select가 실행되어 값이 "2"인 새 행 하나를 생성해 큐에 추가해요. 큐에는 여전히 행이 하나 있으므로 2단계가 반복돼요. "2" 행은 2a와 2b 단계를 거쳐 추출되어 재귀 테이블에 추가돼요. 그런 다음 2가 포함된 행이 마치 재귀 테이블의 전체 내용인 것처럼 사용되고 recursive-select가 다시 실행되어 값이 "3"인 행이 큐에 추가돼요. 이 과정이 999999번 반복된 후, 마침내 2a 단계에서 큐에 남은 유일한 값이 1000000을 포함하는 행이 돼요. 그 행이 추출되어 재귀 테이블에 추가돼요. 하지만 이번에는 WHERE 절 때문에 recursive-select가 어떤 행도 반환하지 않아서 큐는 비어 있는 채로 남고 재귀는 종료돼요.
최적화 참고:
위 논의에서 "행을 재귀 테이블에 삽입한다"와 같은 표현은 개념적으로 이해해야지 문자 그대로 받아들여서는 안 돼요. SQLite가 백만 개의 행을 담은 거대한 테이블을 누적한 다음, 결과를 생성하기 위해 다시 돌아가 그 테이블을 처음부터 끝까지 스캔하는 것처럼 들리지만, 실제로 일어나는 일은 이래요. 쿼리 최적화 프로그램은 "cnt" 재귀 테이블의 값이 한 번만 사용된다는 것을 알아채요. 그래서 각 행이 재귀 테이블에 추가될 때마다 그 행은 즉시 메인 SELECT 문의 결과로 반환된 다음 버려져요. SQLite는 백만 개의 행을 담은 임시 테이블을 축적하지 않아요. 위 예제를 실행하는 데 필요한 메모리는 아주 적어요. 하지만 만약 예제에서 UNION ALL 대신 UNION을 사용했다면, SQLite는 중복을 확인하기 위해 이전에 생성된 모든 내용을 계속 유지해야 했을 거예요. 이런 이유로 프로그래머는 가능하면 UNION 대신 UNION ALL을 사용하는 것이 좋아요.
다음은 앞선 예제의 변형이에요.
WITH RECURSIVE
cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
LIMIT 1000000
)
SELECT x FROM cnt;
이 변형에는 두 가지 차이점이 있어요. initial-select가 "VALUES(1)" 대신 "SELECT 1"이에요. 하지만 이것은 정확히 같은 내용을 다른 문법으로 표현한 것에 불과해요. 또 다른 변경점은 재귀가 WHERE 절이 아닌 LIMIT에 의해 중단된다는 거예요. LIMIT을 사용하면 (쿼리 최적화 프로그램 덕분에) 백만 번째 행이 "cnt" 테이블에 추가되고 메인 SELECT에 의해 반환될 때, 큐에 남은 행이 몇 개든 관계없이 재귀가 즉시 중단돼요. 더 복잡한 쿼리에서는 WHERE 절이 결국 큐를 비우고 재귀를 종료시키도록 보장하기 어려운 경우가 있어요. 하지만 LIMIT 절은 항상 재귀를 중단시켜요. 따라서 재귀 크기의 상한을 알고 있다면 안전장치로 항상 LIMIT 절을 포함시키는 것이 좋은 습관이에요.
3.2. 계층형 쿼리 예제
조직의 구성원과 그 조직 내의 지휘 체계를 설명하는 테이블을 생각해 봐요.
CREATE TABLE org(
name TEXT PRIMARY KEY,
boss TEXT REFERENCES org,
height INT,
-- other content omitted
);
조직의 모든 구성원은 이름을 갖고 있어요. 대부분의 구성원은 단 한 명의 상사를 갖고 있어요. (조직 전체의 수장은 "boss" 필드가 NULL이에요.) "org" 테이블의 행들은 트리를 형성해요.
다음은 Alice 자신을 포함해 Alice의 조직에 속한 모든 사람의 평균 height를 계산하는 쿼리예요.
WITH RECURSIVE
works_for_alice(n) AS (
VALUES('Alice')
UNION
SELECT name FROM org, works_for_alice
WHERE org.boss=works_for_alice.n
)
SELECT avg(height) FROM org
WHERE org.name IN works_for_alice;
다음 예제는 단일 WITH 절에서 두 개의 common table expression을 사용해요. 다음 테이블은 가계도를 기록해요.
CREATE TABLE family(
name TEXT PRIMARY KEY,
mom TEXT REFERENCES family,
dad TEXT REFERENCES family,
born DATETIME,
died DATETIME -- NULL if still alive
-- other content
);
"family" 테이블은 앞선 "org" 테이블과 비슷하지만, 각 구성원에게 부모가 둘씩 있다는 점이 달라요. 우리는 Alice의 살아있는 모든 조상을 나이가 많은 순서부터 어린 순서로 알고 싶어요. 먼저 일반적인 common table expression인 "parent_of"가 정의돼요. 이 일반 CTE는 어떤 개인의 모든 부모를 찾는 데 사용할 수 있는 뷰예요. 그런 다음 이 일반 CTE가 "ancestor_of_alice" 재귀 CTE에서 사용돼요. 마지막 쿼리에서는 그 재귀 CTE가 사용돼요.
WITH RECURSIVE
parent_of(name, parent) AS
(SELECT name, mom FROM family UNION SELECT name, dad FROM family),
ancestor_of_alice(name) AS
(SELECT parent FROM parent_of WHERE name='Alice'
UNION ALL
SELECT parent FROM parent_of JOIN ancestor_of_alice USING(name))
SELECT family.name FROM ancestor_of_alice, family
WHERE ancestor_of_alice.name=family.name
AND died IS NULL
ORDER BY born;
3.3. 그래프에 대한 쿼리
각 노드가 정수로 식별되고 간선이 다음과 같은 테이블로 정의되는 무방향 그래프가 있다고 가정해 봐요.
CREATE TABLE edge(aa INT, bb INT);
CREATE INDEX edge_aa ON edge(aa);
CREATE INDEX edge_bb ON edge(bb);
인덱스는 필수가 아니지만, 큰 그래프에서는 성능에 도움이 돼요. 노드 59에 연결된 그래프의 모든 노드를
3.5. 기상천외한 재귀 쿼리 예제
다음 쿼리는 만델브로 집합의 근삿값을 계산하고 그 결과를 ASCII 아트로 출력해요.
WITH RECURSIVE
xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
m(iter, cx, cy, x, y) AS (
SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
UNION ALL
SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
WHERE (x*x + y*y) < 4.0 AND iter<28
),
m2(iter, cx, cy) AS (
SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
),
a(t) AS (
SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')
FROM m2 GROUP BY cy
)
SELECT group_concat(rtrim(t),x'0a') FROM a;
이 쿼리에서 "xaxis" 및 "yaxis" CTE는 만델브로 집합을 근사할 점들의 격자를 정의해요. "m(iter,cx,cy,x,y)" CTE의 각 행은 "iter"번 반복한 후, cx,cy에서 시작한 만델브로 반복이 점 x,y에 도달했음을 뜻해요. 이 예제의 반복 횟수는 28로 제한되어 있어요. (따라서 계산 해상도는 심각하게 제한되지만, 저해상도 ASCII 아트 출력에는 충분해요.) "m2(iter,cx,cy)" CTE는 cx,cy에서 시작했을 때 도달한 최대 반복 횟수를 담아요. 마지막으로 "a(t)" CTE의 각 행은 출력 ASCII 아트의 한 줄 문자열을 담아요. 끝의 SELECT 문은 그냥 "a" CTE에서 ASCII 아트의 모든 줄을 하나씩 가져와요.
SQLite 명령줄 셸에서 위 쿼리를 실행하면 다음과 같은 결과가 나와요.
....#
..#*..
..+####+.
.......+####.... +
..##+*##########+.++++
.+.##################+.
.............+###################+.+
..++..#.....*#####################+.
...+#######++#######################.
....+*################################.
#############################################...
....+*################################.
...+#######++#######################.
..++..#.....*#####################+.
.............+###################+.+
.+.##################+.
..##+*##########+.++++
.......+####.... +
..+####+.
..#*..
....#
+.
이 다음 쿼리는 스도쿠 퍼즐을 풀어요. 퍼즐 상태는 퍼즐 상자의 항목을 왼쪽에서 오른쪽으로, 그런 다음 위에서 아래로 행 단위로 읽어 만든 81자 문자열로 정의돼요. 퍼즐의 빈 칸은 "." 문자로 표시해요. 따라서 입력 문자열:
53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79
은(는) 다음과 같은 퍼즐에 해당해요.
| 5 | 3 | 7 | |||||||
| 6 | 1 | 9 | 5 | ||||||
| 9 | 8 | 6 | |||||||
| 8 | 6 | 3 | |||||||
| 4 | 8 | 3 | 1 | ||||||
| 7 | 2 | 6 | |||||||
| 6 | 2 | 8 | |||||||
| 4 | 1 | 9 | 5 | ||||||
| 8 | 7 | 9 |
퍼즐을 푸는 쿼리는 다음과 같아요.
WITH RECURSIVE
input(sud) AS (
VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
),
digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
x(s, ind) AS (
SELECT sud, instr(sud, '.') FROM input
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1
FROM digits AS lp
WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
)
)
SELECT s FROM x WHERE ind=0;
"input" CTE는 입력 퍼즐을 정의해요. "digits" CTE는 1부터 9까지의 모든 숫자를 담는 테이블을 정의해요. 퍼즐을 푸는 작업은 "x" CTE가 수행해요. x(s,ind)의 항목