window functions 윈도우 함수.

윈도우 함수

참조 : https://mariadb.com/kb/en/library/window-functions-overview/

Syntax

function (expression) OVER (
  [ PARTITION BY expression_list ]
  [ ORDER BY order_list [ frame_clause ] ] ) 

function:
  A valid window function

expression_list:
  expression | column_name [, expr_list ]

order_list:
  expression | column_name [ ASC | DESC ] 
  [, ... ]

frame_clause:

The following functions (also called aggregate functions) can be used with the GROUP BY clause:

AVG : Returns the average value

BIT_AND : Bitwise AND

BIT_OR : Bitwise OR

BIT_XOR : Bitwise XOR

COUNT : Returns count of non-null values.

COUNT DISTINCT : Returns count of number of different non-NULL values.

GROUP_CONCAT : Returns string with concatenated values from a group.

MAX : Returns the maximum value

MIN : Returns the minimum value

STD : Population standard deviation

STDDEV : Population standard deviation

STDDEV_POP : Returns the population standard deviation

STDDEV_SAMP : Standard deviation

SUM : Sum total

VARIANCE : Population standard variance

VAR_POP : Population standard variance

VAR_SAMP : Returns the sample variance

따라하기 

(1) 테이블, 데이터 생성

CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);

INSERT INTO student VALUES
  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

-- 뎅이터 조회..
SELECT name, test, score from student;
Chun	SQL	75
Chun	Tuning	73
Esben	SQL	43
Esben	Tuning	31
Kaolin	SQL	56
Kaolin	Tuning	88
Tatiana	SQL	87
Tatiana	Tuning	83

-- 집계함수 avg만 사용했을때.
SELECT name, test, avg(score) from student;
Chun	SQL	67.0000

-- name, test, score 별로 집계함수 avg를 사용했을때.
SELECT name, test, score, avg(score) from student group by name, test, score;
Chun	SQL	75	75.0000
Chun	Tuning	73	73.0000
Esben	SQL	43	43.0000
Esben	Tuning	31	31.0000
Kaolin	SQL	56	56.0000
Kaolin	Tuning	88	88.0000
Tatiana	SQL	87	87.0000
Tatiana	Tuning	83	83.0000

-- test 별 파티션된 평균. (test 기준으로 score의 평균을 집계)
SELECT name, test, score, avg(score) over (PARTITION BY test) AS average_by_test from student;
Chun	SQL	75	65.2500
Chun	Tuning	73	68.7500
Esben	SQL	43	65.2500
Esben	Tuning	31	68.7500
Kaolin	SQL	56	65.2500
Kaolin	Tuning	88	68.7500
Tatiana	SQL	87	65.2500
Tatiana	Tuning	83	68.7500

-- name 별 파티션된 평균. (name 기준으로 score의 평균을 집계)
SELECT name, test, score, avg(score) over (PARTITION BY name) AS average_by_name from student;
Chun	SQL	75	74.0000
Chun	Tuning	73	74.0000
Esben	SQL	43	37.0000
Esben	Tuning	31	37.0000
Kaolin	SQL	56	72.0000
Kaolin	Tuning	88	72.0000
Tatiana	SQL	87	85.0000
Tatiana	Tuning	83	85.0000

추가된 window function 

window functions include 

ROW_NUMBER, 

RANK, 

DENSE_RANK, 

PERCENT_RANK, 

CUME_DIST, 

NTILE, 

COUNT, 

SUM, 

AVG, 

BIT_OR, 

BIT_AND and BIT_XOR

(1) ROW_NUMBER, RANK, DENSE_RANK  (https://mariadb.com/kb/en/library/row_number/ 참조)

ROW_NUMBER : course 파티션별로 순위를 보여줌.  같은 순위일 경우 순서대로 보여줌.

RANK : course 파티션별로 순위를 보여줌.  같은 랭킹일 경우  같은 숫자를 보여주고, 다음 랭킹에서 다음 순위 숫자를 보여줌.

DENSE_RANK : course 파티션별로 순위를 보여줌.  같은 랭킹일 경우 같은 숫자를 보여주고, 다음 랭킹에서 전체숫자에서 다음 숫자를 보여줌.

MariaDB [employees]> CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10));

Query OK, 0 rows affected (0.02 sec)

MariaDB [employees]> INSERT INTO student VALUES 

    ->   ('Maths', 60, 'Thulile'),

    ->   ('Maths', 60, 'Pritha'),

    ->   ('Maths', 70, 'Voitto'),

    ->   ('Maths', 55, 'Chun'),

    ->   ('Biology', 60, 'Bilal'),

    ->    ('Biology', 70, 'Roger');

Query OK, 6 rows affected (0.00 sec)

Records: 6  Duplicates: 0  Warnings: 0

MariaDB [employees]> SELECT 

    ->   RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS rank, 

    ->   DENSE_RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS dense_rank, 

    ->   ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark DESC) AS row_num, 

    ->   course, mark, name 

    -> FROM student ORDER BY course, mark DESC;

+------+------------+---------+---------+------+---------+

| rank | dense_rank | row_num | course  | mark | name    |

+------+------------+---------+---------+------+---------+

|    1 |          1 |       1 | Biology |   70 | Roger   |

|    2 |          2 |       2 | Biology |   60 | Bilal   |

|    1 |          1 |       1 | Maths   |   70 | Voitto  |

|    2 |          2 |       2 | Maths   |   60 | Thulile |

|    2 |          2 |       3 | Maths   |   60 | Pritha  |

|    4 |          3 |       4 | Maths   |   55 | Chun    |

+------+------------+---------+---------+------+---------+

6 rows in set (0.00 sec)

파티션의 행 수를 입력해서 사용.

PERCENT_RANK     https://mariadb.com/kb/en/library/percent_rank/

(rank - 1) / (number of rows in the window or partition - 1) 

CUME_DIST     https://mariadb.com/kb/en/library/cume_dist/

(number of rows <= current row) / (total rows)

NTILE, 

Aggregate functions (집계함수) 

COUNT, 

SUM, 

AVG, 

BIT_OR, 

BIT_AND and BIT_XOR