데브러너 (DevRunner)

14.20.2 Window Function 개념 및 Syntax

이 섹션은 window function을 사용하는 방법을 설명합니다. 예제에서는 GROUPING() 함수에 대한 논의인
Section 14.19.2, “GROUP BY Modifiers”에 나오는 것과 동일한 sales 정보 데이터 세트를 사용합니다:

1mysql> SELECT * FROM sales ORDER BY country, year, product;
2+------+---------+------------+--------+
3| year | country | product    | profit |
4+------+---------+------------+--------+
5| 2000 | Finland | Computer   |   1500 |
6| 2000 | Finland | Phone      |    100 |
7| 2001 | Finland | Phone      |     10 |
8| 2000 | India   | Calculator |     75 |
9| 2000 | India   | Calculator |     75 |
10| 2000 | India   | Computer   |   1200 |
11| 2000 | USA     | Calculator |     75 |
12| 2000 | USA     | Computer   |   1500 |
13| 2001 | USA     | Calculator |     50 |
14| 2001 | USA     | Computer   |   1500 |
15| 2001 | USA     | Computer   |   1200 |
16| 2001 | USA     | TV         |    150 |
17| 2001 | USA     | TV         |    100 |
18+------+---------+------------+--------+

window function은 쿼리 row 집합에 대해 집계와 유사한 연산을 수행합니다. 그러나 집계 연산이 쿼리 row들을 하나의 결과 row로 그룹화하는 반면, window function은 각 쿼리 row마다 결과를 생성합니다:

함수가 평가되는 row를 current row라고 합니다.
current row에 대해 함수 평가가 수행되는 관련 쿼리 row들은 current row에 대한 window를 구성합니다.

예를 들어, sales 정보 테이블을 사용하여 다음 두 쿼리는 모든 row를 하나의 그룹으로 취급하여 전체 global 합계를 계산하는 집계 연산과, country별로 그룹화된 합계를 수행합니다:

1mysql> SELECT SUM(profit) AS total_profit
2       FROM sales;
3+--------------+
4| total_profit |
5+--------------+
6|         7535 |
7+--------------+
8mysql> SELECT country, SUM(profit) AS country_profit
9       FROM sales
10       GROUP BY country
11       ORDER BY country;
12+---------+----------------+
13| country | country_profit |
14+---------+----------------+
15| Finland |           1610 |
16| India   |           1350 |
17| USA     |           4575 |
18+---------+----------------+

반대로, window 연산은 쿼리 row 그룹을 단일 출력 row로 축소하지 않습니다. 대신 각 row마다 결과를 생성합니다. 이전 쿼리와 마찬가지로, 다음 쿼리도 SUM()을 사용하지만, 이번에는 window function으로 사용합니다:

1mysql> SELECT
2         year, country, product, profit,
3         SUM(profit) OVER() AS total_profit,
4         SUM(profit) OVER(PARTITION BY country) AS country_profit
5       FROM sales
6       ORDER BY country, year, product, profit;
7+------+---------+------------+--------+--------------+----------------+
8| year | country | product    | profit | total_profit | country_profit |
9+------+---------+------------+--------+--------------+----------------+
10| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
11| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
12| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
13| 2000 | India   | Calculator |     75 |         7535 |           1350 |
14| 2000 | India   | Calculator |     75 |         7535 |           1350 |
15| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
16| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
17| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
18| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
19| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
20| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
21| 2001 | USA     | TV         |    100 |         7535 |           4575 |
22| 2001 | USA     | TV         |    150 |         7535 |           4575 |
23+------+---------+------------+--------+--------------+----------------+

쿼리 내 각 window 연산은 window function이 쿼리 row들을 어떻게 파티셔닝하여 처리할지를 지정하는 OVER 절의 포함으로 표시됩니다:

첫 번째 OVER 절은 비어 있으며, 전체 쿼리 row 집합을 단일 파티션으로 취급합니다. 따라서 window function은 global 합계를 계산하지만, 각 row에 대해 이를 수행합니다.
두 번째 OVER 절은 row를 country별로 파티셔닝하여, 파티션(country)마다 합계를 생성합니다. 함수는 해당 파티션의 각 row에 대해 이 합계를 생성합니다.

