14.20.1 Window Function Descriptions

14.20.1 Window Function Descriptions

이 섹션에서는 쿼리에서 가져온 각 행에 대해, 해당 행과 관련된 행들을 사용하여 계산을 수행하는 비집계 윈도 함수(nonaggregate window function)를 설명합니다. 대부분의 집계 함수도 윈도 함수로 사용할 수 있습니다. 이에 대해서는 Section 14.19.1, “Aggregate Function Descriptions”를 참조하십시오.

윈도 함수의 사용 정보와 예제, 그리고 OVER 절, 윈도, 파티션, 프레임, 피어 같은 용어의 정의는 Section 14.20.2, “Window Function Concepts and Syntax”를 참조하십시오.

Table 14.30 Window Functions

Name	Description
CUME_DIST()	Cumulative distribution value
DENSE_RANK()	Rank of current row within its partition, without gaps
FIRST_VALUE()	Value of argument from first row of window frame
LAG()	Value of argument from row lagging current row within partition
LAST_VALUE()	Value of argument from last row of window frame
LEAD()	Value of argument from row leading current row within partition
NTH_VALUE()	Value of argument from N-th row of window frame
NTILE()	Bucket number of current row within its partition.
PERCENT_RANK()	Percentage rank value
RANK()	Rank of current row within its partition, with gaps
ROW_NUMBER()	Number of current row within its partition

Name	Description

다음 함수 설명에서, _over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명된 OVER 절을 나타냅니다. 일부 윈도 함수는 결과 계산 시 NULL 값을 어떻게 처리할지를 지정하는 null_treatment 절을 허용합니다. 이 절은 선택 사항입니다. 이는 SQL 표준의 일부이지만, MySQL 구현에서는 RESPECT NULLS만 허용합니다(기본값도 동일). 이는 결과를 계산할 때 NULL 값이 고려된다는 의미입니다. IGNORE NULLS는 파싱은 되지만 에러를 발생시킵니다.

• CUME_DIST() over_clause

값의 그룹 내에서 어떤 값의 누적 분포(cumulative distribution)를 반환합니다. 즉, 현재 행의 값보다 작거나 같은 파티션 값의 비율입니다. 이는 윈도 파티션의 윈도 정렬에서 현재 행에 선행하거나 피어인 행 수를 윈도 파티션의 전체 행 수로 나눈 값입니다. 반환 값의 범위는 0에서 1까지입니다.

이 함수는 파티션 행을 원하는 순서로 정렬하기 위해 ORDER BY와 함께 사용해야 합니다. ORDER BY가 없으면 모든 행은 피어이며 값은 N / N = 1이 됩니다. 여기서 _N_은 파티션 크기입니다.

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다.

다음 쿼리는 val 컬럼에 있는 값 집합에 대해 각 행에 대한 CUME_DIST() 값과, 유사한 함수인 PERCENT_RANK()가 반환하는 percentage rank 값을 보여 줍니다. 참고를 위해, 쿼리는 ROW_NUMBER()를 사용하여 행 번호도 표시합니다:

1mysql> SELECT
2           val,
3           ROW_NUMBER()   OVER w AS 'row_number',
4           CUME_DIST()    OVER w AS 'cume_dist',
5           PERCENT_RANK() OVER w AS 'percent_rank'
6         FROM numbers
7         WINDOW w AS (ORDER BY val);
8+------+------------+--------------------+--------------+
9| val  | row_number | cume_dist          | percent_rank |
10+------+------------+--------------------+--------------+
11|    1 |          1 | 0.2222222222222222 |            0 |
12|    1 |          2 | 0.2222222222222222 |            0 |
13|    2 |          3 | 0.3333333333333333 |         0.25 |
14|    3 |          4 | 0.6666666666666666 |        0.375 |
15|    3 |          5 | 0.6666666666666666 |        0.375 |
16|    3 |          6 | 0.6666666666666666 |        0.375 |
17|    4 |          7 | 0.8888888888888888 |         0.75 |
18|    4 |          8 | 0.8888888888888888 |         0.75 |
19|    5 |          9 |                  1 |            1 |
20+------+------------+--------------------+--------------+

