윈도우 함수
윈도우 함수 (Window Functions)
SQLite의 윈도우 함수(window function)에 대해 설명하는 문서예요. OVER 절, PARTITION BY, 프레임(Frame) 정의, 내장 윈도우 함수, 사용자 정의 집계 윈도우 함수 등을 다뤄요.
본문
1. 윈도우 함수 소개 (Introduction to Window Functions)
윈도우 함수는 입력 값이 SELECT 문의 결과 집합에서 하나 이상의 행으로 이루어진 "윈도우(window)"에서 가져오는 SQL 함수예요.
윈도우 함수는 OVER 절의 존재로 스칼라 함수와 집계 함수와 구별돼요. 함수에 OVER 절이 있으면 그것은 윈도우 함수예요. OVER 절이 없으면 일반 집계 또는 스칼라 함수예요. 윈도우 함수는 함수와 OVER 절 사이에 FILTER 절을 가질 수도 있어요.
윈도우 함수의 구문은 다음과 같아요:
window-func ( expr ) filter-clause OVER window-name window-defn , *
일반 함수와 달리 윈도우 함수는 DISTINCT 키워드를 사용할 수 없어요. 또한 윈도우 함수는 SELECT 문의 결과 집합과 ORDER BY 절에만 나타날 수 있어요.
윈도우 함수에는 집계 윈도우 함수(aggregate window functions)와 내장 윈도우 함수(built-in window functions)의 두 종류가 있어요. 모든 집계 윈도우 함수는 OVER와 FILTER 절을 생략하기만 하면 일반 집계 함수로도 동작할 수 있어요. 게다가 SQLite의 모든 내장 집계 함수는 적절한 OVER 절을 추가해 집계 윈도우 함수로 사용할 수 있어요. 응용 프로그램은 sqlite3_create_window_function() 인터페이스로 새 집계 윈도우 함수를 등록할 수 있어요. 하지만 내장 윈도우 함수는 쿼리 계획자에서 특별한 처리가 필요하므로, 내장 윈도우 함수에서 발견되는 예외적 속성을 보이는 새 윈도우 함수는 응용 프로그램이 추가할 수 없어요.
내장 row_number() 윈도우 함수를 사용한 예는 다음과 같아요:
CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
-- The following SELECT statement returns:
--
-- x | y | row_number
-- -------------
-- 1 | aaa | 1
-- 2 | ccc | 3
-- 3 | bbb | 2
--
SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x;
row_number() 윈도우 함수는 window-defn 안의 "ORDER BY" 절(이 경우 "ORDER BY y") 순서대로 각 행에 연속 정수를 할당해요. 이것이 전체 쿼리에서 결과가 반환되는 순서에는 영향을 주지 않는다는 점에 주의하세요. 최종 출력의 순서는 여전히 SELECT 문에 붙은 ORDER BY 절(이 경우 "ORDER BY x")이 좌우해요.
명명된 window-defn 절을 WINDOW 절로 SELECT 문에 추가하고, 윈도우 함수 호출 안에서 이름으로 참조할 수도 있어요. 예를 들어 다음 SELECT 문은 "win1"과 "win2" 두 개의 명명된 window-defs 절을 포함해요:
SELECT x, y, row_number() OVER win1, rank() OVER win2
FROM t0
WINDOW win1 AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
win2 AS (PARTITION BY y ORDER BY x)
ORDER BY x;
WINDOW 절(있는 경우)은 HAVING 절 뒤, ORDER BY 앞에 와요.
2. 집계 윈도우 함수 (Aggregate Window Functions)
이 절의 예제는 모두 데이터베이스가 다음과 같이 채워져 있다고 가정해요:
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
INSERT INTO t1 VALUES (1, 'A', 'one' ),
(2, 'B', 'two' ),
(3, 'C', 'three'),
(4, 'D', 'one' ),
(5, 'E', 'two' ),
(6, 'F', 'three'),
(7, 'G', 'one' );
집계 윈도우 함수는 일반 집계 함수와 비슷하지만, 쿼리에 추가해도 반환되는 행 수가 변하지 않는다는 점이 달라요. 대신 각 행에 대해 집계 윈도우 함수의 결과는 대응하는 집계가 OVER 절이 지정한 "윈도우 프레임(window frame)"의 모든 행에 대해 실행된 것과 같아요.
-- The following SELECT statement returns:
--
-- a | b | group_concat
-- ----------------
-- 1 | A | A.B
-- 2 | B | A.B.C
-- 3 | C | B.C.D
-- 4 | D | C.D.E
-- 5 | E | D.E.F
-- 6 | F | E.F.G
-- 7 | G | F.G
--
SELECT a, b, group_concat(b, '.') OVER (
ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS group_concat FROM t1;
위 예제에서 윈도우 프레임은 (행이 window-defn의 ORDER BY 절, 이 경우 "ORDER BY a"에 따라 정렬될 때) 이전 행("1 PRECEDING")부터 다음 행("1 FOLLOWING")까지의 모든 행으로 구성되며, 양 끝을 포함해요. 예를 들어 (a=3) 행의 프레임은 (2, 'B', 'two'), (3, 'C', 'three'), (4, 'D', 'one') 행으로 구성돼요. 따라서 그 행의 group_concat(b, '.') 결과는 'B.C.D'예요.
SQLite의 모든 집계 함수는 집계 윈도우 함수로 사용될 수 있어요. 사용자 정의 집계 윈도우 함수를 만드는 것도 가능해요.
2.1. PARTITION BY 절 (The PARTITION BY Clause)
윈도우 함수를 계산하기 위해 쿼리의 결과 집합은 하나 이상의 "파티션(partition)"으로 나뉘어요. 파티션은 window-defn의 PARTITION BY 절의 모든 항에 대해 같은 값을 가진 모든 행으로 구성돼요. PARTITION BY 절이 없으면 쿼리의 전체 결과 집합이 단일 파티션이에요. 윈도우 함수 처리는 각 파티션별로 별도로 수행돼요.
예를 들어:
-- The following SELECT statement returns:
--
-- c | a | b | group_concat
-- -------------------------
-- one | 1 | A | A.D.G
-- one | 4 | D | D.G
-- one | 7 | G | G
-- three | 3 | C | C.F
-- three | 6 | F | F
-- two | 2 | B | B.E
-- two | 5 | E | E
--
SELECT c, a, b, group_concat(b, '.') OVER (
PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY c, a;
위 쿼리에서 "PARTITION BY c" 절은 결과 집합을 세 개의 파티션으로 쪼개요. 첫 번째 파티션은 c=='one'인 세 행을 가지고, 두 번째 파티션은 c=='three'인 두 행, 세 번째 파티션은 c=='two'인 두 행을 가져요.
위 예제에서 각 파티션의 모든 행은 최종 출력에서 함께 그룹화돼요. PARTITION BY 절이 전체 쿼리의 ORDER BY 절의 접두사이기 때문이에요. 하지만 그럴 필요는 없어요. 파티션은 결과 집합 안에 여기저기 흩어진 행들로 구성될 수 있어요. 예를 들어:
-- The following SELECT statement returns:
--
-- c | a | b | group_concat
-- -------------------------
-- one | 1 | A | A.D.G
-- two | 2 | B | B.E
-- three | 3 | C | C.F
-- one | 4 | D | D.G
-- two | 5 | E | E
-- three | 6 | F | F
-- one | 7 | G | G
--
SELECT c, a, b, group_concat(b, '.') OVER (
PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY a;
2.2. 프레임 지정 (Frame Specifications)
frame-spec은 집계 윈도우 함수가 읽는 출력 행을 결정해요. frame-spec은 네 부분으로 구성돼요:
- 프레임 유형(frame type) - ROWS, RANGE 또는 GROUPS 중 하나,
- 시작 프레임 경계(starting frame boundary),
- 끝 프레임 경계(ending frame boundary),
- EXCLUDE 절.
구문 세부 사항은 다음과 같아요:
frame-spec:
GROUPS | RANGE | ROWS
[ BETWEEN start-boundary AND end-boundary ]
[ EXCLUDE CURRENT ROW | EXCLUDE GROUP | EXCLUDE TIES | EXCLUDE NO OTHERS ]
start-boundary / end-boundary:
UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW |
<expr> FOLLOWING | UNBOUNDED FOLLOWING
(시작 프레임 경계를 둘러싼 BETWEEN과 AND 키워드도 생략하면) 끝 프레임 경계를 생략할 수 있으며, 그 경우 끝 프레임 경계는 기본적으로 CURRENT ROW가 돼요.
프레임 유형이 RANGE 또는 GROUPS이면 모든 ORDER BY 표현식에 대해 같은 값을 가진 행들은 "동료(peer)"로 간주돼요. 또는 ORDER BY 항이 없으면 모든 행이 동료예요. 동료는 항상 같은 프레임 안에 있어요.
기본 frame-spec은 다음과 같아요:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
기본값은 집계 윈도우 함수가 파티션의 시작부터 현재 행과 그 동료까지의 모든 행을 읽는다는 뜻이에요. 이것은 모든 ORDER BY 표현식에 대해 같은 값을 가진 행들은 (윈도우 프레임이 같으므로) 윈도우 함수의 결과에도 같은 값을 가짐을 의미해요. 예를 들어:
-- The following SELECT statement returns:
--
-- a | b | c | group_concat
-- -------------------------
-- 1 | A | one | A.D.G
-- 2 | B | two | A.D.G.C.F.B.E
-- 3 | C | three | A.D.G.C.F
-- 4 | D | one | A.D.G
-- 5 | E | two | A.D.G.C.F.B.E
-- 6 | F | three | A.D.G.C.F
-- 7 | G | one | A.D.G
--
SELECT a, b, c,
group_concat(b, '.') OVER (ORDER BY c) AS group_concat
FROM t1 ORDER BY a;
2.2.1. 프레임 유형 (Frame Type)
ROWS, GROUPS, RANGE의 세 가지 프레임 유형이 있어요. 프레임 유형은 프레임의 시작과 끝 경계가 어떻게 측정되는지 결정해요.
-
ROWS: ROWS 프레임 유형은 프레임의 시작과 끝 경계가 현재 행에 상대적인 개별 행을 세는 것으로 결정됨을 의미해요.
-
GROUPS: GROUPS 프레임 유형은 시작과 끝 경계가 현재 그룹에 상대적인 "그룹"을 세는 것으로 결정됨을 의미해요. "그룹"은 윈도우 ORDER BY 절의 모든 항에 대해 동등한 값을 가진 행의 집합이에요. ("동등(Equivalent)"은 두 값을 비교할 때 IS 연산자가 참이라는 뜻이에요.) 즉, 그룹은 한 행의 모든 동료로 구성돼요.
-
RANGE: RANGE 프레임 유형은 윈도우의 ORDER BY 절이 정확히 하나의 항을 가져야 함을 요구해요. 그 항을 "X"라고 부르자. RANGE 프레임 유형에서 프레임의 요소는 파티션의 모든 행에 대해 표현식 X의 값을 계산하고, X의 값이 현재 행의 X 값의 특정 범위 안에 있는 행들을 프레임으로 구성함으로써 결정돼요. 자세한 내용은 아래의 "
PRECEDING " 경계 지정 설명을 참고하세요.
ROWS와 GROUPS 프레임 유형은 둘 다 현재 행에 상대적으로 세어 프레임의 범위를 결정한다는 점에서 비슷해요. 차이는 ROWS는 개별 행을 세고 GROUPS는 동료 그룹을 센다는 것이에요. RANGE 프레임 유형은 달라요. RANGE 프레임 유형은 현재 행에 상대적인 어떤 값 띠(band) 안에 있는 표현식 값을 찾아 프레임의 범위를 결정해요.
2.2.2. 프레임 경계 (Frame Boundaries)
시작 및 끝 프레임 경계를 기술하는 다섯 가지 방법이 있어요:
-
UNBOUNDED PRECEDING 프레임 경계는 파티션의 첫 번째 행이에요.
-
PRECEDING 은 음이 아닌 상수 숫자 표현식이어야 해요. 경계는 현재 행보다 "단위" 앞에 있는 행이에요. 여기서 "단위"의 의미는 프레임 유형에 따라 달라요: -
ROWS → 프레임 경계는 현재 행보다
행 앞에 있는 행이거나, 현재 행보다 앞에 보다 적은 행이 있으면 파티션의 첫 번째 행이에요. 은 정수여야 해요. -
GROUPS → "그룹"은 동료 행 집합 - ORDER BY 절의 모든 항에 대해 같은 값을 가진 행 - 이에요. 프레임 경계는 현재 행을 담은 그룹보다
개 그룹 앞에 있는 그룹이거나, 현재 행보다 앞에 보다 적은 그룹이 있으면 파티션의 첫 번째 그룹이에요. 프레임의 시작 경계에는 그룹의 첫 번째 행이 사용되고, 프레임의 끝 경계에는 그룹의 마지막 행이 사용돼요. 은 정수여야 해요. -
RANGE → 이 형식의 경우 window-defn의 ORDER BY 절이 단일 항을 가져야 해요. 그 ORDER BY 항을 "X"라고 부르자. Xi를 파티션의 i번째 행에 대한 X 표현식의 값, Xc를 현재 행에 대한 X의 값이라고 하자. 비공식적으로 RANGE 경계는 Xi가 Xc의
안에 있는 첫 번째 행이에요. 더 정확히: - Xi나 Xc 중 하나라도 숫자가 아니면 경계는 "Xi IS Xc" 표현식이 참인 첫 번째 행이에요.
- ORDER BY가 ASC이면 경계는 Xi>=Xc-
인 첫 번째 행이에요. - ORDER BY가 DESC이면 경계는 Xi<=Xc+
인 첫 번째 행이에요.
-
이 형식의 경우
-
CURRENT ROW 현재 행. RANGE와 GROUPS 프레임 유형의 경우, EXCLUDE 절이 특별히 제외하지 않는 한 현재 행의 동료도 프레임에 포함돼요. 이것은 CURRENT ROW가 시작 또는 끝 프레임 경계로 사용되는지와 무관하게 참이에요.
-
FOLLOWING 이것은 경계가 현재 행 이전이 아니라 현재 행보다단위 뒤에 있다는 것만 제외하면 " PRECEDING"과 같아요. -
UNBOUNDED FOLLOWING 프레임 경계는 파티션의 마지막 행이에요.
끝 프레임 경계는 위 목록에서 시작 프레임 경계보다 더 높은 위치에 나타나는 형식을 취할 수 없어요.
다음 예제에서 각 행의 윈도우 프레임은 행이 "ORDER BY a"에 따라 정렬될 때 현재 행부터 집합의 끝까지의 모든 행으로 구성돼요.
-- The following SELECT statement returns:
--
-- c | a | b | group_concat
-- -------------------------
-- one | 1 | A | A.D.G.C.F.B.E
-- one | 4 | D | D.G.C.F.B.E
-- one | 7 | G | G.C.F.B.E
-- three | 3 | C | C.F.B.E
-- three | 6 | F | F.B.E
-- two | 2 | B | B.E
-- two | 5 | E | E
--
SELECT c, a, b, group_concat(b, '.') OVER (
ORDER BY c, a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY c, a;
2.2.3. EXCLUDE 절 (The EXCLUDE Clause)
선택적 EXCLUDE 절은 다음 네 가지 형식 중 하나를 취할 수 있어요:
-
EXCLUDE NO OTHERS: 이것이 기본이에요. 이 경우 시작과 끝 프레임 경계가 정의하는 윈도우 프레임에서 어떤 행도 제외되지 않아요.
-
EXCLUDE CURRENT ROW: 이 경우 현재 행이 윈도우 프레임에서 제외돼요. GROUPS와 RANGE 프레임 유형의 경우 현재 행의 동료는 프레임에 남아요.
-
EXCLUDE GROUP: 이 경우 현재 행과 현재 행의 동료인 모든 다른 행이 프레임에서 제외돼요. EXCLUDE 절을 처리할 때, 프레임 유형이 ROWS여도 같은 ORDER BY 값을 가진 모든 행, 또는 ORDER BY 절이 없으면 파티션의 모든 행이 동료로 간주돼요.
-
EXCLUDE TIES: 이 경우 현재 행은 프레임의 일부이지만 현재 행의 동료는 제외돼요.
다음 예제는 EXCLUDE 절의 다양한 형식의 효과를 보여줘요:
-- The following SELECT statement returns:
--
-- c | a | b | no_others | current_row | grp | ties
-- one | 1 | A | A.D.G | D.G | | A
-- one | 4 | D | A.D.G | A.G | | D
-- one | 7 | G | A.D.G | A.D | | G
-- three | 3 | C | A.D.G.C.F | A.D.G.F | A.D.G | A.D.G.C
-- three | 6 | F | A.D.G.C.F | A.D.G.C | A.D.G | A.D.G.F
-- two | 2 | B | A.D.G.C.F.B.E | A.D.G.C.F.E | A.D.G.C.F | A.D.G.C.F.B
-- two | 5 | E | A.D.G.C.F.B.E | A.D.G.C.F.B | A.D.G.C.F | A.D.G.C.F.E
--
SELECT c, a, b,
group_concat(b, '.') OVER (
ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
) AS no_others,
group_concat(b, '.') OVER (
ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
) AS current_row,
group_concat(b, '.') OVER (
ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP
) AS grp,
group_concat(b, '.') OVER (
ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
) AS ties
FROM t1 ORDER BY c, a;
2.3. FILTER 절 (The FILTER Clause)
filter-clause:
FILTER ( WHERE expr )
FILTER 절이 제공되면 _expr_이 참인 행만 윈도우 프레임에 포함돼요. 집계 윈도우는 여전히 모든 행에 대해 값을 반환하지만, FILTER 표현식이 참 이외의 값으로 평가되는 행은 어떤 행의 윈도우 프레임에도 포함되지 않아요. 예를 들어:
-- The following SELECT statement returns:
--
-- c | a | b | group_concat
-- -------------------------
-- one | 1 | A | A
-- two | 2 | B | A
-- three | 3 | C | A.C
-- one | 4 | D | A.C.D
-- two | 5 | E | A.C.D
-- three | 6 | F | A.C.D.F
-- one | 7 | G | A.C.D.F.G
--
SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER (
ORDER BY a
) AS group_concat
FROM t1 ORDER BY a;
3. 내장 윈도우 함수 (Built-in Window Functions)
집계 윈도우 함수에 더해 SQLite는 PostgreSQL이 지원하는 것을 기반으로 한 내장 윈도우 함수 집합을 제공해요.
내장 윈도우 함수는 집계 윈도우 함수와 같은 방식으로 PARTITION BY 절을 존중해요. - 각 선택된 행이 파티션에 할당되고 각 파티션이 별도로 처리돼요. ORDER BY 절이 각 내장 윈도우 함수에 영향을 주는 방식은 아래에 설명돼요. 일부 윈도우 함수(rank(), dense_rank(), percent_rank(), ntile())는 "동료 그룹(peer group)"(같은 파티션 안에서 모든 ORDER BY 표현식에 대해 같은 값을 가진 행) 개념을 사용해요. 이런 경우 frame-spec이 ROWS, GROUPS, RANGE 중 무엇을 지정하는지는 중요하지 않아요. 내장 윈도우 함수 처리의 목적상, 모든 ORDER BY 표현식에 대해 같은 값을 가진 행들은 프레임 유형과 무관하게 동료로 간주돼요.
대부분의 내장 윈도우 함수는 frame-spec을 무시해요. 예외는 first_value(), last_value(), nth_value()예요. 내장 윈도우 함수 호출의 일부로 FILTER 절을 지정하는 것은 구문 오류예요.
SQLite는 다음 11개의 내장 윈도우 함수를 지원해요:
row_number()
현재 파티션 안에서의 행 번호예요. 행은 윈도우 정의의 ORDER BY 절이 정의한 순서로 1부터 번호가 매겨지고, 그 외에는 임의의 순서로 매겨져요.
rank()
각 그룹의 첫 동료의 row_number() - 즉, 갭이 있는 현재 행의 순위예요. ORDER BY 절이 없으면 모든 행이 동료로 간주되고 이 함수는 항상 1을 반환해요.
dense_rank()
현재 행의 동료 그룹이 파티션 안에서 가지는 번호 - 즉, 갭이 없는 현재 행의 순위예요. 행은 윈도우 정의의 ORDER BY 절이 정의한 순서로 1부터 번호가 매겨져요. ORDER BY 절이 없으면 모든 행이 동료로 간주되고 이 함수는 항상 1을 반환해요.
percent_rank()
이름과 달리 이 함수는 항상 0.0과 1.0 사이의 값을 반환하며, (rank - 1)/(partition-rows - 1)과 같아요. 여기서 _rank_는 내장 윈도우 함수 rank()가 반환한 값이고 _partition-rows_는 파티션의 총 행 수예요. 파티션에 행이 하나만 있으면 이 함수는 0.0을 반환해요.
cume_dist()
누적 분포(cumulative distribution)예요. row-number/_partition-rows_로 계산되며, _row-number_는 그룹의 마지막 동료에 대한 row_number()가 반환한 값이고 _partition-rows_는 파티션의 행 수예요.
ntile(N)
인자 _N_은 정수로 처리돼요. 이 함수는 파티션을 가능한 한 균등하게 N개의 그룹으로 나누고, ORDER BY 절이 정의한 순서로(또는 그 외에는 임의의 순서로) 각 그룹에 1부터 N 사이의 정수를 할당해요. 필요하면 더 큰 그룹이 먼저 나와요. 이 함수는 현재 행이 속한 그룹에 할당된 정수 값을 반환해요.
lag(expr)
lag(expr, offset)
lag(expr, offset, default)
lag() 함수의 첫 번째 형식은 파티션의 이전 행에 대해 표현식 _expr_을 평가한 결과를 반환해요. 또는 (현재 행이 첫 번째라서) 이전 행이 없으면 NULL을 반환해요.
offset 인자가 제공되면 음이 아닌 정수여야 해요. 이 경우 반환 값은 파티션 안에서 현재 행보다 _offset_행 앞에 있는 행에 대해 _expr_을 평가한 결과예요. _offset_이 0이면 _expr_은 현재 행에 대해 평가돼요. 현재 행보다 _offset_행 앞에 행이 없으면 NULL이 반환돼요.
_default_도 제공되면 _offset_이 식별한 행이 존재하지 않을 때 NULL 대신 반환돼요.
lead(expr)
lead(expr, offset)
lead(expr, offset, default)
lead() 함수의 첫 번째 형식은 파티션의 다음 행에 대해 표현식 _expr_을 평가한 결과를 반환해요. 또는 (현재 행이 마지막이라서) 다음 행이 없으면 NULL을 반환해요.
offset 인자가 제공되면 음이 아닌 정수여야 해요. 이 경우 반환 값은 파티션 안에서 현재 행보다 _offset_행 뒤에 있는 행에 대해 _expr_을 평가한 결과예요. _offset_이 0이면 _expr_은 현재 행에 대해 평가돼요. 현재 행보다 _offset_행 뒤에 행이 없으면 NULL이 반환돼요.
_default_도 제공되면 _offset_이 식별한 행이 존재하지 않을 때 NULL 대신 반환돼요.
first_value(expr)
이 내장 윈도우 함수는 집계 윈도우 함수와 같은 방식으로 각 행의 윈도우 프레임을 계산해요. 각 행의 윈도우 프레임의 첫 번째 행에 대해 평가된 _expr_의 값을 반환해요.
last_value(expr)
이 내장 윈도우 함수는 집계 윈도우 함수와 같은 방식으로 각 행의 윈도우 프레임을 계산해요. 각 행의 윈도우 프레임의 마지막 행에 대해 평가된 _expr_의 값을 반환해요.
nth_value(expr, N)
이 내장 윈도우 함수는 집계 윈도우 함수와 같은 방식으로 각 행의 윈도우 프레임을 계산해요. 윈도우 프레임의 _N_번째 행에 대해 평가된 _expr_의 값을 반환해요. 행은 윈도우 프레임 안에서, 있으면 ORDER BY 절이 정의한 순서로(그 외에는 임의의 순서로) 1부터 번호가 매겨져요. 파티션에 _N_번째 행이 없으면 NULL이 반환돼요.
이 절의 예제는 앞서 정의한 T1 테이블과 다음 T2 테이블을 사용해요:
CREATE TABLE t2(a, b);
INSERT INTO t2 VALUES('a', 'one'),
('a', 'two'),
('a', 'three'),
('b', 'four'),
('c', 'five'),
('c', 'six');
다음 예제는 다섯 개의 순위 함수 - row_number(), rank(), dense_rank(), percent_rank(), cume_dist() - 의 동작을 보여줘요.
-- The following SELECT statement returns:
--
-- a | row_number | rank | dense_rank | percent_rank | cume_dist
-- ------------------------------------------------------------------
-- a | 1 | 1 | 1 | 0.0 | 0.5
-- a | 2 | 1 | 1 | 0.0 | 0.5
-- a | 3 | 1 | 1 | 0.0 | 0.5
-- b | 4 | 4 | 2 | 0.6 | 0.66
-- c | 5 | 5 | 3 | 0.8 | 1.0
-- c | 6 | 5 | 3 | 0.8 | 1.0
--
SELECT a AS a,
row_number() OVER win AS row_number,
rank() OVER win AS rank,
dense_rank() OVER win AS dense_rank,
percent_rank() OVER win AS percent_rank,
cume_dist() OVER win AS cume_dist
FROM t2
WINDOW win AS (ORDER BY a);
아래 예제는 ntile()을 사용해 여섯 행을 두 그룹(ntile(2) 호출)으로, 네 그룹(ntile(4) 호출)으로 나눠요. ntile(2)의 경우 각 그룹에 세 행이 할당돼요. ntile(4)의 경우 둘로 된 그룹 두 개와 하나로 된 그룹 두 개가 있어요. 더 큰 둘로 된 그룹이 먼저 나와요.
-- The following SELECT statement returns:
--
-- a | b | ntile_2 | ntile_4
-- ----------------------------------
-- a | one | 1 | 1
-- a | two | 1 | 1
-- a | three | 1 | 2
-- b | four | 2 | 2
-- c | five | 2 | 3
-- c | six | 2 | 4
--
SELECT a AS a,
b AS b,
ntile(2) OVER win AS ntile_2,
ntile(4) OVER win AS ntile_4
FROM t2
WINDOW win AS (ORDER BY a);
다음 예제는 lag(), lead(), first_value(), last_value(), nth_value()를 보여줘요. frame-spec은 lag()와 lead() 둘 다에서 무시되지만 first_value(), last_value(), nth_value()에서는 존중돼요.
-- The following SELECT statement returns:
--
-- b | lead | lag | first_value | last_value | nth_value_3
-- -------------------------------------------------------------
-- A | C | NULL | A | A | NULL
-- B | D | A | A | B | NULL
-- C | E | B | A | C | C
-- D | F | C | A | D | C
-- E | G | D | A | E | C
-- F | n/a | E | A | F | C
-- G | n/a | F | A | G | C
--
SELECT b AS b,
lead(b, 2, 'n/a') OVER win AS lead,
lag(b) OVER win AS lag,
first_value(b) OVER win AS first_value,
last_value(b) OVER win AS last_value,
nth_value(b, 3) OVER win AS nth_value_3
FROM t1
WINDOW win AS (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
4. 윈도우 체이닝 (Window Chaining)
윈도우 체이닝은 한 윈도우를 다른 윈도우로 정의할 수 있게 하는 축약 표기법이에요. 구체적으로, 이 축약 표기법은 새 윈도우가 기본(베이스) 윈도우의 PARTITION BY와 선택적으로 ORDER BY 절을 암시적으로 복사할 수 있게 해줘요. 예를 들어 다음에서:
SELECT group_concat(b, '.') OVER (
win ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM t1
WINDOW win AS (PARTITION BY a ORDER BY c)
group_concat() 함수가 사용하는 윈도우는 "PARTITION BY a ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"와 동등해요. 윈도우 체이닝을 사용하려면 다음이 모두 참이어야 해요:
-
새 윈도우 정의는 PARTITION BY 절을 포함해서는 안 돼요. PARTITION BY 절(있으면)은 기본 윈도우 정의가 제공해야 해요.
-
기본 윈도우에 ORDER BY 절이 있으면 새 윈도우로 복사돼요. 이 경우 새 윈도우는 ORDER BY 절을 지정해서는 안 돼요. 기본 윈도우에 ORDER BY 절이 없으면 새 윈도우 정의의 일부로 하나를 지정할 수 있어요.
-
기본 윈도우는 프레임 정의를 지정해서는 안 돼요. 프레임 정의는 새 윈도우 정의에서만 주어질 수 있어요.
아래 두 SQL 조각은 비슷하지만 완전히 동등하지는 않아요. 후자는 윈도우 "win"의 정의가 프레임 정의를 포함하면 실패할 거예요.
SELECT group_concat(b, '.') OVER win ...
SELECT group_concat(b, '.') OVER (win) ...
5. 사용자 정의 집계 윈도우 함수 (User-Defined Aggregate Window Functions)
사용자 정의 집계 윈도우 함수는 sqlite3_create_window_function() API로 만들 수 있어요. 집계 윈도우 함수를 구현하는 것은 일반 집계 함수와 매우 비슷해요. 어떤 사용자 정의 집계 윈도우 함수도 일반 집계로 사용될 수 있어요. 사용자 정의 집계 윈도우 함수를 구현하려면 응용 프로그램이 네 개의 콜백 함수를 제공해야 해요:
| 콜백 | 설명 |
|---|---|
| xStep | 이 메서드는 윈도우 집계와 레거시 집계 함수 구현 모두에 필요해요. 현재 윈도우에 행을 추가하기 위해 호출돼요. 추가되는 행에 해당하는 (있으면) 함수 인자가 xStep 구현에 전달돼요. |
| xFinal | 이 메서드는 윈도우 집계와 레거시 집계 함수 구현 모두에 필요해요. (현재 윈도우의 내용으로 결정되는) 집계의 현재 값을 반환하고, 이전 xStep 호출이 할당한 자원을 해제하기 위해 호출돼요. |
| xValue | 이 메서드는 윈도우 집계 함수에만 필요해요. 이 메서드의 존재가 윈도우 집계 함수를 레거시 집계 함수와 구별짓는 것이에요. 집계의 현재 값을 반환하기 위해 호출돼요. xFinal과 달리 구현이 어떤 컨텍스트도 삭제해서는 안 돼요. |
| xInverse | 이 메서드는 레거시 집계 함수 구현이 아닌 윈도우 집계 함수에만 필요해요. 현재 창에서 현재 가장 오래된 집계 결과인 xStep의 결과를 제거하기 위해 호출돼요. (있으면) 함수 인자는 제거되는 행에 대해 xStep에 전달된 것들이에요. |
아래 C 코드는 sumint()라는 이름의 간단한 윈도우 집계 함수를 구현해요. 이것은 인자로 정수 값이 아닌 것이 전달되면 예외를 던진다는 점만 제외하고 내장 sum() 함수와 같은 방식으로 동작해요.
/*
** xStep for sumint().
**
** Add the value of the argument to the aggregate context (an integer).
*/
static void sumintStep(
sqlite3_context *ctx,
int nArg,
sqlite3_value *apArg[]
){
sqlite3_int64 *pInt;
assert( nArg==1 );
if( sqlite3_value_type(apArg[0])!=SQLITE_INTEGER ){
sqlite3_result_error(ctx, "invalid argument", -1);
return;
}
pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, sizeof(sqlite3_int64));
if( pInt ){
*pInt += sqlite3_value_int64(apArg[0]);
}
}
/*
** xInverse for sumint().
**
** This does the opposite of xStep() - subtracts the value of the argument
** from the current context value. The error checking can be omitted from
** this function, as it is only ever called after xStep() (so the aggregate
** context has already been allocated) and with a value that has already
** been passed to xStep() without error (so it must be an integer).
*/
static void sumintInverse(
sqlite3_context *ctx,
int nArg,
sqlite3_value *apArg[]
){
sqlite3_int64 *pInt;
assert( sqlite3_value_type(apArg[0])==SQLITE_INTEGER );
pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, sizeof(sqlite3_int64));
*pInt -= sqlite3_value_int64(apArg[0]);
}
/*
** xFinal for sumint().
**
** Return the current value of the aggregate window function. Because
** this implementation does not allocate any resources beyond the buffer
** returned by sqlite3_aggregate_context, which is automatically freed
** by the system, there are no resources to free. And so this method is
** identical to xValue().
*/
static void sumintFinal(sqlite3_context *ctx){
sqlite3_int64 res = 0;
sqlite3_int64 *pInt;
pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, 0);
if( pInt ) res = *pInt;
sqlite3_result_int64(ctx, res);
}
/*
** xValue for sumint().
**
** Return the current value of the aggregate window function.
*/
static void sumintValue(sqlite3_context *ctx){
sqlite3_int64 res = 0;
sqlite3_int64 *pInt;
pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, 0);
if( pInt ) res = *pInt;
sqlite3_result_int64(ctx, res);
}
/*
** Register sumint() window aggregate with database handle db.
*/
int register_sumint(sqlite3 *db){
return sqlite3_create_window_function(db, "sumint", 1, SQLITE_UTF8, 0,
sumintStep, sumintFinal, sumintValue, sumintInverse, 0
);
}
다음 예제는 위 C 코드가 구현한 sumint() 함수를 사용해요. 각 행에 대해 윈도우는 (있으면) 이전 행, 현재 행, (역시 있으면) 다음 행으로 구성돼요:
CREATE TABLE t3(x, y);
INSERT INTO t3 VALUES('a', 4),
('b', 5),
('c', 3),
('d', 8),
('e', 1);
-- Assuming the database is populated using the above script, the
-- following SELECT statement returns:
--
-- x | sum_y
-- --------------
-- a | 9
-- b | 12
-- c | 16
-- d | 12
-- e | 9
--
SELECT x, sumint(y) OVER (
ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS sum_y
FROM t3 ORDER BY x;
위 쿼리를 처리하면서 SQLite는 sumint 콜백을 다음과 같이 호출해요:
- xStep(4) - "4"를 현재 윈도우에 추가한다.
- xStep(5) - "5"를 현재 윈도우에 추가한다.
- xValue() - (x='a') 행에 대한 sumint() 값을 얻기 위해 xValue()를 호출한다. 윈도우는 현재 값 4와 5로 구성되므로 결과는 9다.
- xStep(3) - "3"을 현재 윈도우에 추가한다.
- xValue() - (x='b') 행에 대한 sumint() 값을 얻기 위해 xValue()를 호출한다. 윈도우는 현재 값 4, 5, 3으로 구성되므로 결과는 12다.
- xInverse(4) - "4"를 윈도우에서 제거한다.
- xStep(8) - "8"을 현재 윈도우에 추가한다. 윈도우는 이제 값 5, 3, 8로 구성된다.
- xValue() - (x='c') 행의 값을 얻기 위해 호출된다. 이 경우 16이다.
- xInverse(5) - "5" 값을 윈도우에서 제거한다.
- xStep(1) - "1" 값을 윈도우에 추가한다.
- xValue() - (x='d') 행의 값을 얻기 위해 호출된다.
- xInverse(3) - "3" 값을 윈도우에서 제거한다. 윈도우는 이제 값 8과 1만 담고 있다.
- xFinal() - 할당된 자원을 회수하고 (x='e') 행의 값을 얻기 위해 호출된다. 9다.
사용자가 SQLite가 xFinal()을 호출하기 전에 문 핸들에서 sqlite3_reset()이나 sqlite3_finalize()를 호출해 쿼리 실행을 중단하면, 할당된 자원을 회수하기 위해 sqlite3_reset() 또는 sqlite3_finalize() 호출 안에서 xFinal()이 자동으로 호출돼요. 값은 필요 없지만요. 이 경우 xFinal 구현이 반환한 어떤 오류도 조용히 버려져요.
6. 역사 (History)
윈도우 함수 지원은 SQLite에 3.25.0 버전(2018-09-15) 릴리스로 처음 추가되었어요. SQLite 개발자들은 윈도우 함수가 어떻게 동작해야 하는지에 대한 주요 참고 자료로 PostgreSQL 윈도우 함수 문서를 사용했어요. SQLite와 PostgreSQL에서 윈도우 함수가 같은 방식으로 동작하는지 확인하기 위해 PostgreSQL에 대해 많은 테스트 케이스가 실행되었어요.
SQLite 3.28.0 버전(2019-04-16)에서 윈도우 함수 지원이 EXCLUDE 절, GROUPS 프레임 유형, 윈도우 체이닝, RANGE 프레임에서의 "