[SQLite] WITH (CTE)

SQLite에서 Common Table Expressions (CTE)는 복잡한 쿼리를 보다 명확하게 표현할 수 있게 해주는 강력한 기능입니다. CTE는 일시적인 결과 집합을 정의하는데 사용되며, 이 결과 집합은 쿼리의 나머지 부분에서 하나 이상의 번 참조될 수 있습니다. CTE는 복잡한 쿼리를 더 읽기 쉽고 관리하기 쉬운 여러 단계로 나눌 수 있도록 도와줍니다.

 

일반 CTE

일반 CTE는 단순히 쿼리 내에서 재사용될 수 있는 임시 결과 집합을 정의합니다. 이는 주로 데이터를 조직하고, 쿼리를 더 읽기 쉽게 만드는데 사용됩니다.

WITH RegionalSales AS (
    SELECT Region, SUM(Sales) AS TotalSales
    FROM SalesRecords
    GROUP BY Region
)
SELECT Region, TotalSales
FROM RegionalSales
WHERE TotalSales > 1000000;

이 예시에서 RegionalSales CTE는 각 지역별 총매출을 계산하고, 그 결과를 사용하여 총매출이 100만 이상인 지역만을 조회합니다.

 

재귀 CTE

재귀 CTE는 자기 자신을 참조하여 결과를 반복적으로 구축하는 CTE입니다. 이는 계층적 데이터 또는 순차 데이터를 조회하고 조작하는 데 유용하게 사용됩니다.

WITH RECURSIVE NumberSeries(Number) AS (
    SELECT 1
    UNION ALL
    SELECT Number + 1 FROM NumberSeries
    WHERE Number < 10
)
SELECT Number FROM NumberSeries;

이 예시에서 재귀 CTE NumberSeries는 1부터 10까지의 숫자 시리즈를 생성합니다. 재귀적으로 자기 자신을 참조하여 결과 집합을 구축합니다.

 

MATERIALIZED

MATERIALIZED 키워드는 CTE의 결과를 명시적으로 물리적 임시 테이블에 저장하도록 지시합니다. 이는 CTE가 여러 번 참조될 때 성능을 최적화하는 데 도움이 될 수 있습니다. 반대로 NOT MATERIALIZED 키워드는 CTE의 결과가 저장되지 않고, 필요할 때마다 다시 계산되어야 함을 나타냅니다.

WITH RECURSIVE Factorials(N, Fact) AS MATERIALIZED (
    SELECT 1, 1
    UNION ALL
    SELECT N+1, (N+1)*Fact FROM Factorials WHERE N < 10
)
SELECT N, Fact FROM Factorials;

위 예시에서 Factorials CTE는 재귀적으로 팩토리얼 값을 계산합니다.

이 예시에서 MATERIALIZED를 사용한 것은 CTE가 단 한 번만 참조되기 때문에 성능상의 큰 이점을 제공하지 않습니다. 그러나 CTE 결과가 여러 번 참조되는 복잡한 쿼리에서 MATERIALIZED 사용은 중요한 성능 최적화를 가져올 수 있습니다.