window function은 select 리스트와 ORDER BY 절에서만 허용됩니다. 쿼리 결과 row는 FROM 절에서 결정되며, 이는 WHERE, GROUP BY, HAVING 처리가 끝난 후이고, windowing 실행은 ORDER BY, LIMIT, SELECT DISTINCT 이전에 수행됩니다.

OVER 절은 많은 집계 함수에 대해 허용되며, 따라서 OVER 절의 존재 여부에 따라 이것들을 window function 또는 비 window function으로 사용할 수 있습니다:

1AVG()
2BIT_AND()
3BIT_OR()
4BIT_XOR()
5COUNT()
6JSON_ARRAYAGG()
7JSON_OBJECTAGG()
8MAX()
9MIN()
10STDDEV_POP(), STDDEV(), STD()
11STDDEV_SAMP()
12SUM()
13VAR_POP(), VARIANCE()
14VAR_SAMP()

각 집계 함수에 대한 자세한 내용은
Section 14.19.1, “Aggregate Function Descriptions”를 참조하십시오.

MySQL은 window function으로만 사용되는 비집계 함수도 지원합니다. 이들에 대해서는 OVER 절이 필수입니다:

1CUME_DIST()
2DENSE_RANK()
3FIRST_VALUE()
4LAG()
5LAST_VALUE()
6LEAD()
7NTH_VALUE()
8NTILE()
9PERCENT_RANK()
10RANK()
11ROW_NUMBER()

각 비집계 함수에 대한 자세한 내용은
Section 14.20.1, “Window Function Descriptions”를 참조하십시오.

이러한 비집계 window function 중 하나의 예로, 다음 쿼리는 ROW_NUMBER()를 사용합니다. 이 함수는 자신의 파티션 내에서 각 row의 row 번호를 생성합니다. 이 경우 row는 country별로 번호가 매겨집니다. 기본적으로 파티션 row는 정렬되지 않으며, row numbering은 비결정적입니다. 파티션 row를 정렬하려면 window 정의 내에 ORDER BY 절을 포함하십시오. 이 쿼리는 정렬되지 않은 파티션과 정렬된 파티션(row_num1과 row_num2 열)을 사용하여 ORDER BY를 생략한 경우와 포함한 경우의 차이를 보여 줍니다:

1mysql> SELECT
2         year, country, product, profit,
3         ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
4         ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
5       FROM sales;
6+------+---------+------------+--------+----------+----------+
7| year | country | product    | profit | row_num1 | row_num2 |
8+------+---------+------------+--------+----------+----------+
9| 2000 | Finland | Computer   |   1500 |        2 |        1 |
10| 2000 | Finland | Phone      |    100 |        1 |        2 |
11| 2001 | Finland | Phone      |     10 |        3 |        3 |
12| 2000 | India   | Calculator |     75 |        2 |        1 |
13| 2000 | India   | Calculator |     75 |        3 |        2 |
14| 2000 | India   | Computer   |   1200 |        1 |        3 |
15| 2000 | USA     | Calculator |     75 |        5 |        1 |
16| 2000 | USA     | Computer   |   1500 |        4 |        2 |
17| 2001 | USA     | Calculator |     50 |        2 |        3 |
18| 2001 | USA     | Computer   |   1500 |        3 |        4 |
19| 2001 | USA     | Computer   |   1200 |        7 |        5 |
20| 2001 | USA     | TV         |    150 |        1 |        6 |
21| 2001 | USA     | TV         |    100 |        6 |        7 |
22+------+---------+------------+--------+----------+----------+

앞에서 언급했듯이, window function을 사용하려면(또는 집계 함수를 window function으로 취급하려면) 함수 호출 뒤에 OVER 절을 포함하십시오. OVER 절에는 두 가지 형식이 있습니다:

1over_clause:
2    {OVER (window_spec) | OVER window_name}

