References

• https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
• https://www.mysqltutorial.org/mysql-window-functions/

1. 윈도우 함수(Window Function)란?

윈도우 함수는 query row 집합에 대해 집계 연산과 비슷한 동작을 수행한다.

하지만 집계 함수는 여러 query rows를 하나의 결과 row로 만든다면, 윈도우 함수는 각 query row에 대해 결과를 반환한다.

특징

• 윈도우 함수가 평가되고 있는 현재 row와 관련된 query rows는 현재 row에 대한 window를 구성한다고 판단한다.
• 윈도우 함수는 SELECT list와 ORDER BY 절에서만 허용된다.
• 윈도우 함수는 OVER 절과 함께 사용된다.
• 대부분의 집계 함수는 윈도우 함수로 사용될 수 있다. 함수의 사용 용도는 OVER 절이 뒤이어 나오는지를 기반으로 판단한다.

종류

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

예시

SELECT
   year, country, product, profit,
   ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
   ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
 FROM sales;

+------+---------+------------+--------+----------+----------+
| year | country | product    | profit | row_num1 | row_num2 |
+------+---------+------------+--------+----------+----------+
| 2000 | Finland | Computer   |   1500 |        2 |        1 |
| 2000 | Finland | Phone      |    100 |        1 |        2 |
| 2001 | Finland | Phone      |     10 |        3 |        3 |
| 2000 | India   | Calculator |     75 |        2 |        1 |
| 2000 | India   | Calculator |     75 |        3 |        2 |
| 2000 | India   | Computer   |   1200 |        1 |        3 |
| 2000 | USA     | Calculator |     75 |        5 |        1 |
| 2000 | USA     | Computer   |   1500 |        4 |        2 |
| 2001 | USA     | Calculator |     50 |        2 |        3 |
| 2001 | USA     | Computer   |   1500 |        3 |        4 |
| 2001 | USA     | Computer   |   1200 |        7 |        5 |
| 2001 | USA     | TV         |    150 |        1 |        6 |
| 2001 | USA     | TV         |    100 |        6 |        7 |
+------+---------+------------+--------+----------+----------+

2. 윈도우 함수의 구성

OVER 절

OVER 절은 두 가지 형태로 작성할 수 있다.

1. OVER (window_spec) (w/ 괄호): 윈도우의 스펙이 괄호 안에 곧바로 나타나야 한다.

   윈도우의 스펙은 다음과 같은 순서로 나타낸다.

   [window_name] [partition_clause] [order_clause] [frame_clause]

   • window_name: 이미 WINDOW 절에서 named window가 정의되어 있을 때 해당 이름을 사용한다. 그 뒤 옵션들도 제공된다면, 해당 named window의 interpretation을 수정한다.
   • partition_clause: query rows를 어떻게 나눌 것인지를 나타내는 것으로, 윈도우 함수의 결과가 이에 기반하게 된다. 해당 값이 생략되면, 모든 query rows를 포함하는 하나의 partition으로 나뉘게 된다.
   • order_clause: 각 partition 내에서 row를 어떻게 정렬할지를 나타내는 것으로, 오름차순 및 내림차순 설정이 가능하다.
   • frame_clause: 현재 partition의 subset인 frame을 정의한다.

2. OVER window_name (w/o 괄호): WINDOW 절을 통해 named window가 정의되어 있다면, 해당 이름을 괄호 없이 곧바로 사용한다.

frame와 named window에 대해서는 이어서 알아보자!

Frame

https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

• frame이란 현재 partition의 subset이며, OVER() 절의 맨 마지막 요소를 통해 설정할 수 있다.

• frame은 현재 row에 대해 결정되며, partition 내에서 현재 row의 위치에 따라 frame을 이동할 수 있다.

