Рамки окон

Синтаксис

frame_clause:
  {ROWS | RANGE} {frame_border | BETWEEN frame_border AND frame_border}

frame_border:
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | CURRENT ROW
  | expr PRECEDING
  | expr FOLLOWING

Описание

Основное описание функций окон приведено в Обзоре функций окон. Рамки окон расширяют эти возможности, позволяя функции включать в себя заданное количество строк вокруг текущей строки.

В них входят:

• Все строки до текущей строки (UNBOUNDED PRECEDING), например RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• Все строки после текущей строки (UNBOUNDED FOLLOWING), например RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
• Заданное количество строк до текущей строки (expr PRECEDING), например RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
• Заданное количество строк после текущей строки (expr PRECEDING и expr FOLLOWING), например RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING
• Заданное количество строк как до, так и после текущей строки, например RANGE BETWEEN 6 PRECEDING AND 3 FOLLOWING

Следующие функции работают с рамками окон:

• AVG
• BIT_AND
• BIT_OR
• BIT_XOR
• COUNT
• LEAD
• MAX
• MIN
• NTILE
• STD
• STDDEV
• STDDEV_POP
• STDDEV_SAMP
• SUM
• VAR_POP
• VAR_SAMP
• VARIANCE

Рамки окон определяются frame_clause в запросе функции окна.

Рассмотрим пример:

CREATE TABLE `student_test` (
  name char(10),
  test char(10),
  score tinyint(4)
);

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

SELECT name, test, score, SUM(score) 
  OVER () AS total_score 
  FROM student_test;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Chun    | SQL    |    75 |         453 |
| Chun    | Tuning |    73 |         453 |
| Esben   | SQL    |    43 |         453 |
| Esben   | Tuning |    31 |         453 |
| Kaolin  | SQL    |    56 |         453 |
| Kaolin  | Tuning |    88 |         453 |
| Tatiana | SQL    |    87 |         453 |
+---------+--------+-------+-------------+

Если не указать предложение OVER, функция SUM выполняется над всем набором данных. Однако если мы укажем условие ORDER BY, основанное на оценке (и упорядочим весь результат аналогичным образом для ясности), будет возвращен следующий результат:

SELECT name, test, score, SUM(score) 
  OVER (ORDER BY score) AS total_score 
  FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |          31 |
| Esben   | SQL    |    43 |          74 |
| Kaolin  | SQL    |    56 |         130 |
| Chun    | Tuning |    73 |         203 |
| Chun    | SQL    |    75 |         278 |
| Tatiana | SQL    |    87 |         365 |
| Kaolin  | Tuning |    88 |         453 |
+---------+--------+-------+-------------+

Столбец total_score представляет собой суммарную оценку текущей строки и всех предыдущих строк. Рамка окна в этом примере расширяется по мере выполнения функции.

В приведенном выше запросе используется значение по умолчанию для определения рамки окна. Его можно явно записать следующим образом:

SELECT name, test, score, SUM(score) 
  OVER (ORDER BY score RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_score 
  FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |          31 |
| Esben   | SQL    |    43 |          74 |
| Kaolin  | SQL    |    56 |         130 |
| Chun    | Tuning |    73 |         203 |
| Chun    | SQL    |    75 |         278 |
| Tatiana | SQL    |    87 |         365 |
| Kaolin  | Tuning |    88 |         453 |
+---------+--------+-------+-------------+

Давайте рассмотрим альтернативные варианты:

Во-первых, для применения функции окна к текущей строке и всем последующим строкам можно использовать UNBOUNDED FOLLOWING:

SELECT name, test, score, SUM(score) 
  OVER (ORDER BY score RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS total_score 
  FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |         453 |
| Esben   | SQL    |    43 |         422 |
| Kaolin  | SQL    |    56 |         379 |
| Chun    | Tuning |    73 |         323 |
| Chun    | SQL    |    75 |         250 |
| Tatiana | SQL    |    87 |         175 |
| Kaolin  | Tuning |    88 |          88 |
+---------+--------+-------+-------------+

Можно указать количество строк вместо всего неограниченного набора последующих или предшествующих строк. Следующий пример берет текущую строку и предыдущую строку:

SELECT name, test, score, SUM(score) 
  OVER (ORDER BY score ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS total_score 
  FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |          31 |
| Esben   | SQL    |    43 |          74 |
| Kaolin  | SQL    |    56 |          99 |
| Chun    | Tuning |    73 |         129 |
| Chun    | SQL    |    75 |         148 |
| Tatiana | SQL    |    87 |         162 |
| Kaolin  | Tuning |    88 |         175 |
+---------+--------+-------+-------------+

Текущую строку и последующую строку:

SELECT name, test, score, SUM(score) 
  OVER (ORDER BY score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS total_score 
  FROM student_test ORDER BY score;
+---------+--------+-------+-------------+
| name    | test   | score | total_score |
+---------+--------+-------+-------------+
| Esben   | Tuning |    31 |          74 |
| Esben   | SQL    |    43 |         130 |
| Kaolin  | SQL    |    56 |         172 |
| Chun    | Tuning |    73 |         204 |
| Chun    | SQL    |    75 |         235 |
| Tatiana | SQL    |    87 |         250 |
| Kaolin  | Tuning |    88 |         175 |
+---------+--------+-------+-------------+