윈도우 함수¶

윈도우 함수는 관련 행의 그룹(“윈도우”)에서 작동합니다.

윈도우 함수가 호출될 때마다 행(윈도우의 현재 행)과 현재 행을 포함한 행으로 구성된 윈도우가 전달됩니다. 윈도우 함수는 각 입력 행에 대해 하나의 출력 행을 반환합니다. 출력은 함수로 전달된 개별 행과 함수로 전달된 윈도우에서 다른 행의 값에 따라 다릅니다.

일부 윈도우 함수에서는 순서를 구분합니다. 순서를 구분하는 윈도우 함수의 주요 유형은 다음과 같이 2개가 있습니다.

순위 관련 함수

윈도우 프레임 함수

순위 관련 함수는 행의 “순위”를 기반으로 정보를 나열합니다. 예를 들어, 연도별 수익을 기준으로 상점을 내림차순으로 순위를 매길 경우 수익이 가장 많은 상점이 1위가 되고 두 번째로 많은 상점이 2위가 되는 식으로 순위가 매겨집니다.

윈도우 프레임 함수를 사용하면 윈도우에 있는 행의 서브세트에 대해 누계 또는 이동 평균 계산과 같은 이동 작업을 수행할 수 있습니다.

윈도우 함수, 순위 관련 함수 또는 윈도우 프레임 함수에 익숙하지 않은 사용자는 윈도우 함수 사용하기 에서 개념을 설명한 자료를 읽어보면 도움이 될 것입니다.

이 항목의 내용:

개요¶

윈도우란 무엇입니까?¶

윈도우는 관련된 행의 그룹입니다. 예를 들어, 같은 달의 모든 행이 같은 윈도우에 그룹화되어 있는 타임스탬프를 기반으로 윈도우를 정의할 수 있습니다. 또는 특정 도시의 모든 행이 같은 윈도우에 그룹화된 상태에서 위치를 기반으로 윈도우를 정의할 수도 있습니다.

윈도우는 0개, 1개 또는 여러 개의 행으로 구성될 수 있습니다. 단순성을 기하기 위해, Snowflake 설명서에서는 보통 윈도우에 여러 행이 있는 것으로 설명합니다.

윈도우 함수란 무엇입니까?¶

윈도우 함수는 행 윈도우를 통해 작동하는 모든 함수를 말합니다.

윈도우 함수에는 일반적으로 다음 두 매개 변수가 전달됩니다.

행. 더 정확하게는, 윈도우 함수에 0개 또는 그 이상의 식이 전달됩니다. 거의 모든 경우에 그와 같은 식 중 하나 이상이 해당 행의 열을 참조합니다. (대부분의 윈도우 함수에는 하나 이상의 열이나 식이 필요하지만, 순위 관련 함수와 같은 몇 가지 윈도우 함수에는 명시적인 열이나 식이 필요하지 않습니다.)
해당 행을 포함하는 관련된 행의 윈도우. 윈도우는 전체 테이블이거나 테이블에 있는 행의 서브세트일 수 있습니다.

윈도우 함수가 아닌 함수의 경우, 모든 인자는 보통 함수에 명시적으로 전달됩니다. 예를 들면 다음과 같습니다.

MY_FUNCTION(인자1, 인자2, …)

윈도우 함수는 다르게 작동합니다. 현재 행은 일반적인 방식에 따라 인자로 전달되지만, 윈도우는 OVER 절이라는 별도의 절을 통해 전달됩니다. OVER 절의 구문은 뒷부분에서 설명합니다.

윈도우를 지원하는 함수 목록¶

아래 목록에는 모든 윈도우 함수가 나와 있습니다.

윈도우 프레임 함수로 나열된 일부 함수는 가능한 모든 유형의 윈도우 프레임을 지원하지 않습니다.