• 다음과 같은 문법으로 사용한다.

  frame_clause:
      frame_units frame_extent
  
  frame_units:
      {ROWS | RANGE}

  • frame_units: current row와 frame rows 간 관계를 명시한다.

    • ROWS: 시작과 끝의 row position을 통해 frame이 정의된다. current row와의 row numbers 차이가 offset에 해당한다.
    • RANGE: value range를 통해 frame이 정의된다. current row value와의 row value 차이가 offset에 해당한다.

  • frame_extent: frame의 시작과 끝을 지정한다.

    frame_extent:
        {frame_start | frame_between}

    • BETWEEN 이용하기: frame_start가 frame_end 보다 나중에 등장하면 안 되는 것에 주의한다.

      frame_between:
          BETWEEN frame_start AND frame_end

    • frame의 시작 및 끝 명시하기: 만약 현재 row가 frame의 마지막 row라면 시작만 명시해도 된다.

      frame_start, frame_end: {
          CURRENT ROW
        | UNBOUNDED PRECEDING
        | UNBOUNDED FOLLOWING
        | expr PRECEDING
        | expr FOLLOWING
      }

      frame_units별로 bound의 의미가 다른 경우를 표로 정리하면 다음과 같다.

      	ROW	RANGE
      CURRENT ROW	current row	peers
      expr PRECEDING	expr rows before the current row	rows with values equal to the <current row value - expr> (current row value가 NULL이라면, peers)
      expr FOLLOWING	expr rows after the current row	rows with values equal to the <current row value + expr> (current row value가 NULL이라면, peers)

      반면, 다음의 경우에는 frame_units와 상관없이 bound의 의미가 동일하다.

      UNBOUNDED PRECEDING	first partition row 부터 current row 까지
      UNBOUNDED FOLLOWING	current row 부터 last partition row 까지

Named Windows

• 윈도우에게 이름을 부여하는 방법으로, WINDOW 절을 사용한다.

• WINDOW 절은 HAVING과 ORDER BY 절 사이에 위치한다.

• OVER 절의 괄호 안에 설정하는 window_spec과 동일한 형식으로 설정한다.

• 같은 윈도우를 가지고 여러 OVER 절에 사용할 수 있어 편리하다.

  -- original
  SELECT
    val,
    ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
    RANK()       OVER (ORDER BY val) AS 'rank',
    DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
  FROM numbers;

  -- simpler!
  SELECT
    val,
    ROW_NUMBER() OVER w AS 'row_number',
    RANK()       OVER w AS 'rank',
    DENSE_RANK() OVER w AS 'dense_rank'
  FROM numbers
  WINDOW w AS (ORDER BY val);

• OVER 절에서 named window의 이름과 함께 다른 옵션도 기입한다면, 해당 옵션을 적용하여 윈도우를 수정할 수 있다.

  SELECT
    DISTINCT year, country,
    FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first,
    FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
  FROM sales
  WINDOW w AS (PARTITION BY country);

3. 대표적인 윈도우 함수

RANK, DENSE_RANK, ROW_NUMBER

순위를 구할 때 사용한다.

SELECT
 val,
 ROW_NUMBER() OVER w AS 'row_number',
 RANK()       OVER w AS 'rank',
 DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

+------+------------+------+------------+
| val  | row_number | rank | dense_rank |
+------+------------+------+------------+
|    1 |          1 |    1 |          1 |
|    1 |          2 |    1 |          1 |
|    2 |          3 |    3 |          2 |
|    3 |          4 |    4 |          3 |
|    3 |          5 |    4 |          3 |
|    3 |          6 |    4 |          3 |
|    4 |          7 |    7 |          4 |
|    4 |          8 |    7 |          4 |
|    5 |          9 |    9 |          5 |
+------+------------+------+------------+

LAG, LEAD

• LAG는 이전(preceding) row를, LEAD는 다음(following) row를 참조한다.

• 이때, , 이후에 설정할 수 있는 값인 N을 통해 살펴볼 row의 개수를 지정할 수 있으며, 또 , 이후에 설정할 수 있는 값인 default를 통해 N개의 row를 전부 볼 수 없을 때 사용하는 기본값을 설정할 수 있다.

  default는 각각 1, NULL이다.

SELECT
 t, val,
 LAG(val)        OVER w AS 'lag',
 LEAD(val)       OVER w AS 'lead',
 val - LAG(val)  OVER w AS 'lag diff',
 val - LEAD(val) OVER w AS 'lead diff'
FROM series
WINDOW w AS (ORDER BY t);

+----------+------+------+------+----------+-----------+
| t        | val  | lag  | lead | lag diff | lead diff |
+----------+------+------+------+----------+-----------+
| 12:00:00 |  100 | NULL |  125 |     NULL |       -25 |
| 13:00:00 |  125 |  100 |  132 |       25 |        -7 |
| 14:00:00 |  132 |  125 |  145 |        7 |       -13 |
| 15:00:00 |  145 |  132 |  140 |       13 |         5 |
| 16:00:00 |  140 |  145 |  150 |       -5 |       -10 |
| 17:00:00 |  150 |  140 |  200 |       10 |       -50 |
| 18:00:00 |  200 |  150 | NULL |       50 |      NULL |
+----------+------+------+------+----------+-----------+