3724 подписчика

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

8 сентября 20248 сен 2024

516

5 мин

Оглавление

Вот несколько примеров работы оконных функций.
Пример 1: ROW_NUMBER()
Пример 2: RANK()

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

Оконные функции (window functions) в SQL позволяют выполнять вычисления по набору строк, связанных с текущей строкой, без необходимости группировать результат, как это делается в агрегатных функциях. Оконные функции не изменяют количество строк в результате и могут быть использованы с предложением OVER для выполнения вычислений в пределах определённого окна (набора строк).

Вот несколько примеров работы оконных функций.

Пример 1: ROW_NUMBER()

Предположим, у нас есть таблица employees с полями id, name, и salary. Мы хотим пронумеровать всех сотрудников по порядку в рамках одинаковых зарплат.

Результат:

Здесь ROW_NUMBER() нумерует строки в порядке возрастания зарплаты.

Пример 2: RANK()

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

Результат:

Здесь сотрудники с одинаковой зарплатой (3000) получают одинаковый ранг, и следующий ранг (3) пропускает значение "2".

Пример 3: NTILE()

Функция NTILE(N) разбивает строки на N групп с равным количеством строк, насколько это возможно.

Результат:

Здесь сотрудники распределены по двум группам (или "корзинам"). Первые две строки попали в первую группу, следующие две — во вторую.

Пример 4: SUM() с оконной функцией

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

Результат:

Здесь для каждой строки мы видим накопленную сумму зарплат всех предыдущих строк.

Пример 5: LAG() и LEAD()

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

Результат:

Функция LAG() возвращает значение из предыдущей строки, а LEAD() — из следующей строки.

Вот список основных оконных функций в SQL:

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

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

SUM() — вычисляет сумму значений.

SUM(column) OVER (PARTITION BY group_column ORDER BY order_column)

AVG() — вычисляет среднее значение.

AVG(column) OVER (PARTITION BY group_column ORDER BY order_column)

COUNT() — возвращает количество строк.

COUNT(column) OVER (PARTITION BY group_column ORDER BY order_column)

MIN() — возвращает минимальное значение.

MIN(column) OVER (PARTITION BY group_column ORDER BY order_column)

MAX() — возвращает максимальное значение.

MAX(column) OVER (PARTITION BY group_column ORDER BY order_column)

2. Ранжирующие функции

Эти функции позволяют вычислять ранги или порядковые номера для строк в наборе данных.

ROW_NUMBER() — присваивает уникальный порядковый номер каждой строке в пределах окна.

ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY order_column)

RANK() — присваивает ранг строкам в пределах окна с возможностью "разделения" рангов при одинаковых значениях.

RANK() OVER (PARTITION BY group_column ORDER BY order_column)

DENSE_RANK() — присваивает ранг строкам, но без пропуска значений ранга (в отличие от RANK).

DENSE_RANK() OVER (PARTITION BY group_column ORDER BY order_column)

NTILE(N) — разбивает строки на N частей и присваивает номер части каждой строке.

NTILE(4) OVER (PARTITION BY group_column ORDER BY order_column)

3. Функции смещения

Эти функции позволяют получать значения из предыдущих или последующих строк в пределах окна.

LAG() — возвращает значение из предыдущей строки относительно текущей строки.

LAG(column, offset, default_value) OVER (PARTITION BY group_column ORDER BY order_column)

offset — смещение назад (по умолчанию 1).

default_value — значение по умолчанию, если смещение выходит за пределы окна.

LEAD() — возвращает значение из следующей строки относительно текущей строки.

LEAD(column, offset, default_value) OVER (PARTITION BY group_column ORDER BY order_column)

4. Функции для работы с рамками (frames)

Эти функции позволяют вычислять итоговые значения на основе определённых диапазонов строк в пределах окна.

FIRST_VALUE() — возвращает первое значение в окне.

FIRST_VALUE(column) OVER (PARTITION BY group_column ORDER BY order_column)

LAST_VALUE() — возвращает последнее значение в окне.

LAST_VALUE(column) OVER (PARTITION BY group_column ORDER BY order_column)

NTH_VALUE() — возвращает n-е значение в окне.

NTH_VALUE(column, n) OVER (PARTITION BY group_column ORDER BY order_column)

Пример использования оконных функций

Пример: вычисление суммы по окну, порядкового номера и разницы значений между строками:

Компоненты оконной функции:

PARTITION BY — разделяет строки на группы (по аналогии с GROUP BY).
ORDER BY — задаёт порядок строк внутри каждой группы.
ROWS BETWEEN или RANGE BETWEEN — указывает диапазон строк для расчёта (например, от текущей строки до предыдущей или следующей строки).

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

Примечания:

MySQL: поддержка оконных функций появилась только в версии 8.0. В предыдущих версиях MySQL оконные функции не поддерживаются.
SQLite: поддержка оконных функций появилась в версии 3.25.0 (выпущенной в сентябре 2018 года). В более старых версиях этой СУБД оконные функции не поддерживаются.
Oracle: поддерживает оконные функции с версии 8i, но начиная с 12c, появилось больше продвинутых возможностей для работы с оконными функциями.

Заключение:
Приведённые примеры подходят для большинства современных СУБД, таких как PostgreSQL, MySQL (с версии 8.0), SQL Server, Oracle, Db2, SQLite (с версии 3.25.0), Amazon Redshift, Google BigQuery и Snowflake. Важно убедиться, что ваша версия СУБД поддерживает оконные функции, поскольку в старых версиях некоторых систем (например, MySQL до 8.0) они могут быть недоступны.