본문으로 건너뛰기
번역된 페이지

이 페이지는 AI의 도움을 받아 영어에서 번역되었습니다. 두 내용이 다를 경우 영어 버전을 기준으로 합니다.

WITH (CTEs)

뒤이은 SELECT, INSERT, UPDATE 또는 DELETE 문에서 사용할 공통 테이블 식(CTE)을 정의합니다. CTE는 쿼리가 실행되는 동안에만 존재하는, 이름이 지정된 임시 결과 집합 역할을 합니다.

참고 항목: [SELECT] [INSERT] [UPDATE] [DELETE]


구문​

withClause::=

WITHRECURSIVEcteDefinition','cteDefinition

cteDefinition::=

cteName'('columnList')'AS'('selectStatement')'

   

WITH [RECURSIVE] cte_name [(column_name, ...)] AS (
select_statement
)
[, cte_name [(column_name, ...)] AS (
select_statement
)]
SELECT ... FROM cte_name ...;

인수​

RECURSIVE CTE가 자기 자신을 참조할 수 있게 하는 선택적 키워드입니다. 이를 통해 계층적 데이터 순회와 같은 재귀 쿼리를 작성할 수 있습니다.

cte_name CTE에 할당되는 이름입니다. 이 이름은 주 쿼리에서 테이블처럼 참조할 수 있습니다.

column_name 선택 사항입니다. CTE 결과 집합의 열 이름입니다. 지정하지 않으면 열 이름은 SELECT 문에서 파생됩니다.

select_statement CTE의 결과 집합을 정의하는 쿼리입니다.

반환 값​

CTE는 값을 직접 반환하지 않습니다. 주 쿼리에서 참조할 수 있는 임시 결과 집합을 정의합니다.


예제​

복잡한 쿼리 구성을 위한 기본 CTE​

CTE를 사용하면 복잡한 쿼리를 이름이 지정된 논리적 구성 요소로 나누어 단순화할 수 있습니다.

-- Calculate moving average of commit activity
WITH weekly_totals AS (
SELECT
week,
SUM(json_each.value) as commits_this_week
FROM github.repos.stats_commit_activity, JSON_EACH(days)
WHERE owner = 'stackql'
AND repo = 'stackql'
GROUP BY week
)
SELECT
week,
commits_this_week,
ROUND(AVG(commits_this_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg,
SUM(commits_this_week) OVER (ORDER BY week) as cumulative_commits
FROM weekly_totals
ORDER BY week;

UNION ALL 연산자를 사용한 여러 소스의 데이터 결합​

CTE는 분석을 수행하기 전에 여러 소스의 데이터를 결합하는 데 유용합니다.

-- Rank total contributions across multiple repos
WITH all_contributors AS (
SELECT login, contributions, html_url
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql'
UNION ALL
SELECT login, contributions, html_url
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql-deploy'
UNION ALL
SELECT login, contributions, html_url
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
)
SELECT
DENSE_RANK() OVER (ORDER BY SUM(contributions) DESC) as rank,
login,
SUM(contributions) as total_contributions,
MAX(html_url) as html_url
FROM all_contributors
GROUP BY login
ORDER BY total_contributions DESC;

순위 지정을 위한 CTE와 윈도우 함수 사용​

-- Rank contributors within each repo using CTE and window functions
WITH repo_contributors AS (
SELECT
repo,
login,
contributions,
RANK() OVER (PARTITION BY repo ORDER BY contributions DESC) as rank_in_repo,
SUM(contributions) OVER (PARTITION BY repo) as repo_total_contributions
FROM (
SELECT 'stackql' as repo, login, contributions
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql'
UNION ALL
SELECT 'stackql-provider-registry' as repo, login, contributions
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
) t
)
SELECT
repo,
login,
contributions,
rank_in_repo
FROM repo_contributors
ORDER BY repo, rank_in_repo;

단일 쿼리의 여러 CTE​

쉼표로 구분하여 여러 CTE를 정의할 수 있습니다.

-- Analyze issues and releases together
WITH open_issues AS (
SELECT number, title, created_at
FROM github.issues.issues
WHERE owner = 'stackql'
AND repo = 'stackql'
AND state = 'open'
),
recent_releases AS (
SELECT tag_name, published_at
FROM github.repos.releases
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY published_at DESC
LIMIT 5
)
SELECT
'Open Issues' as category,
COUNT(*) as count
FROM open_issues
UNION ALL
SELECT
'Recent Releases' as category,
COUNT(*) as count
FROM recent_releases;

재귀 CTE​

재귀 CTE를 사용하면 CTE가 자기 자신을 참조할 수 있으므로, 계층적 데이터 구조나 그래프 형태의 데이터 구조를 순회하는 쿼리를 작성할 수 있습니다.

WITH RECURSIVE cte_name (columns) AS (
-- Base case (anchor member)
SELECT ...
UNION ALL
-- Recursive case (references cte_name)
SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;
팁

재귀 CTE는 조직도, 파일 시스템, 종속성 트리와 같은 계층적 데이터에 효과적입니다. 무한 루프를 방지하려면 재귀 부분에 항상 종료 조건을 포함하세요.

모범 사례​

  1. 가독성을 위한 CTE 사용: 복잡한 쿼리를 이름이 지정된 논리적 구성 요소로 나눕니다.
  2. 윈도우 함수와 결합: CTE는 윈도우 함수와 함께 사용하면 고급 분석에 효과적입니다.
  3. 여러 CTE 연결: 뒤의 CTE에서 앞의 CTE를 참조하여 복잡한 로직을 단계별로 구성합니다.
  4. 성능 고려: CTE는 가독성을 높이지만 매우 큰 CTE는 성능에 영향을 줄 수 있습니다. 실제 데이터 양으로 테스트하세요.

자세한 내용은 https://sqlite.org/lang_with.html 문서를 참조하세요.