두 형식 모두 window function이 쿼리 row를 처리하는 방식을 정의합니다. 이들 차이는 window가 OVER 절 안에서 직접 정의되는지, 아니면 쿼리의 다른 곳에 정의된 named window에 대한 참조로 제공되는지에 있습니다:

첫 번째 경우에는 window 명세가 괄호 안의 OVER 절 내에 직접 나타납니다.
두 번째 경우, _window_name_은 쿼리의 다른 곳에서 WINDOW 절에 의해 정의된 window 명세의 이름입니다. 자세한 내용은
Section 14.20.4, “Named Windows”를 참조하십시오.

OVER (window_spec) 구문에서 window 명세에는 모두 선택적인 여러 부분이 있습니다:

1window_spec:
2    [window_name] [partition_clause] [order_clause] [frame_clause]

OVER()가 비어 있으면 window는 모든 쿼리 row로 구성되며 window function은 모든 row를 사용하여 결과를 계산합니다. 그렇지 않으면 괄호 안에 존재하는 절이 어떤 쿼리 row가 함수 결과를 계산하는 데 사용되는지, 그리고 그것들이 어떻게 파티셔닝 및 정렬되는지를 결정합니다:

window_name: 쿼리의 다른 곳에서 WINDOW 절로 정의된 window의 이름입니다. _window_name_이 OVER 절 내에서 단독으로 나타나면 해당 window를 완전히 정의합니다. 파티셔닝, 정렬 또는 프레이밍 절도 주어진 경우, 이는 named window의 해석을 수정합니다. 자세한 내용은
Section 14.20.4, “Named Windows”를 참조하십시오.
partition_clause: PARTITION BY 절은 쿼리 row를 그룹으로 나누는 방법을 지정합니다. 특정 row에 대한 window function 결과는 그 row를 포함하는 파티션의 row를 기반으로 합니다. PARTITION BY를 생략하면, 모든 쿼리 row로 구성된 단일 파티션이 존재합니다.

Note

window function을 위한 파티셔닝은 테이블 파티셔닝과 다릅니다. 테이블 파티셔닝에 대한 정보는
Chapter 26, Partitioning을 참조하십시오.

_partition_clause_의 구문은 다음과 같습니다:
```
1partition_clause:
2      PARTITION BY expr [, expr] ...
```
표준 SQL에서는 PARTITION BY 뒤에 열 이름만을 허용합니다. MySQL 확장은 열 이름뿐 아니라 표현식도 허용한다는 점입니다. 예를 들어, 테이블에 ts라는 이름의 TIMESTAMP 열이 있는 경우, 표준 SQL은 PARTITION BY ts는 허용하지만 PARTITION BY HOUR(ts)는 허용하지 않습니다. 반면 MySQL은 둘 다 허용합니다.
order_clause: ORDER BY 절은 각 파티션 내의 row를 어떻게 정렬할지를 지정합니다. ORDER BY 절에 따라 동일하다고 간주되는 파티션 row는 peer로 취급됩니다. ORDER BY를 생략하면 파티션 row는 정렬되지 않으며, 처리 순서가 암시되지 않고, 모든 파티션 row가 peer입니다.

_order_clause_의 구문은 다음과 같습니다:
```
1order_clause:
2      ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
```
각 ORDER BY 표현식 뒤에는 선택적으로 정렬 방향을 나타내는 ASC 또는 DESC를 지정할 수 있습니다. 방향을 지정하지 않으면 기본값은 ASC입니다. NULL 값은 오름차순 정렬에서는 먼저, 내림차순 정렬에서는 나중에 정렬됩니다.

window 정의 내의 ORDER BY는 개별 파티션 내에 적용됩니다. 결과 집합 전체를 정렬하려면 쿼리 top level에 ORDER BY를 포함하십시오.
frame_clause: 프레임은 현재 파티션의 서브셋이며, frame 절은 이 서브셋을 정의하는 방법을 지정합니다. frame 절 자체에는 많은 하위 절이 있습니다. 자세한 내용은
Section 14.20.3, “Window Function Frame Specification”을 참조하십시오.

14.20.2 Window Function Concepts and Syntax

14.20.2 Window Function 개념 및 Syntax