윈도우 함수
윈도우 함수 (Window Functions)
DuckDB는 윈도우 함수를 지원해요. 윈도우 함수는 여러 행을 사용해서 각 행의 값을 계산합니다. 윈도우 함수는 차단 연산자(blocking operator)라서, 전체 입력을 버퍼에 담아야만 해요. 그 때문에 SQL에서 메모리를 가장 많이 쓰는 연산자 중 하나입니다.
윈도우 함수는 SQL 표준인 SQL:2003부터 들어왔고, 주요 SQL 데이터베이스 시스템들이 모두 지원합니다.
예제
행에 번호를 붙이는 row_number 열을 만들려면:
SELECT row_number () OVER () FROM sales ;
팁: 테이블의 행마다 그냥 번호만 필요하다면,
rowid의사열(pseudocolumn)을 쓸 수도 있어요.
time 순서로 정렬된 row_number 열을 만들려면:
SELECT row_number () OVER ( ORDER BY time ) FROM sales ;
time 순서로 정렬하고 region별로 나눈 row_number 열을 만들려면:
SELECT row_number () OVER ( PARTITION BY region ORDER BY time ) FROM sales ;
현재 행과 time 순서상 바로 앞 행의 amount 차이를 계산하려면:
SELECT amount - lag ( amount ) OVER ( ORDER BY time ) FROM sales ;
각 행이 region별 전체 amount에서 차지하는 비율을 계산하려면:
SELECT amount / sum ( amount ) OVER ( PARTITION BY region ) FROM sales ;
문법
윈도우 함수는 SELECT 절에서만 쓸 수 있어요. 함수들 사이에 OVER 명세를 공유하고 싶다면, 문의 WINDOW 절을 쓰고 OVER window_name 문법을 사용하면 됩니다.
범용 윈도우 함수
아래 표는 사용 가능한 범용 윈도우 함수를 보여줍니다.
| 이름 | 설명 |
|---|---|
cume_dist([ORDER BY ordering]) |
누적 분포: (현재 행보다 앞에 있거나 같은 값인 파티션 행의 수) / 전체 파티션 행 수. |
dense_rank() |
현재 행의 순위 공백 없이; 이 함수는 동일 값 그룹(peer group)을 센다. |
fill(expr [ ORDER BY ordering]) |
ORDER BY를 X축으로 삼아 선형 보간으로 누락 값을 채운다. |
first_value(expr[ ORDER BY ordering][ IGNORE NULLS]) |
윈도우 프레임의 첫 번째 행(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행)에서 expr을 평가한 값을 반환한다. |
lag(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS]) |
윈도우 프레임 안에서 현재 행보다 offset 행 앞(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행들 중에서)에 있는 행에서 expr을 평가한 값을 반환한다. 그런 행이 없으면 default(반드시 expr과 같은 타입)를 반환한다. offset과 default는 모두 현재 행 기준으로 평가된다. 생략하면 offset은 1, default는 NULL이 기본값. |
last_value(expr[ ORDER BY ordering][ IGNORE NULLS]) |
윈도우 프레임의 마지막 행(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행들 중에서)에서 expr을 평가한 값을 반환한다. |
lead(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS]) |
윈도우 프레임 안에서 현재 행보다 offset 행 뒤(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행들 중에서)에 있는 행에서 expr을 평가한 값을 반환한다. 그런 행이 없으면 default(반드시 expr과 같은 타입)를 반환한다. offset과 default는 모두 현재 행 기준으로 평가된다. 생략하면 offset은 1, default는 NULL이 기본값. |
nth_value(expr, nth[ ORDER BY ordering][ IGNORE NULLS]) |
윈도우 프레임의 n번째 행(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행들 중에서, 1부터 셈)에서 expr을 평가한 값을 반환한다. 그런 행이 없으면 NULL. |
ntile(num_buckets[ ORDER BY ordering]) |
1부터 num_buckets까지의 정수로, 파티션을 최대한 균등하게 나눈다. |
percent_rank([ORDER BY ordering]) |
현재 행의 상대 순위: (rank() - 1) / (전체 파티션 행 수 - 1). |
rank([ORDER BY ordering]) |
현재 행의 순위 공백 포함; 첫 번째 동일 값 행의 row_number와 같다. |
row_number([ORDER BY ordering]) |
파티션 안에서 현재 행의 번호, 1부터 센다. |
cume_dist([ORDER BY ordering])
cume_dist([ORDER BY ordering])
| 설명 | 누적 분포: (현재 행보다 앞에 있거나 같은 값인 파티션 행의 수) / 전체 파티션 행 수. ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 분포를 계산한다. |
|---|---|
| 반환 타입 | DOUBLE |
| 예제 | cume_dist() |
dense_rank()
dense_rank()
| 설명 | 현재 행의 순위 공백 없이; 이 함수는 동일 값 그룹을 센다. |
|---|---|
| 반환 타입 | BIGINT |
| 예제 | dense_rank() |
| 별칭 | rank_dense() |
fill(expr[ ORDER BY ordering])
fill(expr[ ORDER BY ordering])
| 설명 | expr의 NULL 값을, 가장 가까운 NULL이 아닌 값과 정렬값에 기반한 선형 보간으로 대체한다. 두 값 모두 산술 연산을 지원해야 하고, 정렬 키는 하나만 있어야 한다. 양 끝의 누락 값에는 선형 외삽이 적용된다. 보간에 실패하면 NULL 값을 그대로 유지한다. |
|---|---|
| 반환 타입 | expr과 같은 타입 |
| 예제 | fill(column) |
first_value(expr[ ORDER BY ordering][ IGNORE NULLS])
first_value(expr[ ORDER BY ordering][ IGNORE NULLS])
| 설명 | 윈도우 프레임의 첫 번째 행(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행)에서 expr을 평가한 값을 반환한다. ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 첫 행을 계산한다. |
|---|---|
| 반환 타입 | expr과 같은 타입 |
| 예제 | first_value(column) |
lag(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])
lag(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])
| 설명 | 윈도우 프레임 안에서 현재 행보다 offset 행 앞(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행들 중에서)에 있는 행에서 expr을 평가한 값을 반환한다. 그런 행이 없으면 default(반드시 expr과 같은 타입)를 반환한다. offset과 default는 모두 현재 행 기준으로 평가된다. 생략하면 offset은 1, default는 NULL. ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 앞 행 번호를 계산한다. |
|---|---|
| 반환 타입 | expr과 같은 타입 |
| 예제 | lag(column, 3, 0) |
last_value(expr[ ORDER BY ordering][ IGNORE NULLS])
last_value(expr[ ORDER BY ordering][ IGNORE NULLS])
| 설명 | 윈도우 프레임의 마지막 행(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행들 중에서)에서 expr을 평가한 값을 반환한다. 생략하면 offset은 1, default는 NULL. ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 마지막 행을 결정한다. |
|---|---|
| 반환 타입 | expr과 같은 타입 |
| 예제 | last_value(column) |
lead(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])
lead(expr[, offset[, default]][ ORDER BY ordering][ IGNORE NULLS])
| 설명 | 윈도우 프레임 안에서 현재 행보다 offset 행 뒤(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행들 중에서)에 있는 행에서 expr을 평가한 값을 반환한다. 그런 행이 없으면 default(반드시 expr과 같은 타입)를 반환한다. offset과 default는 모두 현재 행 기준으로 평가된다. 생략하면 offset은 1, default는 NULL. ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 뒤 행 번호를 계산한다. |
|---|---|
| 반환 타입 | expr과 같은 타입 |
| 예제 | lead(column, 3, 0) |
nth_value(expr, nth[ ORDER BY ordering][ IGNORE NULLS])
nth_value(expr, nth[ ORDER BY ordering][ IGNORE NULLS])
| 설명 | 윈도우 프레임의 n번째 행(IGNORE NULLS가 설정되면 expr 값이 NULL이 아닌 행들 중에서, 1부터 셈)에서 expr을 평가한 값을 반환한다. 그런 행이 없으면 NULL. ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 n번째 행을 계산한다. |
|---|---|
| 반환 타입 | expr과 같은 타입 |
| 예제 | nth_value(column, 2) |
ntile(num_buckets[ ORDER BY ordering])
ntile(num_buckets[ ORDER BY ordering])
| 설명 | 1부터 num_buckets까지의 정수로, 파티션을 최대한 균등하게 나눈다. ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 ntile을 계산한다. |
|---|---|
| 반환 타입 | BIGINT |
| 예제 | ntile(4) |
percent_rank([ORDER BY ordering])
percent_rank([ORDER BY ordering])
| 설명 | 현재 행의 상대 순위: (rank() - 1) / (전체 파티션 행 수 - 1). ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 상대 순위를 계산한다. |
|---|---|
| 반환 타입 | DOUBLE |
| 예제 | percent_rank() |
rank([ORDER BY ordering])
rank([ORDER BY ordering])
| 설명 | 현재 행의 순위 공백 포함; 첫 번째 동일 값 행의 row_number와 같다. ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 순위를 계산한다. |
|---|---|
| 반환 타입 | BIGINT |
| 예제 | rank() |
row_number([ORDER BY ordering])
row_number([ORDER BY ordering])
| 설명 | 파티션 안에서 현재 행의 번호, 1부터 센다. ORDER BY 절이 지정되면, 프레임 순서 대신 해당 정렬을 사용해 행 번호를 계산한다. |
|---|---|
| 반환 타입 | BIGINT |
| 예제 | row_number() |
집계 윈도우 함수
모든 집계 함수는 선택적인 FILTER 절을 포함해 윈도우 맥락에서 쓸 수 있어요. first와 last 집계 함수는 각각 같은 이름의 범용 윈도우 함수에 가려지는데, 그 부수적인 결과로 이 함수들에는 FILTER 절을 쓸 수 없고 대신 IGNORE NULLS를 사용합니다.
DISTINCT 인자
모든 집계 윈도우 함수는 인자에 DISTINCT 절을 쓰는 걸 지원해요. DISTINCT 절을 주면, 집계 계산에서 고유한 값만 고려됩니다. 보통 COUNT 집계와 함께 써서 고유 요소의 개수를 셀 때 많이 쓰지만, 시스템의 아무 집계 함수와 함께 쓸 수 있어요. 중복 값에 둔감한 일부 집계(예: min, max)는 이 절이 파싱되어도 무시됩니다.
-- 특정 시점의 고유 사용자 수 세기 SELECT count ( DISTINCT name ) OVER ( ORDER BY time ) FROM sales ; -- 그 고유 사용자들을 리스트로 묶기 SELECT list ( DISTINCT name ) OVER ( ORDER BY time ) FROM sales ;
ORDER BY 인자
모든 집계 윈도우 함수는 윈도우 정렬과 다른 ORDER BY 인자 절을 쓰는 걸 지원해요. ORDER BY 인자 절을 주면, 집계되는 값들이 함수 적용 전에 정렬됩니다. 보통은 중요하지 않은데, 결정적이지 않은 결과를 낼 수 있는 순서 민감 집계(mode, list, string_agg 등)가 있어요. 이런 함수들은 인자를 정렬해서 결정적으로 만들 수 있습니다. 순서에 둔감한 집계에서는 이 절이 파싱되어도 무시됩니다.
-- 각 시점까지의 최빈값을, 동률이면 가장 최근 값을 택하며 계산 SELECT mode ( value ORDER BY time DESC ) OVER ( ORDER BY time ) FROM sales ;
SQL 표준은 범용 윈도우 함수에 ORDER BY를 쓰는 걸 허용하지 않지만, DuckDB는 이 함수들 전부(dense_rank 제외)가 그 문법을 받아들이도록 확장했고, 보조 정렬이 적용되는 범위를 제한하기 위해 프레이밍을 사용합니다.
-- 각 선수 기록을 그 종목의 역대 최고 기록과 비교 SELECT event , date , athlete , time first_value ( time ORDER BY time DESC ) OVER w AS record_time , first_value ( athlete ORDER BY time DESC ) OVER w AS record_athlete , FROM meet_results WINDOW w AS ( PARTITION BY event ORDER BY datetime ) ORDER BY ALL
인자와 ORDER BY 절 사이에 쉼표가 없다는 점을 눈여겨보세요.
NULL 처리
IGNORE NULLS를 받는 모든 범용 윈도우 함수는 기본적으로 NULL을 존중해요. 이 기본 동작은 RESPECT NULLS로 명시적으로 만들 수도 있습니다.
반대로, 모든 집계 윈도우 함수는(FILTER로 NULL 무시를 지정할 수 있는 list와 그 별칭들을 제외하고) NULL을 무시하며 RESPECT NULLS를 받지 않습니다. 예를 들어 sum(column) OVER (ORDER BY time) AS cumulativeColumn은 누적 합을 계산하는데, column 값이 NULL인 행의 cumulativeColumn 값은 바로 앞 행과 같아집니다.
평가 방식
윈도우 계산은 관계를 독립적인 파티션으로 나누고, 그 파티션을 정렬한 다음, 각 행에 대해 주변 값의 함수로 새 열을 계산하는 방식으로 동작해요. 일부 윈도우 함수는 파티션 경계와 정렬에만 의존하지만, 일부(집계 함수 전부 포함)는 프레임도 사용합니다. 프레임은 현재 행의 양쪽(preceding 또는 following)에 있는 행 수로 지정됩니다. 그 거리는 rows 수, 파티션의 정렬 값과 거리를 쓰는 range, 또는 groups 수(같은 정렬 값을 가진 행 묶음)로 지정할 수 있어요.
전체 문법은 이 페이지 상단의 그림에서 볼 수 있고, 이 그림은 계산 환경을 시각적으로 보여줍니다.
파티션과 정렬
파티셔닝은 관계를 독립적이고 서로 무관한 조각으로 나눕니다. 파티셔닝은 선택 사항이라, 지정하지 않으면 전체 관계가 하나의 파티션으로 취급됩니다. 윈도우 함수는 평가 중인 행이 속한 파티션 밖의 값에는 접근할 수 없어요.
정렬도 선택 사항인데, 정렬이 없으면 범용 윈도우 함수와 순서 민감 집계 함수의 결과, 그리고 프레이밍의 순서가 잘 정의되지 않습니다. 각 파티션은 같은 정렬 절로 정렬됩니다.
발전량 데이터 테이블이 여기 있어요. CSV 파일(power-plant-generation-history.csv)로 제공됩니다. 데이터를 불러오려면:
CREATE TABLE "Generation History" AS FROM 'power-plant-generation-history.csv' ;
발전소별로 파티셔닝하고 날짜별로 정렬하면 다음과 같은 형태가 됩니다.
| Plant | Date | MWh |
|---|---|---|
| Boston | 2019-01-02 | 564337 |
| Boston | 2019-01-03 | 507405 |
| Boston | 2019-01-04 | 528523 |
| Boston | 2019-01-05 | 469538 |
| Boston | 2019-01-06 | 474163 |
| Boston | 2019-01-07 | 507213 |
| Boston | 2019-01-08 | 613040 |
| Boston | 2019-01-09 | 582588 |
| Boston | 2019-01-10 | 499506 |
| Boston | 2019-01-11 | 482014 |
| Boston | 2019-01-12 | 486134 |
| Boston | 2019-01-13 | 531518 |
| Worcester | 2019-01-02 | 118860 |
| Worcester | 2019-01-03 | 101977 |
| Worcester | 2019-01-04 | 106054 |
| Worcester | 2019-01-05 | 92182 |
| Worcester | 2019-01-06 | 94492 |
| Worcester | 2019-01-07 | 99932 |
| Worcester | 2019-01-08 | 118854 |
| Worcester | 2019-01-09 | 113506 |
| Worcester | 2019-01-10 | 96644 |
| Worcester | 2019-01-11 | 93806 |
| Worcester | 2019-01-12 | 98963 |
| Worcester | 2019-01-13 | 107170 |
앞으로 이 테이블(또는 그 일부)을 쓰면서 윈도우 함수 평가의 여러 부분을 설명할게요.
가장 단순한 윈도우 함수는 row_number()예요. 이 함수는 파티션 안에서 1부터 시작하는 행 번호만 계산합니다.
SELECT "Plant" , "Date" , row_number () OVER ( PARTITION BY "Plant" ORDER BY "Date" ) AS "Row" FROM "Generation History" ORDER BY 1 , 2 ;
결과는 다음과 같습니다.
| Plant | Date | Row |
|---|---|---|
| Boston | 2019-01-02 | 1 |
| Boston | 2019-01-03 | 2 |
| Boston | 2019-01-04 | 3 |
| … | … | … |
| Worcester | 2019-01-02 | 1 |
| Worcester | 2019-01-03 | 2 |
| Worcester | 2019-01-04 | 3 |
| … | … | … |
함수는 ORDER BY 절과 함께 계산되지만 결과가 정렬되지는 않는다는 점을 눈여겨보세요. 그래서 정렬된 결과가 필요하면 SELECT에서도 정렬을 명시해야 합니다.
프레이밍
프레이밍은 함수가 평가되는 각 행에 상대적인 행들의 집합을 지정합니다. 현재 행과의 거리는 OVER 명세의 ORDER BY 절이 정한 순서에서 현재 행의 앞(PRECEDING) 이나 뒤(FOLLOWING) 를 나타내는 식으로 주어집니다. 이 거리는 정수 개수의 ROWS나 GROUPS로, 또는 RANGE 델타 식으로 지정할 수 있어요. 프레임이 끝난 뒤에 시작하는 프레임은 유효하지 않습니다. RANGE 명세에서는 정렬 식이 하나여야 하고, 경계값(센티널)인 UNBOUNDED PRECEDING / UNBOUNDED FOLLOWING / CURRENT ROW만 쓰지 않는다면 뺄셈을 지원해야 합니다. EXCLUDE 절을 쓰면, 지정된 정렬 식에서 현재 행과 같은 값을 가진 행(소위 동일 값 행, peer)을 프레임에서 제외할 수 있어요.
ORDER BY 절이 없으면 기본 프레임은 무제한(즉, 전체 파티션)이고, ORDER BY 절이 있으면 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW입니다. 기본적으로 CURRENT ROW 경계값(EXCLUDE 절의 CURRENT ROW가 아니라)은 RANGE나 GROUP 프레이밍에서는 현재 행과 모든 동일 값 행을 뜻하고, ROWS 프레이밍에서는 현재 행만 뜻합니다.
ROWS 프레이밍
집계 함수를 쓰는 간단한 ROWS 프레임 쿼리를 볼게요.
SELECT points , sum ( points ) OVER ( ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS we FROM results ;
이 쿼리는 각 점수와 그 양쪽 점수의 sum을 계산합니다.
파티션 가장자리에서는 두 값만 더해진다는 점을 눈여겨보세요. 프레임이 파티션 가장자리에 맞춰 잘리기 때문입니다.
RANGE 프레이밍
발전량 데이터로 돌아가볼게요. 데이터에 노이즈가 있다고 가정해봐요. 각 발전소의 노이즈를 부드럽게 하려고 7일 이동 평균을 계산하고 싶을 수 있습니다. 이럴 때 다음 윈도우 쿼리를 쓸 수 있어요.
SELECT "Plant" , "Date" , avg ( "MWh" ) OVER ( PARTITION BY "Plant" ORDER BY "Date" ASC RANGE BETWEEN INTERVAL 3 DAYS PRECEDING AND INTERVAL 3 DAYS FOLLOWING ) AS "MWh 7-day Moving Average" FROM "Generation History" ORDER BY 1 , 2 ;
이 쿼리는 데이터를 Plant로 파티셔닝하고(발전소별 데이터를 서로 분리), 각 발전소의 파티션을 Date로 정렬한 뒤(에너지 측정값들을 서로 붙여 놓고), 각 날짜 양쪽 3일의 RANGE 프레임을 avg에 사용합니다(누락된 날짜를 처리하기 위해서요). 결과는 다음과 같습니다.
| Plant | Date | MWh 7-day Moving Average |
|---|---|---|
| Boston | 2019-01-02 | 517450.75 |
| Boston | 2019-01-03 | 508793.20 |
| Boston | 2019-01-04 | 508529.83 |
| … | … | … |
| Boston | 2019-01-13 | 499793.00 |
| Worcester | 2019-01-02 | 104768.25 |
| Worcester | 2019-01-03 | 102713.00 |
| Worcester | 2019-01-04 | 102249.50 |
| … | … | … |
GROUPS 프레이밍`
세 번째 프레이밍 방식은 현재 행 기준으로 그룹 수를 셉니다. 여기서 그룹은 동일한 ORDER BY 값을 가진 행들의 묶음입니다. 매일 발전이 이뤄진다고 가정하면, 날짜 계산을 쓰지 않고도 GROUPS 프레이밍으로 시스템 전체 발전량의 이동 평균을 계산할 수 있어요.
SELECT "Date" , "Plant" , avg ( "MWh" ) OVER ( ORDER BY "Date" ASC GROUPS BETWEEN 3 PRECEDING AND 3 FOLLOWING ) AS "MWh 7-day Moving Average" FROM "Generation History" ORDER BY 1 , 2 ;
| Date | Plant | MWh 7-day Moving Average |
|---|---|---|
| 2019-01-02 | Boston | 311109.500 |
| 2019-01-02 | Worcester | 311109.500 |
| 2019-01-03 | Boston | 305753.100 |
| 2019-01-03 | Worcester | 305753.100 |
| 2019-01-04 | Boston | 305389.667 |
| 2019-01-04 | Worcester | 305389.667 |
| … | … | … |
| 2019-01-12 | Boston | 309184.900 |
| 2019-01-12 | Worcester | 309184.900 |
| 2019-01-13 | Boston | 299469.375 |
| 2019-01-13 | Worcester | 299469.375 |
같은 날짜의 값들이 서로 동일하다는 점을 눈여겨보세요.
EXCLUDE 절
EXCLUDE는 CURRENT ROW 주변의 행을 제외하기 위한 프레임 절의 선택적 수정자예요. 주변 행들의 집계 값을 계산해 현재 행이 그 값과 어떻게 비교되는지 보고 싶을 때 유용합니다.
다음 예제에서는 선수의 기록이, ±10일 안에 기록된 자기 종목의 모든 기록 평균과 어떻게 비교되는지 알고 싶다고 해볼게요.
SELECT event , date , athlete , avg ( time ) OVER w AS recent , FROM results WINDOW w AS ( PARTITION BY event ORDER BY date RANGE BETWEEN INTERVAL 10 DAYS PRECEDING AND INTERVAL 10 DAYS FOLLOWING EXCLUDE CURRENT ROW ) ORDER BY event , date , athlete ;
EXCLUDE로 현재 행을 어떻게 다룰지 정하는 네 가지 옵션이 있습니다.
CURRENT ROW– 현재 행만 제외GROUP– 현재 행과 그 모든 "동일 값 행"(같은ORDER BY값을 가진 행)을 제외TIES– 모든 동일 값 행을 제외하되 현재 행은 제외하지 않음(양쪽에 구멍이 생김)NO OTHERS– 아무것도 제외하지 않음(기본값)
제외는 윈도우 집계와 first, last, nth_value 함수 모두에 적용됩니다.
WINDOW 절
같은 SELECT에 여러 개의 서로 다른 OVER 절을 지정할 수 있고, 각각 따로 계산됩니다. 하지만 같은 배치를 여러 윈도우 함수에서 쓰고 싶을 때가 많아요. WINDOW 절은 여러 윈도우 함수가 공유할 수 있는 이름 붙은 윈도우를 정의하는 데 쓰입니다.
SELECT "Plant" , "Date" , min ( "MWh" ) OVER seven AS "MWh 7-day Moving Minimum" , avg ( "MWh" ) OVER seven AS "MWh 7-day Moving Average" , max ( "MWh" ) OVER seven AS "MWh 7-day Moving Maximum" FROM "Generation History" WINDOW seven AS ( PARTITION BY "Plant" ORDER BY "Date" ASC RANGE BETWEEN INTERVAL 3 DAYS PRECEDING AND INTERVAL 3 DAYS FOLLOWING ) ORDER BY 1 , 2 ;
세 윈도우 함수가 데이터 배치를 공유하므로 성능도 좋아집니다.
같은 WINDOW 절에 여러 윈도우를 쉼표로 구분해서 정의할 수도 있어요.
SELECT "Plant" , "Date" , min ( "MWh" ) OVER seven AS "MWh 7-day Moving Minimum" , avg ( "MWh" ) OVER seven AS "MWh 7-day Moving Average" , max ( "MWh" ) OVER seven AS "MWh 7-day Moving Maximum" , min ( "MWh" ) OVER three AS "MWh 3-day Moving Minimum" , avg ( "MWh" ) OVER three AS "MWh 3-day Moving Average" , max ( "MWh" ) OVER three AS "MWh 3-day Moving Maximum" FROM "Generation History" WINDOW seven AS ( PARTITION BY "Plant" ORDER BY "Date" ASC RANGE BETWEEN INTERVAL 3 DAYS PRECEDING AND INTERVAL 3 DAYS FOLLOWING ), three AS ( PARTITION BY "Plant" ORDER BY "Date" ASC RANGE BETWEEN INTERVAL 1 DAYS PRECEDING AND INTERVAL 1 DAYS FOLLOWING ) ORDER BY 1 , 2 ;
위 쿼리들은 select 문에서 흔히 쓰는 WHERE, GROUP BY 같은 여러 절을 쓰지 않았어요. 더 복잡한 쿼리에서 WINDOW 절이 어디에 들어가는지는 SELECT 문의 표준 순서에서 찾아볼 수 있습니다.
QUALIFY로 윈도우 함수 결과 필터링하기
윈도우 함수는 WHERE와 HAVING 절이 이미 평가된 뒤에 실행되므로, 이 절들로 윈도우 함수 결과를 필터링할 수 없어요. QUALIFY 절을 쓰면 이 필터링을 위해 서브쿼리나 WITH 절을 쓸 필요가 없어집니다.
박스-수염(Box and Whisker) 쿼리
모든 집계는 복잡한 통계 함수를 포함해 윈도우 함수로 쓸 수 있어요. 이 함수 구현은 윈도우용으로 최적화되어 있고, 윈도우 문법으로 이동하는 박스-수염 플롯 데이터를 만드는 쿼리를 쓸 수 있습니다.
SELECT "Plant" , "Date" , min ( "MWh" ) OVER seven AS "MWh 7-day Moving Minimum" , quantile_cont ( "MWh" , [ 0.25 , 0.5 , 0.75 ]) OVER seven AS "MWh 7-day Moving IQR" , max ( "MWh" ) OVER seven AS "MWh 7-day Moving Maximum" , FROM "Generation History" WINDOW seven AS ( PARTITION BY "Plant" ORDER BY "Date" ASC RANGE BETWEEN INTERVAL 3 DAYS PRECEDING AND INTERVAL 3 DAYS FOLLOWING ) ORDER BY 1 , 2 ;