Шпаргалка по оконным функциям SQL. Часть #1

SQL (Structured Query Language) — это язык для работы с базами данных. Позволяет выбирать данные и строить сложные отчеты. Сегодня SQL стал универсальным языком данных.

Оконные функции

Оконные функции вычисляют свои результаты на основе скользящего окна — набора строк, которые так или иначе связаны с текущей строкой.

Агрегатные и оконные функции

В отличие от агрегирующих функций, оконные функции не объединяют строки.

Синтаксис

SELECT <column_1>, 
       <column_2>,
       <window_function> OVER (PARTITION BY <...>
                               ORDER BY <...>
                               <window_frame>) <window_column_alias>
  FROM <table_name>;

SELECT city, 
       month,
       SUM(sold) OVER (PARTITION BY city 
                       ORDER BY month 
                       RANGE UNBOUNDED PRECEDING) AS total
  FROM sales;

Определение именованного окна

  SELECT <column_1>, 
         <column_2>,
         <window_function>() OVER <window_name>
    FROM <table_name>
   WHERE <...>
GROUP BY <...>
  HAVING <...>
  WINDOW <window_name> AS (
     PARTITION BY <...>
     ORDER BY <...>
     <window_frame>)
ORDER BY <...>;

  SELECT country,
         city, 
         RANK() OVER country_sold_avg
    FROM sales
   WHERE month BETWEEN 1 AND 6
GROUP BY country, city
  HAVING sum(sold) > 10000
  WINDOW country_sold_avg AS (
     PARTITION BY country 
     ORDER BY avg(sold) DESC) 
ORDER BY country, city;

Оператор PARTITION BY

Оператор PARTITION BY разделяет строки на несколько групп, называемых партициями (разделами), к которым применяется оконная функция

Партиция по умолчанию: Если предложение PARTITION BY не указано, то вся результирующая выборка считается одной партицией.

Результат данных справа — применение оператора PARTITION BY city

month	city	sold
1	Рим	200
2	Париж	500
1	Москва	100
1	Париж	300
2	Рим	300
2	Москва	400
3	Рим	400

month	city	sold	sum
1	Париж	300	800
2	Париж	500	800
1	Рим	200	900
2	Рим	300	900
3	Рим	400	900
1	Москва	100	500
2	Москва	400	500

Оператор ORDER BY

Оператор ORDER BY определяет порядок строк в каждой партиции, к которой применяется оконная функция

Сортировка по умолчанию: Если предложение ORDER BY не указано, порядок строк в каждой партиции является произвольным.

Результат данных справа — применение операторов PARTITION BY city ORDER BY month

month	city	sold
1	Рим	200
2	Париж	500
1	Москва	100
1	Париж	300
2	Рим	300
2	Москва	400
3	Рим	400

month	city	sold
1	Париж	300
2	Париж	500
1	Рим	200
2	Рим	300
3	Рим	400
1	Москва	100
2	Москва	400

Границы окна (window frame)

Границы окна определяют набор строк, связанных с текущей строкой. Для каждой партиции границы окна вычисляются отдельно.

Границы окна по умолчанию: если оператор ORDER BY определен, тогда окно использует поведение RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Без указания ORDER BY используется поведение ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

< ROWS | RANGE | GROUPS > BETWEEN нижняя_граница AND верхняя_граница

Границы окна могут быть заданы одним из пяти способов

UNBOUNDED PRECEDING или BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
n PRECEDING или BETWEEN n PRECEDING AND CURRENT ROW
CURRENT ROW или BETWEEN CURRENT ROW AND CURRENT ROW
n FOLLOWING или BETWEEN CURRENT ROW AND n FOLLOWING
UNBOUNDED FOLLOWING или BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Пример ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

1 строка до текущей строки (current row) и 1 строка после текущей строки (current row)

Пример RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Значения в диапазоне (range) от 3 до 5 и оператор ORDER BY должен содержать единственное выражение

Пример GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING

1 группа строк (group) до текущей строки (current row) и 1 группа строк (group) после текущей строки (current row), независимо от значения