윈도우 함수 구문 및 사용법¶
이 항목의 내용:
Snowflake는 윈도우 함수 라고 알려진 수많은 분석 SQL 함수를 지원합니다. 각 함수에 대한 세부 정보는 개별 참조 페이지에 문서화되어 있습니다. 이 섹션의 목적은 OVER 절의 주요 구성 요소에 대한 자세한 구문을 포함하여 일부 또는 모든 윈도우 함수에 적용되는 일반적인 참조 정보를 제공하는 것입니다.
PARTITION BY 절
ORDER BY 절
윈도우 프레임 구문
윈도우 함수에 익숙하지 않은 사용자는 윈도우 함수로 데이터 분석하기 에서 개념을 설명한 자료를 읽어보면 도움이 될 것입니다.
구문¶
<function> ( [ <arguments> ] ) OVER ( [ <windowDefinition> ] )
여기서
windowDefinition ::=
[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]
여기서
windowFrameClause ::=
{
{ ROWS | RANGE } UNBOUNDED PRECEDING
| { ROWS | RANGE } <n> 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 <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
| { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
| { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}
매개 변수¶
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 절을 다른 방식으로 처리합니다.
일부 윈도우 함수에는 ORDER BY 절이 필요합니다.
일부 윈도우 함수에서는 ORDER BY 절을 사용할 수 없습니다.
일부 윈도우 함수에서는 ORDER BY 절이 있으면 사용하지만 필수가 아닙니다.
일부 윈도우 함수는 ORDER BY 절에 암시적인 윈도우 프레임을 적용합니다. (자세한 내용은 윈도우 프레임 사용법 노트 섹션을 참조하십시오.)
{ ROWS | RANGE }행의 실제 개수(ROWS) 또는 논리적으로 계산된 행 세트(RANGE)를 정의하는 윈도우 프레임의 유형 또는 모드를 지정합니다. 범위 기반 윈도우 프레임 대 행 기반 윈도우 프레임 섹션을 참조하십시오.
두 유형의 프레임은 모두 명명된 암시적 경계 또는 명시적 오프셋 값을 사용하여 시작점과 끝점을 지정합니다. 명명된 경계는 키워드 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 데이터 타입은 지원되지 않습니다. 다른 윈도우 프레임의 경우 VARCHAR 등 다른 데이터 타입을 ORDER BY 식에서 사용할 수 있습니다.
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 구문을 지원합니다.
STDDEV, STDDEV_SAMP, STDDEV_POP (및 별칭)
VARIANCE , VARIANCE_SAMP, VARIANCE_POP (및 별칭)
또한 다음 사항에 유의하십시오.
이들 함수의 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 예제 섹션을 참조하십시오.
윈도우 프레임 사용법 노트¶
모든 윈도우 함수는 윈도우 프레임을 지원합니다. 하지만 윈도우 프레임 구문에 대한 지원은 함수별로 다릅니다. 지정된 윈도우 프레임이 없는 경우 기본값은 함수에 따라 다릅니다.
순위를 지정하지 않는 함수(예: 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는 암시적 윈도우 프레임을 피할 것을 권장합니다. 쿼리가 윈도우 프레임을 사용하는 경우 명시적 윈도우 프레임을 정의합니다.
윈도우 프레임에서는 윈도우의 데이터가 알려진 순서대로 되어 있어야 합니다. 따라서 ORDER BY 절은 일반적으로 선택 사항임에도 OVER 절 내부의 ORDER BY 절은 윈도우 프레임 구문에 필수 사항 입니다.
예¶
이 섹션에는 윈도우 함수를 다양한 방식으로 사용하는 방법을 보여주는 예제가 수록되어 있습니다. 추가 예제를 보려면 윈도우 함수로 데이터 분석하기 및 개별 함수 페이지를 참조하십시오.
소개 예¶
상점 체인을 소유하고 있다고 가정해보십시오. 다음 쿼리는 각 상점에서 올리는 이익이 체인 총이익 중 차지하는 비율을 보여줍니다. 이 쿼리는 RATIO_TO_REPORT 함수를 사용하여 현재 행에서 값(net_profit)을 가져와 다른 모든 행에서 대응하는 값의 합으로 나눕니다.
테이블을 만들고 로딩합니다.
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 절이 있습니다.
하나는 순위의 순서를 제어합니다.
하나는 출력의 순서를 제어합니다.
이러한 절은 독립적입니다. 예를 들어, (위에 표시된 대로) 총매출을 기준으로 순위를 정렬할 수 있지만, 출력 행은 영업사원의 성을 기준으로 정렬할 수 있습니다.
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 구문을 사용합니다. 이 예제를 실행하려면 다음 지침을 따르십시오. menu_items 테이블 생성 및 로드하기 INTERVAL 오프셋을 사용하는 유사한 예를 보려면 롤링 계산을 위한 윈도우 집계 사용하기 섹션을 참조하십시오.
다음 쿼리는 푸드트럭에서 판매하는 메뉴 항목 카테고리에 대해 판매되는 상품의 평균 가격을 계산합니다. 윈도우 함수는 결과를 분할하지 않으므로, 평균은 범위 기반 프레임에 따라 전체 결과 세트에 걸쳐 계산됩니다.
프레임의 경계는 현재 행의 상품 가격 값에 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 값이므로 단일 행 프레임이 됩니다. “다음” 행이 없습니다.
이 쿼리에는 윈도우 함수에 대한 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가 지정되고 윈도우 프레임이 명시적 오프셋을 사용하는 경우, 현재 행의 ORDER BY 값이 NULL일 때만 c1 에 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')
;