• DENSE_RANK() over_clause

현재 행의 파티션 내 랭크를 갭 없이 반환합니다. 피어는 동률로 간주되어 동일한 랭크를 받습니다. 이 함수는 피어 그룹에 연속된 랭크를 할당합니다. 그 결과, 크기가 1보다 큰 그룹이 있더라도 불연속적인 랭크 번호가 생성되지 않습니다. 예제는 RANK() 함수 설명을 참조하십시오.

이 함수는 파티션 행을 원하는 순서로 정렬하기 위해 ORDER BY와 함께 사용해야 합니다. ORDER BY가 없으면 모든 행은 피어입니다.

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다.

• FIRST_VALUE(expr) [ null_treatment ] over_clause

윈도 프레임의 첫 번째 행에서 _expr_의 값을 반환합니다.

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다. _null_treatment_는 이 섹션의 도입부에 설명된 것과 같습니다.

다음 쿼리는 FIRST_VALUE(), LAST_VALUE(), 그리고 두 개의 NTH_VALUE() 호출을 보여 줍니다:

1mysql> SELECT
2           time, subject, val,
3           FIRST_VALUE(val)  OVER w AS 'first',
4           LAST_VALUE(val)   OVER w AS 'last',
5           NTH_VALUE(val, 2) OVER w AS 'second',
6           NTH_VALUE(val, 4) OVER w AS 'fourth'
7         FROM observations
8         WINDOW w AS (PARTITION BY subject ORDER BY time
9                      ROWS UNBOUNDED PRECEDING);
10+----------+---------+------+-------+------+--------+--------+
11| time     | subject | val  | first | last | second | fourth |
12+----------+---------+------+-------+------+--------+--------+
13| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
14| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
15| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
16| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
17| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
18| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
19| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
20| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
21| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
22+----------+---------+------+-------+------+--------+--------+

각 함수는 현재 프레임의 행을 사용합니다. 윈도 정의에 따르면, 프레임은 첫 번째 파티션 행에서 현재 행까지 확장됩니다. NTH_VALUE() 호출의 경우, 현재 프레임이 요청된 행을 항상 포함하는 것은 아니며, 이런 경우 반환 값은 NULL입니다.

• LAG(expr [, N[, default]]) [ null_treatment ] over_clause

파티션 내에서 현재 행보다 N 행만큼 뒤에 있는(행이 lagging 또는 preceding) 행에서 _expr_의 값을 반환합니다. 해당 행이 없으면 반환 값은 _default_입니다. 예를 들어 _N_이 3이면, 처음 세 개의 행에 대해 반환 값은 _default_입니다. N 또는 _default_가 생략되면 기본값은 각각 1과 NULL입니다.

_N_은 리터럴 음이 아닌 정수여야 합니다. _N_이 0이면, _expr_는 현재 행에 대해 평가됩니다.

_N_은 NULL이 될 수 없으며, 다음과 같은 형태 중 하나로 0에서 263까지(양 끝 포함)의 정수여야 합니다:

• 부호 없는 정수 상수 리터럴
• 위치 기반 파라미터 마커(?)
• 사용자 정의 변수
• 저장 루틴의 로컬 변수

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다. _null_treatment_는 이 섹션의 도입부에 설명된 것과 같습니다.

LAG()(및 유사한 LEAD() 함수)은 종종 행 간의 차이를 계산하는 데 사용됩니다. 다음 쿼리는 시간 순으로 정렬된 관측값 집합과 각 관측값에 대해 인접 행으로부터의 LAG() 및 LEAD() 값을, 그리고 현재 행과 인접 행 간의 차이를 보여 줍니다:

