Неоднократно встречал деление данных в информационных системах (далее ИС) на
Этого недостаточно, чтобы представить типичное разнообразие данных с точки зрения их моделирования и задач, возникающих при эксплуатации ИС. Вот мое деление, которым пользуюсь несколько лет и которое кажется довольно удачным:
Опишу эти виды данных неформально, но доходчиво.
Если бы у нас были только агрегатные функции, мы были бы принуждены постоянно делать выбор: видеть детальные данные или агрегированные? Аналитические функции, известные также как оконные функции или over-функции, позволяют одновременно получить и то и другое. С их помощью можно видеть данные, агрегированные по множеству строк, рядом с детальными данными.
Обычные, или скалярные, функции принимают на вход значения столбцов одной — текущей — строки курсора. Агрегатные функции принимают на вход значения столбцов всех строк курсора, определяемых where и group by (или их отсутствием). Оконные функции принимают на вход значения столбцов множества строк, определяемого в предложении over и зависящего от текущей строки.
Рассмотрим оконные функции на примере СУБД PosgreSQL 15.
Это продолжение поста Пробелы и острова, или Gaps and islands. Часть I
Напомню, что задачи вида "пробелы и острова", или "gaps and islands", возникают, когда в некоторой последовательности нужно найти участки (диапазоны, интервалы) непрерывных данных – "острова" – и/или участки отсутствия данных – "пробелы".
Часто последовательность данных – это хронологическая последовательность, то есть, развернутая во времени, например, по датам. Тогда "острова" – это непрерывные периоды, когда некое событие происходит ежедневно, а "пробелы" – это непрерывные периоды, в течение которых это событие не происходит.
Рассмотрим решение задачи "пробелы и острова" на хронологической последовательности данных.
Задачи вида "пробелы и острова", или "gaps and islands", возникают, когда в некоторой последовательности нужно найти участки (диапазоны, интервалы) непрерывных данных – "острова" – и/или участки отсутствия данных – "пробелы".
С наступленим эры оконных функций (они же аналитические, они же over-функции) задачи вида "пробелы и острова" решаются с помощью этих функций.
Примеры ниже с таблицей t1 взяты мной из статьи Solving Gaps and Islands with Enhanced Window Functions by Itzik Ben-Gan и адаптированы для выполнения на PostgreSQL.
После примеров с таблицей t1 я разберу более прикладной оригинальный пример с таблицей ежедневных продаж daily_sales.
В посте Периодические таблицы (не химия) я писал о специфике работы с таблицами, строки которых содержат период действия. В частности, рассматривал способы обеспечения того, чтобы периоды не перекрывались. При этом периоды действия определялись двумя полями: первая дата действия и последняя дата действия, где обе даты включены в период. Такой подход позволяет выбирать действующую строку с помощью оператора between, как в следующем примере:
select *
from ttt
where current_date between first_date and last_date
;
Подход естественный и очевидный. В самом деле, январь длится с 1 по 31, включительно. Неделя длится с понедельника по воскресенье, включительно. Акция в магазине проходит с 10 по 15 апреля, включительно.
Однако! Магазин работает с 10 до 20 часов, исключая 20 часов. Обеденный перерыв длится с 13 до 14 часов, исключая 14 часов, а с 14 до 14:30 проходит совещание – исключая 14:30.