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

LEAD()

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

SELECT
     call_id,
     call_datetime,
     LEAD(call_datetime [, offset_n, 0]) OVER ([PARTITION BY ...] ORDER BY call_datetime) AS next_call_datetime
         FROM call;

LAG()

Функция подобна LEAD(). Обращается к значениям поля предшествующих строк. Функция полезна для обнаружения разрывов, трендов, последовательных событий.

SELECT
     call_id,
     call_datetime,
     LAG(call_datetime) OVER (ORDER BY call_datetime) AS previous_call_datetime
         FROM call;

RANK()

Функция присваивает каждой строке порядковый номер (ранг) на основе указанного критерия. Сначала функция сортирует данные по указанному столбцу, а затем присваивает каждой строке порядковый номер: 1, 2, 3 и так далее. Если две или более строк имеют одинаковое значение по критерию ранжирования, RANK() присвоит им одинаковый ранг. Самое главное: после этих одинаковых рангов, следующий ранг будет пропущен.
Функция полезна для определения топ N, создания рейтингов.

SELECT
     employee_id,
     employee,
     salary,
     RANK() OVER ([PARTITION BY ...] ORDER BY salary DESC) AS salary_rank
         FROM employee_salary;

DENCE_RANK()

Функция подобна RANK(), присваивает одинаковый ранг при ничьей, не пропуская следующий ранг (идёт плотно).

SELECT
     employee_id,
     employee,
     salary,
     DENCE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
         FROM employee_salary;

SUM() OVER()

Вычисляет суммы нарастающими итогом или подытоги по секциям. Позволяет вычислять подытоги без группировки (без GROUP BY).

SELECT
     product_id,
     product,
     price,
     product_group,
     SUM(price) OVER (PARTITION BY product_group) AS price_total
         FROM product;

AVG() OVER()

Вычисляет средние значения по секциям, помогает сравнивать индивидуальные значения с средними значениями по группе.

SELECT
     product_id,
     product,
     price,
     product_group,
     AVG(price) OVER (PARTITION BY product_group) AS price_average
         FROM product;

ROW_NUMBER()

Используется, когда нужны уникальные последовательные номера строк во всем наборе / в группах. Обязательно требует сортировки.
Полезна для идентификации уникальных записей (когда нет ключа в таблице), выборки "только одного" из дубликатов строк, для пагинации (разбивки на страницы).

SELECT
     product_id,
     product,
     price,
     product_group,
     ROW_NUMBER() OVER (PARTITION BY product_group ORDER BY price DESC) AS product_rank
         FROM product;

NTILE(n)

Делит отсортированные строки на n примерно равных сегментов (бакетов), присваивает каждой строке номер группы (от 1 до n). Сначала функция сортирует все строки (или строки внутри группы) по указанному столбцу (важно, ибо определяет, кто в какую группу попадет). Затем подсчитывается общее количество строк в этом отсортированном наборе. Далее - деление на группы: пытается равномерно распределить эти строки по n группам. Если количество строк делится на n без остатка, то все группы будут абсолютно одинакового размера. Если есть остаток, то первые группы (начиная с 1) будут иметь на 1 строку больше, чем остальные, чтобы распределение было максимально равномерным.
Функция отлично подходит для создания процентилей или квартилей.

SELECT
     product_id,
     product,
     NTILE(4) OVER ([PARTITION BY ...] ORDER BY price DESC) AS price_quartile
         FROM product;

CUME_DIST()

Вычисляет кумулятивное распределение, возвращает значение от 0 до 1, которое показывает относительную позицию текущей строки в отсортированном наборе данных. Отвечает на вопрос: "Какая доля всех строк имеет значение меньшее или равное значению в текущей строке". Сначала функция сортирует все строки (или строки внутри группы) по указанному столбцу, затем для каждой строки она подсчитывает:
сколько строк имеют значение, которое меньше или равно значению в текущей строке,
общее количество строк в наборе данных (или в группе).
затем делит первое число на второе.
CUME_DIST() подходит для анализа на основе процентилей, поиска выбросов, сегментации данных (разделить данные на группы, например, худшие 10%, средние 80%, лучшие 10%).

SELECT
     employee_id,
     employee,
     salary,
     CUME_DIST() OVER ([PARTITION BY ...] ORDER BY salary) AS salary_cume
         FROM employee_salary;