1mysql> SELECT
2         t, val,
3         LAG(val)        OVER w AS 'lag',
4         LEAD(val)       OVER w AS 'lead',
5         val - LAG(val)  OVER w AS 'lag diff',
6         val - LEAD(val) OVER w AS 'lead diff'
7       FROM series
8       WINDOW w AS (ORDER BY t);
9+----------+------+------+------+----------+-----------+
10| t        | val  | lag  | lead | lag diff | lead diff |
11+----------+------+------+------+----------+-----------+
12| 12:00:00 |  100 | NULL |  125 |     NULL |       -25 |
13| 13:00:00 |  125 |  100 |  132 |       25 |        -7 |
14| 14:00:00 |  132 |  125 |  145 |        7 |       -13 |
15| 15:00:00 |  145 |  132 |  140 |       13 |         5 |
16| 16:00:00 |  140 |  145 |  150 |       -5 |       -10 |
17| 17:00:00 |  150 |  140 |  200 |       10 |       -50 |
18| 18:00:00 |  200 |  150 | NULL |       50 |      NULL |
19+----------+------+------+------+----------+-----------+

이 예제에서, LAG() 및 LEAD() 호출은 각각 1과 NULL인 기본 N 및 default 값을 사용합니다.

첫 번째 행은 LAG()에 대해 이전 행이 없을 때 어떤 일이 일어나는지를 보여 줍니다. 이 함수는 이 경우 default 값(여기서는 NULL)을 반환합니다. 마지막 행은 LEAD()에 대해 다음 행이 없을 때 같은 현상이 일어나는 것을 보여 줍니다.

LAG()과 LEAD()는 차이뿐 아니라 합계를 계산하는 데에도 사용됩니다. 다음 데이터 집합은 Fibonacci 수열의 처음 몇 개의 숫자를 포함합니다:

1mysql> SELECT n FROM fib ORDER BY n;
2+------+
3| n    |
4+------+
5|    1 |
6|    1 |
7|    2 |
8|    3 |
9|    5 |
10|    8 |
11+------+

다음 쿼리는 현재 행에 인접한 행에 대해 LAG() 및 LEAD() 값을 보여 줍니다. 또한 이들 함수를 사용하여 현재 행 값에 이전 및 다음 행의 값을 더합니다. 그 효과는 Fibonacci 수열의 다음 숫자, 그리고 그 다음 숫자를 생성하는 것입니다:

1mysql> SELECT
2         n,
3         LAG(n, 1, 0)      OVER w AS 'lag',
4         LEAD(n, 1, 0)     OVER w AS 'lead',
5         n + LAG(n, 1, 0)  OVER w AS 'next_n',
6         n + LEAD(n, 1, 0) OVER w AS 'next_next_n'
7       FROM fib
8       WINDOW w AS (ORDER BY n);
9+------+------+------+--------+-------------+
10| n    | lag  | lead | next_n | next_next_n |
11+------+------+------+--------+-------------+
12|    1 |    0 |    1 |      1 |           2 |
13|    1 |    1 |    2 |      2 |           3 |
14|    2 |    1 |    3 |      3 |           5 |
15|    3 |    2 |    5 |      5 |           8 |
16|    5 |    3 |    8 |      8 |          13 |
17|    8 |    5 |    0 |     13 |           8 |
18+------+------+------+--------+-------------+

초기 Fibonacci 숫자 집합을 생성하는 한 가지 방법은 재귀 common table expression을 사용하는 것입니다. 예제는 Fibonacci Series Generation을 참조하십시오.

이 함수의 rows 인자에 음수 값을 사용할 수 없습니다.

• LAST_VALUE(expr) [ null_treatment ] over_clause

윈도 프레임의 마지막 행에서 _expr_의 값을 반환합니다.

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다. _null_treatment_는 이 섹션의 도입부에 설명된 것과 같습니다.

예제는 FIRST_VALUE() 함수 설명을 참조하십시오.

• LEAD(expr [, N[, default]]) [ null_treatment ] over_clause

