Оконные функции aka over-функции

Если бы у нас были только агрегатные функции, мы были бы принуждены постоянно делать выбор: видеть детальные данные или агрегированные? Аналитические функции, известные также как оконные функции или over-функции, позволяют одновременно получить и то и другое. С их помощью можно видеть данные, агрегированные по множеству строк, рядом с детальными данными.

Обычные, или скалярные, функции принимают на вход значения столбцов одной — текущей — строки курсора. Агрегатные функции принимают на вход значения столбцов всех строк курсора, определяемых where и group by (или их отсутствием). Оконные функции принимают на вход значения столбцов множества строк, определяемого в предложении over и зависящего от текущей строки.

Рассмотрим оконные функции на примере СУБД PosgreSQL 15.

Пробелы и острова, или Gaps and islands. Часть II

Это продолжение поста Пробелы и острова, или Gaps and islands. Часть I

Напомню, что задачи вида "пробелы и острова", или "gaps and islands", возникают, когда в некоторой последовательности нужно найти участки (диапазоны, интервалы) непрерывных данных – "острова" – и/или участки отсутствия данных – "пробелы".

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

Рассмотрим решение задачи "пробелы и острова" на хронологической последовательности данных.

Пробелы и острова, или Gaps and islands. Часть I

Задачи вида "пробелы и острова", или "gaps and islands", возникают, когда в некоторой последовательности нужно найти участки (диапазоны, интервалы) непрерывных данных – "острова" – и/или участки отсутствия данных – "пробелы".

С наступленим эры оконных функций (они же аналитические, они же over-функции) задачи вида "пробелы и острова" решаются с помощью этих функций.

Примеры ниже с таблицей t1 взяты мной из статьи Solving Gaps and Islands with Enhanced Window Functions by Itzik Ben-Gan и адаптированы для выполнения на PostgreSQL.

После примеров с таблицей t1 я разберу более прикладной оригинальный пример с таблицей ежедневных продаж daily_sales.