함수 이름	윈도우	윈도우 프레임	순위 관련	참고
일반 윈도우
ANY_VALUE	✔
AVG	✔	✔
CONDITIONAL_CHANGE_EVENT	✔
CONDITIONAL_TRUE_EVENT	✔
CORR	✔
COUNT	✔	✔
COUNT_IF	✔	✔
COVAR_POP	✔
COVAR_SAMP	✔
LISTAGG	✔			표준 윈도우 구문을 사용하지 않습니다.
MAX	✔	✔
MEDIAN	✔
MIN	✔	✔
MODE	✔
PERCENTILE_CONT	✔			다른 윈도우 함수와 다른 구문을 사용합니다.
PERCENTILE_DISC	✔			다른 윈도우 함수와 다른 구문을 사용합니다.
RATIO_TO_REPORT	✔
STDDEV, STDDEV_SAMP	✔	✔		STDDEV와 STDDEV_SAMP는 별칭입니다.
STDDEV_POP	✔	✔
SUM	✔	✔
VAR_POP	✔	✔
VAR_SAMP	✔	✔
VARIANCE_POP	✔	✔		VAR_POP 의 별칭입니다.
VARIANCE , VARIANCE_SAMP	✔	✔		VAR_SAMP 의 별칭입니다.

순위 관련
CUME_DIST	✔		✔
DENSE_RANK	✔	✔	✔
FIRST_VALUE	✔	✔	✔
LAG	✔		✔
LAST_VALUE	✔	✔	✔
LEAD	✔		✔
NTH_VALUE	✔	✔	✔
NTILE	✔		✔
PERCENT_RANK	✔	✔	✔	범위 기반 누적 윈도우 프레임을 지원하지만, 다른 유형의 윈도우 프레임은 지원하지 않습니다.
RANK	✔	✔	✔
ROW_NUMBER	✔		✔
비트 집계
BITAND_AGG	✔
BITOR_AGG	✔
BITXOR_AGG	✔
부울 집계
BOOLAND_AGG	✔
BOOLOR_AGG	✔
BOOLXOR_AGG	✔
해시
HASH_AGG	✔
반정형 데이터 집계
ARRAY_AGG	✔
OBJECT_AGG	✔
고유 값 계산
ARRAY_UNION_AGG	✔
ARRAY_UNIQUE_AGG	✔
선형 회귀
REGR_AVGX	✔
REGR_AVGY	✔
REGR_COUNT	✔
REGR_INTERCEPT	✔
REGR_R2	✔
REGR_SLOPE	✔
REGR_SXX	✔
REGR_SXY	✔
REGR_SYY	✔
통계 및 확률
KURTOSIS	✔
카디널리티 추정 . (HyperLogLog 사용)
APPROX_COUNT_DISTINCT	✔			HLL 의 별칭입니다.
HLL	✔
HLL_ACCUMULATE	✔
HLL_COMBINE	✔
HLL_ESTIMATE				집계 함수가 아니며, HLL_ACCUMULATE 또는 HLL_COMBINE 의 스칼라 입력값을 사용합니다.
HLL_EXPORT	✔
HLL_IMPORT	✔
유사성 추정 . (MinHash 사용)
APPROXIMATE_JACCARD_INDEX	✔			APPROXIMATE_SIMILARITY 의 별칭입니다.
APPROXIMATE_SIMILARITY	✔
MINHASH	✔
MINHASH_COMBINE	✔
빈도 추정 . (공간 절약 사용)
APPROX_TOP_K	✔
APPROX_TOP_K_ACCUMULATE	✔
APPROX_TOP_K_COMBINE	✔
APPROX_TOP_K_ESTIMATE				집계 함수가 아니며, APPROX_TOP_K_ACCUMULATE 또는 APPROX_TOP_K_COMBINE 의 스칼라 입력값을 사용합니다.
백분위수 추정 . (t-Digest 사용)
APPROX_PERCENTILE	✔
APPROX_PERCENTILE_ACCUMULATE	✔
APPROX_PERCENTILE_COMBINE	✔
APPROX_PERCENTILE_ESTIMATE				집계 함수가 아니며, APPROX_PERCENTILE_ACCUMULATE 또는 APPROX_PERCENTILE_COMBINE 의 스칼라 입력값을 사용합니다.

윈도우 함수 및 NULL 값¶

일부 함수는 NULL 값을 무시합니다. 예를 들어, AVG 는 다음 수식을 기반으로 값 1, 5, NULL 의 평균이 3 인 것으로 계산합니다.

