14.20.3 Window Function Frame Specification

14.20.3 Window Function Frame Specification

window function과 함께 사용되는 window의 정의에는 frame 절이 포함될 수 있습니다. frame은 현재 파티션의 부분집합이며, frame 절은 이 부분집합을 어떻게 정의할지 지정합니다.

frame은 현재 행을 기준으로 결정되며, 이를 통해 frame은 현재 행이 파티션 내에서 위치하는 곳에 따라 파티션 내에서 이동할 수 있습니다. 예:

• frame을 파티션 시작부터 현재 행까지의 모든 행으로 정의하면 각 행에 대해 누적 합계를 계산할 수 있습니다.

• frame을 현재 행의 양쪽으로 N 개의 행까지 확장되도록 정의하면 이동 평균(rolling average)을 계산할 수 있습니다.

다음 쿼리는 시간 순서로 정렬된 level 값의 각 그룹 내에서 누적 합계를 계산하기 위해 이동하는 frame을 사용하는 예와, 현재 행 및 그 직전과 직후 행으로부터 계산된 이동 평균을 보여 줍니다:

1mysql> SELECT
2         time, subject, val,
3         SUM(val) OVER (PARTITION BY subject ORDER BY time
4                        ROWS UNBOUNDED PRECEDING)
5           AS running_total,
6         AVG(val) OVER (PARTITION BY subject ORDER BY time
7                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
8           AS running_average
9       FROM observations;
10+----------+---------+------+---------------+-----------------+
11| time     | subject | val  | running_total | running_average |
12+----------+---------+------+---------------+-----------------+
13| 07:00:00 | st113   |   10 |            10 |          9.5000 |
14| 07:15:00 | st113   |    9 |            19 |         14.6667 |
15| 07:30:00 | st113   |   25 |            44 |         18.0000 |
16| 07:45:00 | st113   |   20 |            64 |         22.5000 |
17| 07:00:00 | xh458   |    0 |             0 |          5.0000 |
18| 07:15:00 | xh458   |   10 |            10 |          5.0000 |
19| 07:30:00 | xh458   |    5 |            15 |         15.0000 |
20| 07:45:00 | xh458   |   30 |            45 |         20.0000 |
21| 08:00:00 | xh458   |   25 |            70 |         27.5000 |
22+----------+---------+------+---------------+-----------------+

running_average 열의 경우, 첫 번째 행 앞이나 마지막 행 뒤에는 frame 행이 없습니다. 이러한 경우 AVG()는 사용 가능한 행의 평균을 계산합니다.

window function으로 사용되는 집계 함수는 현재 행 frame의 행에 대해 동작하며, 다음과 같은 비집계 window function도 마찬가지입니다:

1FIRST_VALUE()
2LAST_VALUE()
3NTH_VALUE()

표준 SQL에서는 전체 파티션에 대해 동작하는 window function은 frame 절을 가지면 안 된다고 지정합니다. MySQL은 이러한 함수에 대해 frame 절을 허용하지만 이를 무시합니다. 이들 함수는 frame이 지정되더라도 전체 파티션을 사용합니다:

1CUME_DIST()
2DENSE_RANK()
3LAG()
4LEAD()
5NTILE()
6PERCENT_RANK()
7RANK()
8ROW_NUMBER()

frame 절이 주어지는 경우, 그 문법은 다음과 같습니다:

1frame_clause:
2    frame_units frame_extent
3
4frame_units:
5    {ROWS | RANGE}

frame 절이 없을 때의 기본 frame은 ORDER BY 절이 존재하는지 여부에 따라 달라지며, 이에 대해서는 이 절의 뒷부분에서 설명합니다.

frame_units 값은 현재 행과 frame 행 사이의 관계 유형을 나타냅니다:

• ROWS: frame은 시작 및 종료 행 위치로 정의됩니다. 오프셋은 현재 행 번호로부터의 행 번호 차이입니다.

• RANGE: frame은 값 범위 내의 행으로 정의됩니다. 오프셋은 현재 행 값으로부터의 행 값 차이입니다.

frame_extent 값은 frame의 시작점과 종료점을 나타냅니다. frame의 시작만 지정할 수도 있고(이 경우 현재 행이 암묵적으로 종료점이 됨), BETWEEN을 사용해 frame의 양쪽 끝을 모두 지정할 수도 있습니다:

1frame_extent:
2    {frame_start | frame_between}
3
4frame_between:
5    BETWEEN frame_start AND frame_end
6
7frame_start, frame_end: {
8    CURRENT ROW
9  | UNBOUNDED PRECEDING
10  | UNBOUNDED FOLLOWING
11  | expr PRECEDING
12  | expr FOLLOWING
13}

BETWEEN 문법을 사용할 때, _frame_start_는 _frame_end_보다 뒤에 올 수 없습니다.

허용되는 frame_start 및 frame_end 값의 의미는 다음과 같습니다:

• CURRENT ROW: ROWS의 경우, 경계는 현재 행입니다. RANGE의 경우, 경계는 현재 행의 피어입니다.

• UNBOUNDED PRECEDING: 경계는 파티션의 첫 번째 행입니다.

• UNBOUNDED FOLLOWING: 경계는 파티션의 마지막 행입니다.

• expr PRECEDING: ROWS의 경우, 경계는 현재 행보다 expr 개 앞의 행입니다. RANGE의 경우, 경계는 현재 행 값에서 _expr_를 뺀 값과 같은 값을 가진 행입니다. 현재 행 값이 NULL이면, 경계는 해당 행의 피어입니다.

expr PRECEDING(및 expr FOLLOWING)에 대해, _expr_는 준비된 구문에서 사용할 ? 매개변수 마커, 음수가 아닌 숫자 리터럴, 또는 INTERVAL val unit 형식의 시간 간격이 될 수 있습니다. INTERVAL 표현식에 대해 _val_은 음수가 아닌 간격 값이며, _unit_은 값이 해석되어야 할 단위를 나타내는 키워드입니다. (허용되는 units 지정자에 대한 자세한 내용은 Section 14.7, “Date and Time Functions”의 DATE_ADD() 함수 설명을 참조하십시오.)

숫자 또는 시간 _expr_에 대한 RANGE는 각각 숫자 또는 시간 표현식에 대한 ORDER BY를 필요로 합니다.

유효한 expr PRECEDING 및 expr FOLLOWING indicator 예:

110 PRECEDING
2INTERVAL 5 DAY PRECEDING
35 FOLLOWING
4INTERVAL '2:30' MINUTE_SECOND FOLLOWING

• expr FOLLOWING: ROWS의 경우, 경계는 현재 행보다 expr 개 뒤의 행입니다. RANGE의 경우, 경계는 현재 행 값에 _expr_를 더한 값과 같은 값을 가진 행입니다. 현재 행 값이 NULL이면, 경계는 해당 행의 피어입니다.

_expr_에 대한 허용 값은 expr PRECEDING 설명을 참조하십시오.

다음 쿼리는 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+----------+---------+------+-------+------+--------+--------+

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

frame 절이 없을 때의 기본 frame은 ORDER BY 절이 존재하는지 여부에 따라 달라집니다:

• ORDER BY가 있는 경우: 기본 frame은 파티션 시작부터 현재 행까지의 행(및 ORDER BY 절에 따라 현재 행과 동일한 행인 모든 피어)을 포함합니다. 이 기본값은 다음 frame specification과 동일합니다:

1RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• ORDER BY가 없는 경우: 기본 frame은 모든 파티션 행을 포함합니다(왜냐하면 ORDER BY가 없으면 모든 파티션 행이 피어이기 때문입니다). 이 기본값은 다음 frame specification과 동일합니다:

1RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

ORDER BY 존재 여부에 따라 기본 frame이 달라지므로, 결정적인 결과를 얻기 위해 쿼리에 ORDER BY를 추가하면 결과가 변경될 수 있습니다(예: SUM()이 생성하는 값이 변경될 수 있음). ORDER BY에 따라 정렬하되 동일한 결과를 얻으려면, ORDER BY 존재 여부와 상관없이 사용될 명시적인 frame specification을 제공해야 합니다.

frame specification의 의미는 현재 행 값이 NULL일 때 직관적이지 않을 수 있습니다. 현재 행 값이 그러하다고 가정하면, 다음 예는 다양한 frame specification이 어떻게 적용되는지 보여 줍니다:

• ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWING

  frame은 NULL에서 시작해 NULL에서 멈추므로, 값이 NULL인 행만 포함합니다.

• ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING

  frame은 NULL에서 시작해 파티션의 끝에서 멈춥니다. ASC 정렬은 NULL 값을 먼저 배치하므로, frame은 전체 파티션입니다.

• ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING

  frame은 NULL에서 시작해 파티션의 끝에서 멈춥니다. DESC 정렬은 NULL 값을 마지막에 배치하므로, frame은 NULL 값만 포함합니다.

• ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING

  frame은 NULL에서 시작해 파티션의 끝에서 멈춥니다. ASC 정렬은 NULL 값을 먼저 배치하므로, frame은 전체 파티션입니다.

• ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING

  frame은 NULL에서 시작해 NULL에서 멈추므로, 값이 NULL인 행만 포함합니다.

• ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1 PRECEDING

  frame은 NULL에서 시작해 NULL에서 멈추므로, 값이 NULL인 행만 포함합니다.

• ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING

  frame은 파티션 시작에서 시작해 값이 NULL인 행에서 멈춥니다. ASC 정렬은 NULL 값을 먼저 배치하므로, frame은 NULL 값만 포함합니다.