이 페이지는 AI의 도움을 받아 영어에서 번역되었습니다. 두 내용이 다를 경우 영어 버전을 기준으로 합니다.
윈도우 함수
윈도우 함수는 현재 행과 관련된 행 집합에 걸쳐 계산을 수행합니다. 행 그룹에 대해 단일 결과를 반환하는 집계 함수와 달리, 윈도우 함수는 관련 행의 "윈도우"를 고려하면서 각 행에 대해 값을 반환합니다.
참고 항목:
[SELECT] [WITH (CTEs)] [ 윈도우 함수 참조 ]
구문
윈도우 함수는 OVER 절을 사용하여 윈도우 사양을 정의합니다.
SELECT <windowFunction> OVER ( <windowSpec> ) FROM <multipartIdentifier>;
windowFunctionCall::=
윈도우 함수
윈도우 함수는 윈도우 사양으로 정의된 행 집합에 걸쳐 계산을 수행하는 특수 함수입니다. 전용 윈도우 함수(ROW_NUMBER, LAG 등)일 수도 있고, OVER 절과 함께 사용하는 집계 함수(SUM, COUNT 등)일 수도 있습니다.
{ <aggregateFunction> | ROW_NUMBER() | RANK() | DENSE_RANK() | PERCENT_RANK()
| CUME_DIST() | NTILE(<int>) | LAG(<expr> [,<offset> [,<default>]])
| LEAD(<expr> [,<offset> [,<default>]]) | FIRST_VALUE(<expr>)
| LAST_VALUE(<expr>) | NTH_VALUE(<expr>, <int>) }
windowFunction::=
윈도우 사양
윈도우 사양은 윈도우 함수 계산을 위해 행을 분할하고, 정렬하고, 프레임을 지정하는 방법을 정의합니다. PARTITION BY 절은 행을 그룹으로 나누고, ORDER BY 절은 각 파티션 내의 순서를 결정하며, 선택적 프레임 절은 현재 행을 기준으로 어떤 행을 계산에 포함할지 지정합니다.
[ <windowName> ]
[ PARTITION BY <fieldList> ]
[ ORDER BY <fieldList> [ ASC | DESC ] ]
[ { ROWS | RANGE | GROUPS }
{ UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW }
| { ROWS | RANGE | GROUPS } BETWEEN
{ UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW | <int> FOLLOWING }
AND
{ <int> PRECEDING | CURRENT ROW | <int> FOLLOWING | UNBOUNDED FOLLOWING } ]
windowSpec::=
사용 가능한 윈도우 함수
| 범주 | 함수 |
|---|---|
| 순위 | ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() |
| 오프셋 | LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE() |
| 분포 | PERCENT_RANK(), CUME_DIST() |
| 집계 | SUM(), COUNT(), AVG(), MIN(), MAX() (OVER 절과 함께 사용) |
예제
ROW_NUMBER, RANK, DENSE_RANK 함수를 사용한 순위 지정
순위 함수를 사용하여 열 값을 기준으로 행에 순위를 지정합니다.
-- Rank contributors by contribution count
SELECT
login,
contributions,
ROW_NUMBER() OVER (ORDER BY contributions DESC) as row_num,
RANK() OVER (ORDER BY contributions DESC) as rank,
DENSE_RANK() OVER (ORDER BY contributions DESC) as dense_rank
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';
누계 및 백분율
SUM, COUNT 같은 집계 함수를 윈도우 함수로 사용하여 누계와 백분율을 계산할 수 있습니다.
-- Running total and percentage of contributions
SELECT
login,
contributions,
SUM(contributions) OVER (ORDER BY contributions DESC) as running_total,
SUM(contributions) OVER () as total_contributions,
ROUND(100.0 * contributions / SUM(contributions) OVER (), 2) as pct_of_total
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';
LAG 및 LEAD 함수를 사용한 행 비교
LAG 함수와 LEAD 함수를 사용하여 이전 행이나 다음 행의 값에 액세스합니다.
-- Compare each release to previous release
SELECT
tag_name,
name,
published_at,
LAG(tag_name, 1) OVER (ORDER BY published_at) as previous_release,
LEAD(tag_name, 1) OVER (ORDER BY published_at) as next_release,
julianday(published_at) - julianday(LAG(published_at, 1) OVER (ORDER BY published_at)) as days_since_last_release
FROM github.repos.releases
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY published_at;
NTILE 함수를 사용한 그룹 생성
NTILE 함수를 사용하여 행을 지정된 수의 그룹으로 나눕니다.
-- Group contributors into quartiles
SELECT
login,
contributions,
NTILE(4) OVER (ORDER BY contributions DESC) as quartile,
CASE NTILE(4) OVER (ORDER BY contributions DESC)
WHEN 1 THEN 'Top Contributors'
WHEN 2 THEN 'Active Contributors'
WHEN 3 THEN 'Moderate Contributors'
WHEN 4 THEN 'Occasional Contributors'
END as tier
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';
PARTITION BY 절을 사용한 그룹별 분석
PARTITION BY 절을 사용하여 그룹 내에서 윈도우 계산을 수행합니다.
-- Track cumulative issues by state
SELECT
number,
title,
state,
created_at,
ROW_NUMBER() OVER (ORDER BY created_at) as issue_sequence,
COUNT(*) OVER (ORDER BY created_at) as cumulative_issues,
COUNT(*) OVER (PARTITION BY state ORDER BY created_at) as cumulative_by_state
FROM github.issues.issues
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY created_at;
윈도우 프레임 사용
프레임 절을 지정하여 윈도우 계산에 포함할 행을 제어합니다.
-- 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;
각 윈도우 함수에 대한 자세한 문서는 윈도우 함수 참조를 참조하세요.
자세한 내용은 https://sqlite.org/windowfunctions.html 문서를 참조하세요.