(1 + 5) / 2 = 3

분자와 분모에서 모두 NULL이 아닌 값만 사용합니다.

함수에 전달된 모든 값이 NULL인 경우 함수는 NULL을 반환합니다.

일부 윈도우 함수에는 둘 이상의 열이 전달될 수 있습니다. 예:

SELECT COUNT(col1, col2) FROM table1;

Copy

이런 경우에는 개별 열이 NULL이면 함수가 행을 무시합니다.

예를 들어, 다음 쿼리에서는 선택한 열에서 네 행 중 세 행에 하나 이상의 NULL 값이 있으므로 COUNT 는 4 가 아니라 1 을 반환합니다.

다음과 같이 테이블을 만들고 값으로 채웁니다.

CREATE TABLE t (x INT, y INT);
INSERT INTO t (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.

Copy

테이블을 쿼리합니다.

SELECT COUNT(x, y) FROM t;
+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+

Copy

마찬가지로, 두 개 이상의 열을 참조하는 식으로 SUM 을 호출하고 해당 열 중 하나 이상이 NULL인 경우 이 식은 NULL로 계산되고 행은 무시됩니다.

SELECT SUM(x + y) FROM t;
+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+

Copy

다음과 같이 이 동작은 일부 열이 NULL일 때 행을 삭제하지 않는 GROUP BY 의 동작과 다릅니다.

SELECT x AS X_COL, y AS Y_COL FROM t GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+

Copy

소개 예¶

상점 체인을 소유하고 있다고 가정해보십시오. 다음 쿼리는 각 상점에서 올리는 이익이 체인 총이익 중 차지하는 비율을 보여줍니다. 이 쿼리는 RATIO_TO_REPORT 함수를 사용하여 현재 행에서 값(예: net_profit)을 가져와 다른 모든 행에서 대응하는 값(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;

Copy

테이블을 쿼리합니다.

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 |
+-----------+------------+-------------------------+

Copy

윈도우 프레임이란 무엇입니까?¶

윈도우 프레임은 윈도우에서 행의 하위 그룹입니다. 서브세트를 만들면 행의 지정된 하위 그룹에 대해서만 값을 계산할 수 있습니다. 윈도우 프레임은 OVER 절의 ORDER BY 하위 절에서 추가 하위 절로 지정됩니다.

윈도우 프레임의 유형¶

Snowflake에서 지원하는 두 가지 타입의 윈도우 프레임은 다음과 같습니다.

누적:: 윈도우의 시작부터 현재 행까지, 또는 현재 행부터 윈도우의 끝까지 이동하는 값을 계산할 수 있습니다.
슬라이딩:: 현재 행을 기준으로 윈도우의 두 행 사이(두 행도 포함)에서 이동하는 값을 계산할 수 있습니다.

윈도우 프레임에 사용되는 구문을 포함하여, 윈도우 프레임에 대한 자세한 내용은 윈도우 프레임 구문과 사용법 을 참조하십시오.

윈도우 구문과 사용법¶

윈도우 구문¶

<function> ( [ <arguments> ] ) OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )

Copy

OVER 절은 함수가 윈도우 함수로 사용되고 있음을 지정합니다.

PARTITION BY 하위 절을 사용해 행을 하위 그룹으로(예: 도시, 연도 등을 기준으로) 분류할 수 있습니다. PARTITION BY 절은 선택 사항입니다. 행 그룹 전체를 하위 그룹으로 나누지 않고 분석할 수 있습니다.

ORDER BY 절은 윈도우 내에서 행의 순서를 지정합니다. (이것은 쿼리의 출력 순서 지정과는 다릅니다. 쿼리에는 윈도우 내의 행 순서를 제어하는 하나의 ORDER BY 절과 OVER 절 외부에서 전체 쿼리의 출력 순서를 제어하는 별개의 ORDER BY 절이 있을 수 있습니다.) ORDER BY 절은 어떤 윈도우 함수에서는 선택 사항이지만, 다른 함수에서는 필수적입니다. 예를 들어, 윈도우 프레임 함수와 순위 관련 함수는 데이터가 의미 있는 순서로 되어 있어야 하므로, ORDER BY 하위 절이 필요합니다.

참고

윈도우 프레임을 지원하는 함수는 수정/향상된 구문을 활용합니다. 자세한 내용은 이 항목의 윈도우 프레임 구문과 사용법 섹션을 참조하십시오.

윈도우 사용법 노트¶

OVER 절은 함수가 작동하는 윈도우를 지정합니다. 이 절은 다음 구성 요소 중 하나(또는 둘 다)로 구성됩니다.
- PARTITION BY expr1: 윈도우에 대한 파티션이 있는 경우 이를 정의하는 하위 절입니다(즉, 데이터를 그룹화한 후 함수를 적용하는 방법).
- ORDER BY expr2: 윈도우에서 행의 순서를 결정하는 하위 절입니다.
  
  ORDER BY 하위 절은 예컨대 ASC/DESC(오름차순/내림차순) 및 NULL 처리와 관련하여 쿼리 ORDER BY 절의 규칙과 비슷한 규칙을 따릅니다. 추가로 지원되는 옵션에 대한 자세한 내용은 ORDER BY 쿼리 구문을 참조하십시오.
  
  다른 함수는 ORDER BY 절을 다른 방식으로 처리합니다.
  일부 윈도우 함수에는 ORDER BY 절이 필요합니다.
  
  일부 윈도우 함수에서는 ORDER BY 절을 사용할 수 없습니다.
  
  일부 윈도우 함수에서는 ORDER BY 절이 있으면 사용하지만 필수가 아닙니다.
  
  일부 윈도우 함수에서는 ORDER BY 절을 암시적 누적 윈도우 프레임 절로 취급합니다. (암시적 윈도우 프레임에 대한 자세한 내용은 윈도우 프레임 사용법 노트 에서 확인할 수 있습니다.)
  
  참고
  
  명확성을 위해, Snowflake는 암시적 윈도우 프레임을 피할 것을 권장합니다. 쿼리가 윈도우 프레임을 사용하는 경우 명시적 윈도우 프레임으로 만드십시오.

윈도우 프레임 구문과 사용법¶

윈도우 프레임 구문¶

<function> ( <arguments> ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ cumulativeFrame | slidingFrame ] )