파티션 내에서 현재 행보다 N 행만큼 앞에 있는(행이 leading 또는 following) 행에서 _expr_의 값을 반환합니다. 해당 행이 없으면 반환 값은 _default_입니다. 예를 들어 _N_이 3이면, 마지막 세 개의 행에 대해 반환 값은 _default_입니다. N 또는 _default_가 생략되면 기본값은 각각 1과 NULL입니다.

_N_은 리터럴 음이 아닌 정수여야 합니다. _N_이 0이면, _expr_는 현재 행에 대해 평가됩니다.

_N_은 NULL이 될 수 없으며, 다음과 같은 형태 중 하나로 0에서 263까지(양 끝 포함)의 정수여야 합니다:

• 부호 없는 정수 상수 리터럴
• 위치 기반 파라미터 마커(?)
• 사용자 정의 변수
• 저장 루틴의 로컬 변수

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다. _null_treatment_는 이 섹션의 도입부에 설명된 것과 같습니다.

예제는 LAG() 함수 설명을 참조하십시오.

이 함수의 rows 인자에 음수 값을 사용하는 것은 허용되지 않습니다.

• NTH_VALUE(expr, N) [ from_first_last ] [ null_treatment ] over_clause

윈도 프레임의 _N_번째 행에서 _expr_의 값을 반환합니다. 해당 행이 없으면 반환 값은 NULL입니다.

_N_은 리터럴 양의 정수여야 합니다.

_from_first_last_는 SQL 표준의 일부지만, MySQL 구현에서는 FROM FIRST만 허용합니다(기본값도 동일). 이는 계산이 윈도의 첫 번째 행에서 시작된다는 의미입니다. FROM LAST는 파싱은 되지만 에러를 발생시킵니다. FROM LAST와 동일한 효과(계산을 윈도의 마지막 행에서 시작)를 얻으려면 ORDER BY를 사용하여 역순으로 정렬하십시오.

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다. _null_treatment_는 이 섹션의 도입부에 설명된 것과 같습니다.

예제는 FIRST_VALUE() 함수 설명을 참조하십시오.

이 함수의 row 인자에 NULL을 사용할 수 없습니다.

• NTILE(N) over_clause

파티션을 _N_개의 그룹(bucket)으로 나누고, 파티션 내 각 행에 버킷 번호를 할당한 다음, 현재 행의 버킷 번호를 반환합니다. 예를 들어 _N_이 4이면 NTILE()은 행을 네 개의 버킷으로 나눕니다. _N_이 100이면 NTILE()은 행을 100개의 버킷으로 나눕니다.

_N_은 리터럴 양의 정수여야 합니다. 버킷 번호 반환 값의 범위는 1에서 _N_까지입니다.

_N_은 NULL이 될 수 없으며, 다음과 같은 형태 중 하나로 0에서 263까지(양 끝 포함)의 정수여야 합니다:

• 부호 없는 정수 상수 리터럴
• 위치 기반 파라미터 마커(?)
• 사용자 정의 변수
• 저장 루틴의 로컬 변수

이 함수는 파티션 행을 원하는 순서로 정렬하기 위해 ORDER BY와 함께 사용해야 합니다.

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다.

다음 쿼리는 val 컬럼의 값 집합에 대해, 행을 두 개 또는 네 개의 그룹으로 나눈 결과로서 퍼센타일 값을 보여 줍니다. 참고를 위해, 쿼리는 ROW_NUMBER()를 사용하여 행 번호도 표시합니다:

1mysql> SELECT
2         val,
3         ROW_NUMBER() OVER w AS 'row_number',
4         NTILE(2)     OVER w AS 'ntile2',
5         NTILE(4)     OVER w AS 'ntile4'
6       FROM numbers
7       WINDOW w AS (ORDER BY val);
8+------+------------+--------+--------+
9| val  | row_number | ntile2 | ntile4 |
10+------+------------+--------+--------+
11|    1 |          1 |      1 |      1 |
12|    1 |          2 |      1 |      1 |
13|    2 |          3 |      1 |      1 |
14|    3 |          4 |      1 |      2 |
15|    3 |          5 |      1 |      2 |
16|    3 |          6 |      2 |      3 |
17|    4 |          7 |      2 |      3 |
18|    4 |          8 |      2 |      4 |
19|    5 |          9 |      2 |      4 |
20+------+------------+--------+--------+

