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

Пришло время разобраться с оконными функциями и кратко зафиксировать основные моменты.

Всем рекомендую курс на степике. Всё последовательно и доступно!

общий порядок такой:

1. select
2. from
3. where
4. group by
5. window
6. order by

Ранжирование

Пример dense_rank(), в определении окна разбивка по департаментам

Пример ntile()

Сравнение со смещением — это когда мы смотрим, в чем разница между соседними значениями.

Оконные функции смещения

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

Чтобы границы фрейма совпадали с границами секции (или всего окна, если секция одна) — используют конструкцию rows between unbounded preceding and unbounded following в определении окна.

Агрегация — это когда мы считаем суммарные или средние показатели.

Скользящие агрегаты — это те же сумма и среднее. Только рассчитывают их не по всем элементам набора, а более хитрым способом.

В общем случае определение фрейма выглядит так:

rows between X preceding and Y following

Где X — количество строк перед текущей, а Y — количество строк после текущей:

Если указать вместо X или Y значение unbounded — это значит «граница секции»:

Если указать вместо X preceding или Y following значение current row — это значит «текущая запись»:

Фрейм никогда не выходит за границы секции, если столкнулся с ней —обрезается

Хотим рассчитать скользящее среднее по доходам за предыдущий и текущий месяц

Нарастающий итог

Функции для скользящих агрегатов

Скользящие агрегаты используют те же самые функции, что и агрегаты обычные:

• min() и max()
• count(), avg() и sum()
• group_concat()

Разница только в наличии фрейма у скользящих агрегатов.

Вот задачи, которые решаются с помощью оконных функций в SQL:

1. Ранжирование (всевозможные рейтинги).
2. Сравнение со смещением (соседние элементы и границы).
3. Агрегация (количество, сумма и среднее).
4. Скользящие агрегаты (сумма и среднее в динамике).

Есть еще статистические оконные функции, но мы их не рассматривали.

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

1. Из каких секций состоит (partition by).
2. Как отсортированы строки внутри секции (order by).
3. Как выглядит фрейм внутри секции (rows between).

window w as (
partition by ...
order by ...
rows between ... and ...
)

partition by поддерживается всеми оконными функциями и всегда необязательно. Если не указать — будет одна секция.

order by поддерживается всеми оконными функциями. Для функций ранжирования и смещения оно обязательно, для агрегации — нет. Если не указать order by для функции агрегации — она посчитает обычный агрегат, если указать — скользящий.

Фрейм поддерживается только некоторыми функциями:

• first_value(), last_value(), nth_value();
• функции агрегации.

Остальные функции фреймы не поддерживают.

SQLite реализует оконные функции точно так же, как PostgreSQL, так что если придется работать с постгресом .