윈도우(Window) 함수 구문 및 사용법
윈도우(Window) 함수 구문 및 사용법
Snowflake는 윈도우 함수로 알려진 많은 수의 분석 SQL 함수를 지원합니다. 각 함수의 세부 사항은 개별 참조 페이지에 문서화되어 있습니다. 이 섹션의 목적은 일부 또는 모든 윈도우 함수에 적용되는 일반적인 참조 정보를 제공하는 것으로, 여기에는 OVER 절의 주요 구성 요소에 대한 자세한 구문이 포함됩니다.
- PARTITION BY 절
- ORDER BY 절
- 윈도우 프레임 구문
윈도우 함수에 익숙하지 않은 사용자는 Analyzing data with window functions (윈도우 함수로 데이터 분석)의 개념 자료를 읽어 보시길 권합니다.
본문
구문 (Syntax)
( [ ] ) OVER ( [ ] )
여기서:
windowDefinition ::=
[ PARTITION BY [, ...] ]
[ ORDER BY [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ ]
여기서:
windowFrameClause ::=
{
{ ROWS | RANGE } UNBOUNDED PRECEDING
| { ROWS | RANGE } PRECEDING
| { ROWS | RANGE } CURRENT ROW
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
| { ROWS | RANGE } BETWEEN { PRECEDING | FOLLOWING } AND { PRECEDING | FOLLOWING }
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND { PRECEDING | FOLLOWING }
| { ROWS | RANGE } BETWEEN { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}
파라미터 (Parameters)
OVER( [ windowDefinition ] )
함수가 윈도우 함수로 사용되고 있음을 지정하며, 함수가 동작하는 윈도우를 지정합니다. OVER 절은 괄호를 포함해야 하지만, 해당 함수의 요구 사항에 따라 괄호는 비어 있을 수 있습니다. 빈 OVER 절은 파티션이 없고 암시적 기본 윈도우 프레임을 가집니다.
PARTITION BY expr1
예를 들어 제품, 도시 또는 연도별로 행을 파티션으로 그룹화합니다. 입력 행은 파티션으로 그룹화된 다음 함수가 각 파티션에 대해 계산됩니다. PARTITION BY 절은 선택 사항입니다. 행 집합을 단일 파티션으로 분석할 수 있습니다.
ORDER BY expr2
각 파티션 안에서 또는 파티션이 지정되지 않으면 전체 행 집합 안에서 행을 정렬합니다. 이 ORDER BY 절은 쿼리 최종 결과에서 모든 행의 순서를 제어하는 ORDER BY 절과는 구별됩니다. ORDER BY 절은 일부 윈도우 함수에서는 선택 사항이지만 다른 함수에서는 필수입니다. 예를 들어 RANK와 NTILE 같은 순위 윈도우 함수는 입력 데이터가 의미 있는 순서로 있어야 합니다.
윈도우 함수의 ORDER BY 절은 ASC/DESC(오름차순/내림차순) 및 NULL 처리와 관련해 쿼리의 기본 ORDER BY 절과 유사한 규칙을 따릅니다. 자세한 내용은 ORDER BY를 참고하세요.
참고: 윈도우 함수의 ORDER BY 절은
OVER (PARTITION BY 1 ORDER BY 2)같은 서수 위치의 사용을 지원하지 않습니다. 이 맥락에서2는 상수2로 해석됩니다. 쿼리의 두 번째 컬럼을 가리키지 않습니다.
서로 다른 함수는 ORDER BY 절을 서로 다른 방식으로 처리합니다.
- 일부 윈도우 함수는 ORDER BY 절을 요구합니다.
- 일부 윈도우 함수는 ORDER BY 절을 금지합니다.
- 일부 윈도우 함수는 ORDER BY 절이 있으면 사용하지만 요구하지는 않습니다.
- 일부 윈도우 함수는 ORDER BY 절에 암시적 윈도우 프레임을 적용합니다. (자세한 내용은 윈도우 프레임 사용 참고 사항 참고)
{ ROWS | RANGE }
물리적 행 수(ROWS) 또는 논리적으로 계산된 행 집합(RANGE)을 정의하는 윈도우 프레임의 유형 또는 모드를 지정합니다. Range-based versus row-based window frames를 참고하세요.
두 유형의 프레임 모두 암시적 명명 경계 또는 명시적 오프셋 값을 사용해 시작점과 끝점을 지정합니다. 명명 경계는 CURRENT ROW, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING 키워드로 정의됩니다. 명시적 오프셋은 숫자 또는 간격(n PRECEDING 또는 n FOLLOWING)으로 정의됩니다.
{ RANGE BETWEEN n PRECEDING | n FOLLOWING }
명시적 오프셋을 가진 범위 기반 윈도우 프레임을 지정합니다.
명시적 오프셋을 가진 RANGE BETWEEN 윈도우 프레임은 ORDER BY 표현식이 하나만 있어야 합니다. 해당 표현식에 대해 다음 데이터 타입이 지원됩니다.
- DATE, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ (DATETIME), TIMESTAMP_TZ
- INT, FLOAT 등을 포함한 NUMBER
이 유형의 윈도우 프레임을 사용할 때 TIME 및 기타 Snowflake 데이터 타입은 지원되지 않습니다. 다른 윈도우 프레임에서는 ORDER BY 표현식에 VARCHAR 같은 다른 데이터 타입을 사용할 수 있습니다.
RANGE BETWEEN 윈도우 프레임에서 n은 부호 없는 상수(0을 포함한 양수 값) 또는 양수 INTERVAL 상수여야 합니다.
expr2가 숫자 데이터 타입이면n은 부호 없는 상수여야 합니다.expr2가 TIMESTAMP 데이터 타입이면n은 INTERVAL 상수여야 합니다. 예:INTERVAL '12 hours'또는INTERVAL '3 days'.expr2가 DATE 데이터 타입이면n은 부호 없는 상수 또는 INTERVAL 상수가 될 수 있지만, 프레임의 시작과 끝은n값에 대해 같은 데이터 타입을 사용해야 합니다.
ORDER BY 표현식이 오름차순(ASC)일 때 n FOLLOWING 구문은 "값이 x보다 크거나(또는 더 늦은) 행"을 의미하고, n PRECEDING은 "값이 x보다 작거나(또는 더 이른) 행"을 의미합니다. 여기서 x는 현재 행의 ORDER BY 값입니다. ORDER BY 표현식이 내림차순(DESC)이면 그 반대입니다. (0 PRECEDING과 0 FOLLOWING 오프셋은 CURRENT ROW와 동등합니다.)
RANGE BETWEEN 제한 사항
명시적 오프셋을 가진 RANGE BETWEEN 구문을 지원하는 윈도우 함수의 부분 집합은 다음과 같습니다.
- COUNT, SUM, MIN, MAX, AVG
- STDDEV, STDDEV_SAMP, STDDEV_POP (및 별칭)
- VARIANCE, VARIANCE_SAMP, VARIANCE_POP (및 별칭)
- COUNT_IF
- FIRST_VALUE, LAST_VALUE
- ARRAY_AGG
또한 다음에 유의하세요.
- 이 함수들의 DISTINCT 버전은 이 구문을 지원하지 않습니다.
- COUNT 윈도우 함수를 이 구문과 함께 사용할 때는 다음 제한이 적용됩니다.
- 입력 인수 하나만 지원됩니다.
COUNT(table.*)와일드카드 쿼리는 지원되지 않습니다. 예를 들어 다음은 지정할 수 없습니다:
COUNT(t1.*) OVER(ORDER BY col1 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
- 프레임 시작·끝 위치의 논리적 역전을 초래하는 프레임은 지정할 수 없습니다. 예를 들어 다음 프레임은 프레임의 끝 행이 시작 행보다 앞서므로 오류를 반환합니다:
ORDER BY col1 ASC RANGE BETWEEN 2 PRECEDING AND 4 PRECEDING
ORDER BY col1 ASC RANGE BETWEEN 2 FOLLOWING AND 2 PRECEDING
ORDER BY 표현식에 NULL 값이 있을 때 RANGE BETWEEN 동작
RANGE BETWEEN 윈도우 프레임이 사용되고 ORDER BY 컬럼이 NULL 값을 포함할 때 다음 동작에 유의하세요.
- ORDER BY 절이 NULLS FIRST를 지정하면 ORDER BY 컬럼에 NULL이 있는 행은 UNBOUNDED PRECEDING 프레임에 포함됩니다.
- ORDER BY 절이 NULLS LAST를 지정하면 ORDER BY 컬럼에 NULL이 있는 행은 UNBOUNDED FOLLOWING 프레임에 포함됩니다.
- ORDER BY 컬럼에 NULL이 있는 행은 현재 행의 ORDER BY 값이 NULL일 때만 명시적 오프셋 프레임 경계에 포함됩니다.
ORDER BY 절에 NULL 값이 있는 RANGE BETWEEN 예제를 참고하세요.
윈도우 함수의 상관 컬럼 (Correlated columns)
윈도우 함수 안(PARTITION BY나 ORDER BY 절 등)에서 상관(correlated) 컬럼을 사용하는 것은 지원되지 않습니다. 이 제한은 윈도우 함수가 LATERAL 조인이나 상관 서브쿼리에서 사용되어 외부 쿼리 블록의 컬럼을 참조하려는 경우에 적용됩니다.
다음 예제는 이 제한을 보여줍니다. 이 쿼리는 FLATTEN을 ROW_NUMBER 윈도우 함수가 있는 lateral 조인에서 사용해 JSON 데이터를 처리합니다. 함수의 ORDER BY 절에서 completed_on 컬럼(lateral 조인과 명시적으로 상관된)을 사용하면 쿼리가 오류를 반환합니다:
WITH data AS (
SELECT
PARSE_JSON('[
{"completed_on": "2024-06-03T20:17:08.621001019Z"},
{"completed_on": "2024-06-03T18:26:08.691858742Z"},
{"completed_on": "2024-06-03T14:43:40.215726239Z"}
]'
) d
)
SELECT
fields.*
FROM data,
LATERAL FLATTEN(d) AS f,
LATERAL (SELECT
f.value:completed_on AS completed_on,
ROW_NUMBER() OVER(ORDER BY f.value:completed_on DESC) rn
)fields;
SQL compilation error: Window function [ROW_NUMBER() OVER (ORDER BY GET(F.VALUE, 'completed_on') DESC NULLS FIRST)] contains a correlation.
이 문제를 해결하려면 윈도우 함수 안에서 상관 컬럼을 사용하지 마세요. 상관이 더 이상 존재하지 않는 외부 쿼리로 윈도우 함수를 이동하세요. 예를 들어:
WITH data AS (
SELECT
PARSE_JSON('[
{"completed_on": "2024-06-03T20:17:08.621001019Z"},
{"completed_on": "2024-06-03T18:26:08.691858742Z"},
{"completed_on": "2024-06-03T14:43:40.215726239Z"}
]') d
)
SELECT
fields.*,
ROW_NUMBER() OVER(ORDER BY completed_on DESC) rn
FROM
data,
LATERAL FLATTEN(d) AS f,
LATERAL (SELECT
f.value:completed_on AS completed_on
)fields;
이 쿼리는 성공적으로 실행되며 다음을 반환합니다:
+--------------------------------------+----+
| COMPLETED_ON | RN |
|--------------------------------------+----|
| "2024-06-03T20:17:08.621001019Z" | 1 |
| "2024-06-03T18:26:08.691858742Z" | 2 |
| "2024-06-03T14:43:40.215726239Z" | 3 |
+--------------------------------------+----+
ROW_NUMBER() 함수를 외부 쿼리로 이동하면 completed_on 컬럼은 더 이상 상관되지 않으며, 윈도우 함수가 올바르게 처리할 수 있습니다.
윈도우 프레임 사용 참고 사항
- 모든 윈도우 함수는 윈도우 프레임을 지원합니다. 그러나 윈도우 프레임 구문의 지원은 함수마다 다릅니다. 윈도우 프레임이 지정되지 않으면 기본값은 함수에 따라 다릅니다.
- 비순위(non-ranking) 함수(COUNT, MAX, MIN, SUM 등)의 경우 기본값은 다음 윈도우 프레임입니다(ANSI 표준에 따름):
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- 순위 함수(FIRST_VALUE, LAST_VALUE, NTH_VALUE 등)의 경우 기본값은 전체 윈도우입니다:
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
이 동작은 ANSI 표준을 따르지 않습니다.
참고: 명확성을 위해 Snowflake는 암시적 윈도우 프레임을 피하는 것을 권장합니다. 쿼리가 윈도우 프레임을 사용한다면 명시적 윈도우 프레임을 정의하세요.
- 윈도우 프레임은 윈도우의 데이터가 알려진 순서로 있기를 요구합니다. 따라서 OVER 절 안의 ORDER BY 절은 일반적으로 선택 사항이지만 윈도우 프레임 구문에는 필수입니다.
예제
이 섹션에는 윈도우 함수를 다양한 방식으로 사용하는 방법을 보여주는 예제가 포함됩니다. 추가 예제는 Analyzing data with window functions과 개별 함수의 페이지를 참고하세요.
입문 예제
여러분이 체인 매장을 운영한다고 가정해 봅시다. 다음 쿼리는 체인의 총 이익에서 각 매장이 차지하는 비율을 보여줍니다. 이 쿼리는 현재 행의 값(net_profit)을 다른 모든 행의 해당 값의 합으로 나누는 RATIO_TO_REPORT 함수를 사용합니다.
테이블 생성 및 로딩:
CREATE TRANSIENT TABLE store_sales (
branch_ID INTEGER,
city VARCHAR,
gross_sales NUMERIC(9, 2),
gross_costs NUMERIC(9, 2),
net_profit NUMERIC(9, 2)
);
INSERT INTO store_sales (branch_ID, city, gross_sales, gross_costs)
VALUES
(1, 'Vancouver', 110000, 100000),
(2, 'Vancouver', 140000, 125000),
(3, 'Montreal', 150000, 140000),
(4, 'Montreal', 155000, 146000);
UPDATE store_sales SET net_profit = gross_sales - gross_costs;
테이블 조회:
SELECT branch_ID,
net_profit,
100 * RATIO_TO_REPORT(net_profit) OVER () AS percent_of_chain_profit
FROM store_sales AS s1
ORDER BY branch_ID;
+-----------+------------+-------------------------+
| BRANCH_ID | NET_PROFIT | PERCENT_OF_CHAIN_PROFIT |
|-----------+------------+-------------------------|
| 1 | 10000.00 | 22.72727300 |
| 2 | 15000.00 | 34.09090900 |
| 3 | 10000.00 | 22.72727300 |
| 4 | 9000.00 | 20.45454500 |
+-----------+------------+-------------------------+
무한대 시작 위치의 윈도우 프레임
값이 있는 테이블 생성 및 채우기:
CREATE OR REPLACE TABLE example_cumulative (p INT, o INT, i INT);
INSERT INTO example_cumulative VALUES
( 0, 1, 10), (0, 2, 20), (0, 3, 30),
(100, 1, 10),(100, 2, 30),(100, 2, 5),(100, 3, 11),(100, 3, 120),
(200, 1, 10000),(200, 1, 200),(200, 1, 808080),(200, 2, 33333),(200, 3, null), (200, 3, 4),
(300, 1, null), (300, 1, null);
무한대 시작 위치의 윈도우 프레임을 사용하는 쿼리를 실행하고 출력을 보여줍니다. 각 파티션의 각 행에 대해 누적 COUNT, SUM, AVG, MIN, MAX 값을 반환합니다:
SELECT
p, o, i,
COUNT(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Rows_Pre,
SUM(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Rows_Pre,
AVG(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Rows_Pre,
MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Rows_Pre,
MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Rows_Pre
FROM example_cumulative
ORDER BY p,o;
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
| P | O | I | COUNT_I_ROWS_PRE | SUM_I_ROWS_PRE | AVG_I_ROWS_PRE | MIN_I_ROWS_PRE | MAX_I_ROWS_PRE |
|-----+---+--------+------------------+----------------+----------------+----------------+----------------|
| 0 | 1 | 10 | 1 | 10 | 10.000 | 10 | 10 |
| 0 | 2 | 20 | 2 | 30 | 15.000 | 10 | 20 |
| 0 | 3 | 30 | 3 | 60 | 20.000 | 10 | 30 |
| 100 | 1 | 10 | 1 | 10 | 10.000 | 10 | 10 |
| 100 | 2 | 30 | 2 | 40 | 20.000 | 10 | 30 |
| 100 | 2 | 5 | 3 | 45 | 15.000 | 5 | 30 |
| 100 | 3 | 11 | 4 | 56 | 14.000 | 5 | 30 |
| 100 | 3 | 120 | 5 | 176 | 35.200 | 5 | 120 |
| 200 | 1 | 10000 | 1 | 10000 | 10000.000 | 10000 | 10000 |
| 200 | 1 | 200 | 2 | 10200 | 5100.000 | 200 | 10000 |
| 200 | 1 | 808080 | 3 | 818280 | 272760.000 | 200 | 808080 |
| 200 | 2 | 33333 | 4 | 851613 | 212903.250 | 200 | 808080 |
| 200 | 3 | NULL | 4 | 851613 | 212903.250 | 200 | 808080 |
| 200 | 3 | 4 | 5 | 851617 | 170323.400 | 4 | 808080 |
| 300 | 1 | NULL | 0 | NULL | NULL | NULL | NULL |
| 300 | 1 | NULL | 0 | NULL | NULL | NULL | NULL |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
기본 윈도우 프레임(즉, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)을 사용해 위 쿼리와 같은 결과를 반환합니다:
SELECT
p, o, i,
COUNT(i) OVER (PARTITION BY p ORDER BY o) count_i_Range_Pre,
SUM(i) OVER (PARTITION BY p ORDER BY o) sum_i_Range_Pre,
AVG(i) OVER (PARTITION BY p ORDER BY o) avg_i_Range_Pre,
MIN(i) OVER (PARTITION BY p ORDER BY o) min_i_Range_Pre,
MAX(i) OVER (PARTITION BY p ORDER BY o) max_i_Range_Pre
FROM example_cumulative
ORDER BY p,o;
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
| P | O | I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
| 0 | 1 | 10 | 1 | 10 | 10.000000 | 10 | 10 |
| 0 | 2 | 20 | 2 | 30 | 15.000000 | 10 | 20 |
| 0 | 3 | 30 | 3 | 60 | 20.000000 | 10 | 30 |
| 100 | 1 | 10 | 1 | 10 | 10.000000 | 10 | 10 |
| 100 | 2 | 30 | 3 | 45 | 15.000000 | 5 | 30 |
| 100 | 2 | 5 | 3 | 45 | 15.000000 | 5 | 30 |
| 100 | 3 | 11 | 5 | 176 | 35.200000 | 5 | 120 |
| 100 | 3 | 120 | 5 | 176 | 35.200000 | 5 | 120 |
| 200 | 1 | 10000 | 3 | 818280 | 272760.000000 | 200 | 808080 |
| 200 | 1 | 200 | 3 | 818280 | 272760.000000 | 200 | 808080 |
| 200 | 1 | 808080 | 3 | 818280 | 272760.000000 | 200 | 808080 |
| 200 | 2 | 33333 | 4 | 851613 | 212903.250000 | 200 | 808080 |
| 200 | 3 | NULL | 5 | 851617 | 170323.400000 | 4 | 808080 |
| 200 | 3 | 4 | 5 | 851617 | 170323.400000 | 4 | 808080 |
| 300 | 1 | NULL | 0 | NULL | NULL | NULL | NULL |
| 300 | 1 | NULL | 0 | NULL | NULL | NULL | NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
명시적 오프셋이 있는 윈도우 프레임
값이 있는 테이블 생성 및 채우기:
CREATE TABLE example_sliding
(p INT, o INT, i INT, r INT, s VARCHAR(100));
INSERT INTO example_sliding VALUES
(100,1,1,70,'seventy'),(100,2,2,30, 'thirty'),(100,3,3,40,'forty'),(100,4,NULL,90,'ninety'),
(100,5,5,50,'fifty'),(100,6,6,30,'thirty'),
(200,7,7,40,'forty'),(200,8,NULL,NULL,'n_u_l_l'),(200,9,NULL,NULL,'n_u_l_l'),(200,10,10,20,'twenty'),
(200,11,NULL,90,'ninety'),
(300,12,12,30,'thirty'),
(400,13,NULL,20,'twenty');
현재 행의 앞, 뒤, 포함하는 슬라이딩 윈도우에 걸쳐 두 컬럼(숫자와 문자열)에 대한 MIN 함수 결과를 반환합니다:
select p, o, i AS i_col,
MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_i_3P_1P,
MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_i_1F_3F,
MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_i_1P_3F,
s,
MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_s_3P_1P,
MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_s_1F_3F,
MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_s_1P_3F
FROM example_sliding
ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
| P | O | I_COL | MIN_I_3P_1P | MIN_I_1F_3F | MIN_I_1P_3F | S | MIN_S_3P_1P | MIN_S_1F_3F | MIN_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 | 1 | 1 | NULL | 2 | 1 | seventy | NULL | forty | forty |
| 100 | 2 | 2 | 1 | 3 | 1 | thirty | seventy | fifty | fifty |
| 100 | 3 | 3 | 1 | 5 | 2 | forty | seventy | fifty | fifty |
| 100 | 4 | NULL | 1 | 5 | 3 | ninety | forty | fifty | fifty |
| 100 | 5 | 5 | 2 | 6 | 5 | fifty | forty | thirty | fifty |
| 100 | 6 | 6 | 3 | NULL | 5 | thirty | fifty | NULL | fifty |
| 200 | 7 | 7 | NULL | 10 | 7 | forty | NULL | n_u_l_l | forty |
| 200 | 8 | NULL | 7 | 10 | 7 | n_u_l_l | forty | n_u_l_l | forty |
| 200 | 9 | NULL | 7 | 10 | 10 | n_u_l_l | forty | ninety | n_u_l_l |
| 200 | 10 | 10 | 7 | NULL | 10 | twenty | forty | ninety | n_u_l_l |
| 200 | 11 | NULL | 10 | NULL | 10 | ninety | n_u_l_l | NULL | ninety |
| 300 | 12 | 12 | NULL | NULL | 12 | thirty | NULL | NULL | thirty |
| 400 | 13 | NULL | NULL | NULL | NULL | twenty | NULL | NULL | twenty |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
현재 행의 앞, 뒤, 포함하는 슬라이딩 윈도우에 걸쳐 두 컬럼(숫자와 문자열)에 대한 MAX 함수 결과를 반환합니다:
SELECT p, o, i AS i_col,
MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_i_3P_1P,
MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_i_1F_3F,
MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_i_1P_3F,
s,
MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_s_3P_1P,
MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_s_1F_3F,
MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_s_1P_3F
FROM example_sliding
ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
| P | O | I_COL | MAX_I_3P_1P | MAX_I_1F_3F | MAX_I_1P_3F | S | MAX_S_3P_1P | MAX_S_1F_3F | MAX_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 | 1 | 1 | NULL | 3 | 3 | seventy | NULL | thirty | thirty |
| 100 | 2 | 2 | 1 | 5 | 5 | thirty | seventy | ninety | thirty |
| 100 | 3 | 3 | 2 | 6 | 6 | forty | thirty | thirty | thirty |
| 100 | 4 | NULL | 3 | 6 | 6 | ninety | thirty | thirty | thirty |
| 100 | 5 | 5 | 3 | 6 | 6 | fifty | thirty | thirty | thirty |
| 100 | 6 | 6 | 5 | NULL | 6 | thirty | ninety | NULL | thirty |
| 200 | 7 | 7 | NULL | 10 | 10 | forty | NULL | twenty | twenty |
| 200 | 8 | NULL | 7 | 10 | 10 | n_u_l_l | forty | twenty | twenty |
| 200 | 9 | NULL | 7 | 10 | 10 | n_u_l_l | n_u_l_l | twenty | twenty |
| 200 | 10 | 10 | 7 | NULL | 10 | twenty | n_u_l_l | ninety | twenty |
| 200 | 11 | NULL | 10 | NULL | 10 | ninety | twenty | NULL | twenty |
| 300 | 12 | 12 | NULL | NULL | 12 | thirty | NULL | NULL | thirty |
| 400 | 13 | NULL | NULL | NULL | NULL | twenty | NULL | NULL | twenty |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
현재 행의 앞, 뒤, 포함하는 슬라이딩 윈도우에 걸쳐 숫자 컬럼의 합을 반환합니다:
SELECT p, o, r AS r_col,
SUM(r) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) sum_r_4P_2P,
sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) sum_r_2F_4F,
sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 PRECEDING AND 4 FOLLOWING) sum_r_2P_4F
FROM example_sliding
ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+
| P | O | R_COL | SUM_R_4P_2P | SUM_R_2F_4F | SUM_R_2P_4F |
|-----+----+-------+-------------+-------------+-------------|
| 100 | 1 | 70 | NULL | 180 | 280 |
| 100 | 2 | 30 | NULL | 170 | 310 |
| 100 | 3 | 40 | 70 | 80 | 310 |
| 100 | 4 | 90 | 100 | 30 | 240 |
| 100 | 5 | 50 | 140 | NULL | 210 |
| 100 | 6 | 30 | 160 | NULL | 170 |
| 200 | 7 | 40 | NULL | 110 | 150 |
| 200 | 8 | NULL | NULL | 110 | 150 |
| 200 | 9 | NULL | 40 | 90 | 150 |
| 200 | 10 | 20 | 40 | NULL | 110 |
| 200 | 11 | 90 | 40 | NULL | 110 |
| 300 | 12 | 30 | NULL | NULL | 30 |
| 400 | 13 | 20 | NULL | NULL | 20 |
+-----+----+-------+-------------+-------------+-------------+
순위 함수 예제
다음 예제는 각 영업사원이 판매한 총액(달러)을 기준으로 판매를 순위 매기는 방법을 보여줍니다. OVER 절 안의 ORDER BY 절은 총액을 내림차순(높은 것에서 낮은 것)으로 정렬합니다. 이 쿼리는 다른 모든 영업사원에 비해 각 영업사원의 순위를 계산합니다.
테이블 생성 및 데이터 삽입:
CREATE TABLE sales_table (salesperson_name VARCHAR, sales_in_dollars INTEGER);
INSERT INTO sales_table (salesperson_name, sales_in_dollars) VALUES
('Smith', 600),
('Jones', 1000),
('Torkelson', 700),
('Dolenz', 800);
이제 데이터를 조회합니다:
SELECT
salesperson_name,
sales_in_dollars,
RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
FROM sales_table;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones | 1000 | 1 |
| Dolenz | 800 | 2 |
| Torkelson | 700 | 3 |
| Smith | 600 | 4 |
+------------------+------------------+------------+
출력은 반드시 순위순으로 정렬되지는 않습니다. 순위순으로 정렬된 결과를 표시하려면 윈도우 함수의 ORDER BY 절에 더해 쿼리 자체에도 ORDER BY 절을 지정하세요(아래 참고):
SELECT
salesperson_name,
sales_in_dollars,
RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
FROM sales_table
ORDER BY 3;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones | 1000 | 1 |
| Dolenz | 800 | 2 |
| Torkelson | 700 | 3 |
| Smith | 600 | 4 |
+------------------+------------------+------------+
앞의 예제에는 두 ORDER BY 절이 있습니다.
- 하나는 순위의 순서를 제어합니다.
- 하나는 출력의 순서를 제어합니다.
이 절들은 독립적입니다. 예를 들어 순위는 총 매출 기준으로 정렬하되(위와 같음), 출력 행은 영업사원의 성(last name) 기준으로 정렬할 수 있습니다:
SELECT
salesperson_name,
sales_in_dollars,
RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
FROM sales_table
ORDER BY salesperson_name;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Dolenz | 800 | 2 |
| Jones | 1000 | 1 |
| Smith | 600 | 4 |
| Torkelson | 700 | 3 |
+------------------+------------------+------------+
명시적 숫자 오프셋이 있는 RANGE BETWEEN 예제
다음 예제는 명시적 숫자 오프셋이 있는 RANGE BETWEEN 구문을 사용합니다. 이 예제를 실행하려면 Create and load the menu_items table 지침을 따르세요. INTERVAL 오프셋을 사용하는 유사한 예제는 Using windowed aggregations for rolling calculations를 참고하세요.
다음 쿼리는 푸드트럭에서 제공되는 메뉴 항목 범주의 매출 원가(sales) 평균을 계산합니다. 윈도우 함수는 결과를 파티션하지 않습니다. 따라서 평균은 범위 기반 프레임에 따라 완전한 결과 집합에 걸쳐 계산됩니다.
프레임의 경계는 현재 행의 매출 원가 값에 2를 더한 값입니다(예: 첫 번째 행 = 0.50 + 2.00). 이 2달러 범위 안에 드는 행이 프레임에 포함됩니다.
SELECT menu_category, menu_cogs_usd,
AVG(menu_cogs_usd)
OVER(ORDER BY menu_cogs_usd RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) avg_cogs
FROM menu_items
WHERE menu_category IN('Beverage','Dessert','Snack')
GROUP BY menu_category, menu_cogs_usd
ORDER BY menu_category, menu_cogs_usd;
+---------------+---------------+----------+
| MENU_CATEGORY | MENU_COGS_USD | AVG_COGS |
|---------------+---------------+----------|
| Beverage | 0.50 | 1.18333 |
| Beverage | 0.65 | 1.37857 |
| Beverage | 0.75 | 1.50000 |
| Dessert | 0.50 | 1.18333 |
| Dessert | 1.00 | 1.87500 |
| Dessert | 1.25 | 2.05000 |
| Dessert | 2.50 | 3.16666 |
| Dessert | 3.00 | 3.50000 |
| Snack | 1.25 | 2.05000 |
| Snack | 2.25 | 2.93750 |
| Snack | 4.00 | 4.00000 |
+---------------+---------------+----------+
예를 들어 첫 번째 행의 avg_cogs 값은 1.1833입니다. 이는 0.50과 2.50 사이에 드는 모든 menu_cogs_usd 값의 합을 그 행의 수로 나눈 것입니다:
(0.50 + 0.65 + 0.75 + 0.50 + 1.00 + 1.25 + 2.50 + 1.25 + 2.25) / 9 = 1.18333
끝에서 두 번째 행은 avg_cogs 값이 2.93750입니다. 이는 2.25와 4.25 사이에 드는 모든 menu_cogs_usd 값의 합을 그 행의 수로 나눈 것입니다:
(2.25 + 2.50 + 3.00 + 4.00) / 4 = 2.93750
마지막 행은 avg_cogs와 menu_cogs_usd 모두 4.0을 반환합니다. 이 결과는 이 행만 프레임에 속하므로 정확합니다. 4.0은 전체 결과에서 최대 menu_cogs_usd 값이므로 단일 행 프레임이 됩니다. "뒤따르는(following)" 행이 없습니다.
이 쿼리는 윈도우 함수용 ORDER BY 절과 쿼리 최종 결과용 ORDER BY 절이 있다는 점에 유의하세요. 최종 ORDER BY 출력은 윈도우 함수 결과 계산에 영향을 주지 않습니다. 함수 계산을 위한 정렬된 결과 집합은 최종 쿼리가 표시하지 않는 중간 결과 집합입니다.
ORDER BY 절에 NULL 값이 있는 RANGE BETWEEN 예제
nulls 테이블은 5개의 행을 포함하며, 그중 2개는 c1 컬럼에 NULL 값을 가집니다. 다음과 같이 테이블을 만들고 로드합니다:
CREATE OR REPLACE TABLE nulls(c1 int, c2 int);
INSERT INTO nulls VALUES
(1,10),
(2,20),
(3,30),
(NULL,20),
(NULL,50);
NULLS LAST가 지정되고 윈도우 프레임이 명시적 오프셋을 사용하면, c1에 NULL이 있는 행은 현재 행의 ORDER BY 값이 NULL일 때만 프레임에 포함됩니다. 다음 쿼리는 행 3이 현재 행일 때 합 50을 반환합니다. 다음 NULL 행은 프레임에 포함되지 않습니다.
SELECT c1 c1_nulls_last, c2,
SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum_c2
FROM nulls;
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
| 1 | 10 | 30 |
| 2 | 20 | 60 |
| 3 | 30 | 50 |
| NULL | 20 | 70 |
| NULL | 50 | 70 |
+---------------+----+--------+
NULLS LAST가 지정되고 윈도우 프레임이 UNBOUNDED FOLLOWING을 사용하면, c1에 NULL이 있는 행은 프레임에 포함됩니다. 다음 쿼리는 행 3이 현재 행일 때 합 120을 반환합니다. 두 뒤따르는 NULL 행 모두 프레임에 포함됩니다.
SELECT c1 c1_nulls_last, c2,
SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) sum_c2
FROM nulls;
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
| 1 | 10 | 130 |
| 2 | 20 | 130 |
| 3 | 30 | 120 |
| NULL | 20 | 70 |
| NULL | 50 | 70 |
+---------------+----+--------+
heavy_weather 테이블 생성 및 로드
일부 윈도우 함수 예제에서 사용되는 heavy_weather 테이블을 만들고 행을 삽입하려면 다음 스크립트를 실행하세요.
CREATE OR REPLACE TABLE heavy_weather
(start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));
INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;