NTILE(NULL) 구문은 허용되지 않습니다.

• PERCENT_RANK() over_clause

현재 행의 값보다 작은 파티션 값(최대값은 제외)의 percentage를 반환합니다. 반환 값의 범위는 0에서 1까지이며, 다음 공식의 결과로 계산되는 행 상대 랭크를 나타냅니다. 여기서 _rank_는 행 랭크이고 _rows_는 파티션 행의 개수입니다:

1(rank - 1) / (rows - 1)

이 함수는 파티션 행을 원하는 순서로 정렬하기 위해 ORDER BY와 함께 사용해야 합니다. ORDER BY가 없으면 모든 행은 피어입니다.

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다.

예제는 CUME_DIST() 함수 설명을 참조하십시오.

• RANK() over_clause

현재 행의 파티션 내 랭크를 갭과 함께 반환합니다. 피어는 동률로 간주되어 동일한 랭크를 받습니다. 이 함수는 크기가 1보다 큰 그룹이 존재할 경우 피어 그룹에 연속된 랭크를 할당하지 않으므로, 결과는 불연속적인 랭크 번호가 됩니다.

이 함수는 파티션 행을 원하는 순서로 정렬하기 위해 ORDER BY와 함께 사용해야 합니다. ORDER BY가 없으면 모든 행은 피어입니다.

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다.

다음 쿼리는 갭이 있는 랭크를 생성하는 RANK()와 갭 없이 랭크를 생성하는 DENSE_RANK()의 차이를 보여 줍니다. 쿼리는 일부 중복을 포함하는 val 컬럼의 값 집합에 대해 각 값의 랭크를 보여 줍니다. RANK()는 피어(중복)에 동일한 랭크 값을 할당하고, 다음으로 큰 값에는 피어 수에서 1을 뺀 값만큼 더 높은 랭크를 부여합니다. DENSE_RANK()도 피어에 동일한 랭크 값을 할당하지만, 그다음 더 큰 값에는 1만 더 큰 랭크를 부여합니다. 참고를 위해, 쿼리는 ROW_NUMBER()를 사용하여 행 번호도 표시합니다:

1mysql> SELECT
2           val,
3           ROW_NUMBER() OVER w AS 'row_number',
4           RANK()       OVER w AS 'rank',
5           DENSE_RANK() OVER w AS 'dense_rank'
6         FROM numbers
7         WINDOW w AS (ORDER BY val);
8+------+------------+------+------------+
9| val  | row_number | rank | dense_rank |
10+------+------------+------+------------+
11|    1 |          1 |    1 |          1 |
12|    1 |          2 |    1 |          1 |
13|    2 |          3 |    3 |          2 |
14|    3 |          4 |    4 |          3 |
15|    3 |          5 |    4 |          3 |
16|    3 |          6 |    4 |          3 |
17|    4 |          7 |    7 |          4 |
18|    4 |          8 |    7 |          4 |
19|    5 |          9 |    9 |          5 |
20+------+------------+------+------------+

• ROW_NUMBER() over_clause

현재 행의 파티션 내 번호를 반환합니다. 행 번호 범위는 1에서 파티션 행 개수까지입니다.

ORDER BY는 행에 번호를 매기는 순서에 영향을 줍니다. ORDER BY가 없으면 행 번호 매김은 비결정적(nondeterministic)입니다.

ROW_NUMBER()는 피어에 서로 다른 행 번호를 할당합니다. 피어에 동일한 값을 할당하려면 RANK() 또는 DENSE_RANK()를 사용하십시오. 예제는 RANK() 함수 설명을 참조하십시오.

_over_clause_는 Section 14.20.2, “Window Function Concepts and Syntax”에 설명되어 있습니다.