Copy

여기서:

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }

Copy

slidingFrame ::=
    {
       ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
    }

Copy

참고

윈도우 프레임에서는 윈도우의 데이터가 알려진 순서대로 되어 있어야 합니다. 따라서 일반적인 윈도우 함수 구문에서는 ORDER BY 절이 선택 사항이지만, 윈도우 프레임 구문에는 OVER 절 내에 ORDER BY가 필수적 입니다.

윈도우 프레임 사용법 노트¶

누적 윈도우 프레임:
- ROWS 는 파티션의 시작 또는 끝부터 현재 행까지의 모든 행을 사용하여 현재 행에 대한 결과를 계산합니다(지정된 ORDER BY 하위 절에 따라).
- RANGE 는 (지정된 ORDER BY 하위 절에 따라) 현재 행과 같은 값을 갖는 행에 대한 결과만 계산한다는 점을 제외하면, ROWS 와 비슷합니다.
슬라이딩 윈도우 프레임:
- ROWS 가 포함되고 항상 현재 행을 기준으로 합니다.
- RANGE 가 지원되지 않습니다.
- 지정된 수의 이전 또는 다음 ROWS 가 윈도우 제한을 초과하는 경우 Snowflake는 해당 값을 NULL로 처리합니다.
지정된 윈도우 프레임이 없는 경우 기본값은 함수에 따라 다릅니다.
- 순위 관련 함수가 아닌 함수(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 표준에서 벗어납니다.

일반적인 팁¶

구문은 OVER 절의 모든 하위 절을 윈도우 함수에 대한 선택 사항으로 표시합니다. 이는 의도적인 것입니다(즉 , 괄호 안에 하위 절 없이 OVER 를 사용할 수 있음). 이것은 특정 시나리오(예: 병렬 처리 해제)에서 유용할 수 있습니다.
PARTITION BY 가 GROUP BY 와 항상 호환되는 것은 아닙니다.
쿼리가 둘 이상의 윈도우 함수를 사용하는 경우, 일반적으로 각 함수의 입력 데이터 세트를 같은 방식으로 분할해야 합니다. 예를 들어, 아래의 첫 번째 문은 아래의 두 번째 문보다 정확할 가능성이 더 큽니다.
```
SELECT SUM(a) OVER (PARTITION BY x), SUM(b) OVER (PARTITION BY x) ... ;

SELECT SUM(a)                      , SUM(b) OVER (PARTITION BY x) ... ;
```
Copy
오류 메시지 SQL compilation error: ... is not a valid group by expression 은 SELECT 문의 “project” 절에 있는 다른 열이 똑같은 방식으로 분할되지 않아 다른 개수의 행을 생성할 수 있다는 신호일 때가 많습니다.

예¶

추가 예는 윈도우 함수 사용하기 에서 찾을 수 있습니다.

누적 윈도우 프레임 예¶

값이 있는 테이블을 만들고 채웁니다.

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);

