SQLite CTE: WITH 절로 쿼리 깔끔하게 짜기

CTE는 이름이 붙은 서브쿼리다

**CTE(Common Table Expression)**는 쉽게 말해 이름을 붙인 서브쿼리다. SELECT 안에 또 SELECT를 중첩해서 쓰는 대신, 쿼리 맨 위에 WITH 절로 따로 빼서 이름을 지어주고, 본 쿼리에서는 그 이름을 마치 테이블처럼 가져다 쓰는 방식이다.

기본 문법은 항상 이런 형태다:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

위에서 아래로 자연스럽게 읽으면 됩니다. 먼저 customer_totals라는 이름의 결과 집합을 정의하고, 그다음 그 결과를 조회하는 구조죠. CTE는 해당 SQL 문이 실행되는 동안에만 존재하는 임시 뷰처럼 동작합니다.

CTE 없이 같은 쿼리 작성하기

CTE가 어떤 부분을 대체해 주는지 감을 잡을 수 있도록, 똑같은 로직을 서브쿼리로 풀어 쓰면 다음과 같습니다.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

같은 결과가 나옵니다. 하지만 읽는 순서를 한번 보세요. 괄호 안으로 시선이 먼저 들어가서 뭘 계산하는지 파악한 다음, 다시 바깥으로 나와야 합니다. 반면 CTE 버전은 작업이 실제로 일어나는 순서대로 읽힙니다 — 중간 결과를 먼저 정의하고, 그걸 사용하는 식이죠. 짧은 쿼리에서는 큰 차이가 없습니다. 그런데 단계가 서너 개로 늘어나는 쿼리라면, 훑어볼 수 있는 코드와 한참 해석해야 하는 코드의 차이로 벌어집니다.

하나의 쿼리에 여러 CTE 사용하기 (sqlite 다중 cte)

CTE는 쉼표로 구분해서 여러 개를 이어 쓸 수 있습니다. 뒤에 오는 CTE는 앞서 정의한 CTE를 참조할 수 있어서, 이름 붙은 단계들로 파이프라인을 차곡차곡 쌓아 올릴 수 있죠:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

WITH은 한 번만 쓰고, 그 뒤에 CTE 정의들을 쉼표로 구분해서 나열합니다. 두 번째 CTE인 big_spenders는 첫 번째 CTE인 customer_totals를 마치 테이블처럼 읽어 옵니다. 메인 SELECT 문은 마지막 CTE 정의 뒤에 옵니다.

자주 하는 실수 하나: 두 번째 CTE 앞에 WITH을 또 붙이는 경우입니다. 이렇게 하면 문법 오류가 납니다. WITH은 한 번만 써도 모든 CTE에 적용된다는 점을 기억하세요.

CTE를 여러 번 참조하기

바로 이 지점에서 CTE가 서브쿼리보다 확실히 앞섭니다. 같은 중간 결과를 두 군데에서 써야 할 때, CTE는 한 번만 계산해 두고 두 번 참조할 수 있게 해줍니다.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

CTE를 두 번 참조하고 있죠. 한 번은 평균을 구할 때, 또 한 번은 메인 데이터 소스로요. 만약 CTE가 없었다면 GROUP BY 쿼리를 그대로 복붙해야 하고, 쿼리에 변경이 생길 때마다 두 군데를 모두 수정해야 합니다.

INSERT, UPDATE, DELETE에서 CTE 활용하기

CTE는 SELECT에서만 쓸 수 있는 게 아닙니다. INSERT, UPDATE, DELETE 앞에 WITH 절을 붙이면, 이름이 있는 서브쿼리를 쓰기 작업에서도 그대로 활용할 수 있어요:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

CTE는 어떤 행에 표시를 남길지 정의하고, INSERT ... SELECT는 그걸 데이터 출처로 사용합니다. DELETE FROM ... WHERE id IN (SELECT id FROM cte) 형태로도 똑같이 쓸 수 있어서, 삭제 대상을 고르는 로직이 복잡할 때 단계적으로 처리하기 좋습니다.

CTE를 언제 써야 할까

대략 이런 기준으로 판단하면 됩니다.

• 쿼리가 여러 논리 단계로 나뉠 때. 집계하고, 집계 결과로 다시 필터링하고, 그 결과를 조인하는 식의 파이프라인이라면, 단계마다 CTE 하나씩 두는 편이 훨씬 읽기 좋습니다.
• 같은 서브쿼리를 두 번 이상 써야 할 때. 한 번만 정의해 놓고 여러 곳에서 참조하면 됩니다.
• 서브쿼리에 이름을 붙일 만한 가치가 있을 때. 서브쿼리 위에 "이게 뭘 뜻하는 거다"라고 주석을 달아야 할 정도라면, CTE 이름 자체가 그 주석 역할을 하고, 문법이 그 의미를 강제해 줍니다.
• 재귀 쿼리를 써야 할 때. 재귀는 오직 WITH RECURSIVE로만 가능합니다. 이 부분은 다음 페이지에서 다룹니다.

반대로 굳이 CTE까지 갈 필요가 없는 경우도 있습니다.

• 한 군데에서만 쓰는 단순한 서브쿼리 하나. WHERE id IN (SELECT id FROM ...) 정도면 그대로 두는 게 낫습니다.
• 성능이 중요한 쿼리에서, 이미 로직을 인라인으로 풀어 쓰는 게 더 빠르다고 확인된 경우. SQLite는 다른 DB들에 비해 CTE를 최적화 차단막(optimization fence)으로 취급하는 정도가 덜한 편이긴 하지만, 핫 패스에서는 EXPLAIN QUERY PLAN으로 직접 확인해 보는 게 좋습니다.

실전 예제로 정리

지금까지 본 내용을 한데 모아서, 각 고객의 최대 주문 금액과 그 고객의 평균 주문 금액을 비교하는 작은 리포트를 만들어 봅시다.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

CTE 두 개가 각각 하나의 역할만 맡고, 마지막 SELECT에서 결과를 다듬어 출력하는 구조입니다. 쿼리를 위에서 아래로 읽으면 각 단계를 따로따로 이해할 수 있죠. 사실 이게 바로 CTE를 쓰는 이유이기도 합니다.

다음 주제: 재귀 CTE

지금까지 살펴본 건 모두 일반 CTE, 즉 이름이 붙은 서브쿼리를 한 번만 평가하는 형태였습니다. SQLite는 여기에 더해 WITH RECURSIVE도 지원하는데요, CTE가 자기 자신을 참조하면서 계층 구조를 따라가거나, 연속된 값을 만들어 내거나, 그래프를 순회할 수 있게 해 줍니다. 다음 페이지에서 본격적으로 다뤄 보겠습니다.