Copy

누적 윈도우 프레임을 사용하는 쿼리를 실행하고 출력을 표시합니다. 테이블에 대해 지정된 윈도우의 행에 대한 누적 개수, 합계, 최소값, 최대값을 반환합니다.

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 |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+

Copy

테이블에 대해 지정된 윈도우의 행에 대한 범위별 누적 개수, 합계, 최소값, 최대값을 반환합니다.

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 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 |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

Copy

기본 윈도우 프레임 의미 체계(즉, RANGE 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 |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

Copy

슬라이딩 윈도우 프레임 예¶

값이 있는 테이블을 만들고 채웁니다.

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');

Copy

현재 행의 앞과 뒤 그리고 이를 포괄하는 슬라이딩 윈도우 전체에서 두 열(숫자 및 문자열)에 대한 최소값을 반환합니다.

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      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

Copy

현재 행의 앞과 뒤 그리고 이를 포괄하는 슬라이딩 윈도우 전체에서 두 열(숫자 및 문자열)에 대한 최대값을 반환합니다.

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      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

Copy

현재 행의 앞과 뒤 그리고 이를 포괄하는 슬라이딩 윈도우 전체에서 숫자 열의 합계를 반환합니다.

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 |
+-----+----+-------+-------------+-------------+-------------+

Copy

순위 관련 함수 예¶

다음 예에서는 각 영업사원이 판매한 총 금액(달러)을 기준으로 영업사원의 순위를 매기는 방법을 보여줍니다. 이 쿼리는 OVER 절을 사용하여 각 영업사원의 총 매출을 포함하는 윈도우를 만듭니다. 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);

Copy

이제 데이터를 쿼리합니다.

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 |
+------------------+------------------+------------+

Copy

출력이 반드시 순위를 기준으로 정렬되어 표시되는 것은 아닙니다. 아래에 표시된 것처럼, ORDER BY 절(OVER 절 외부)을 사용하여 순위를 기준으로 출력을 순서대로 강제로 표시할 수 있습니다.

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 |
+------------------+------------------+------------+

Copy

앞의 예에는 두 개의 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 |
+------------------+------------------+------------+

Copy

윈도우 함수¶

개요¶

윈도우란 무엇입니까?¶

윈도우 함수란 무엇입니까?¶

윈도우를 지원하는 함수 목록¶

윈도우 함수 및 NULL 값¶

소개 예¶

윈도우 프레임이란 무엇입니까?¶

윈도우 프레임의 유형¶

순위 관련 함수란 무엇입니까?¶

윈도우 구문과 사용법¶

윈도우 구문¶

윈도우 사용법 노트¶

윈도우 프레임 구문과 사용법¶

윈도우 프레임 구문¶

윈도우 프레임 사용법 노트¶

순위 관련 윈도우 함수 구문과 사용법¶

순위 관련 윈도우 함수 구문¶

순위 관련 윈도우 함수 사용법 노트¶

일반적인 팁¶

예¶

누적 윈도우 프레임 예¶

슬라이딩 윈도우 프레임 예¶

순위 관련 함수 예¶