Секционирование и оконные функции

Анализ временных рядов в PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Секционирование

Разбивает большую таблицу на меньшие подмножества — секции
Удобно при работе с большими наборами данных
Данные временных рядов:
- новые данные каждый день
- секционирование по диапазону дат

Иллюстрация разбиения большой таблицы на два подмножества

Диапазон

Границы диапазона:
- нижняя граница включается
- верхняя граница исключается
Альтернатива диапазону: секционирование по событию или категории

Диапазон 1 : 2000–2010

2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009

Диапазон 2 : 2010–2020

2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020

Секционирование

PARTITION BY RANGE

CREATE TABLE timetable (
    date_info DATE,
    train_id INTEGER,
    departure_time TIMESTAMP,
    arrival_time TIMESTAMP,
    delay INTEGER
) PARTITION BY RANGE (date_info);

Секция таблицы

PARITION OF FOR VALUES FROM
timetable — секционированная таблица, разбитая по столбцу date_info методом RANGE

CREATE TABLE timetable_y2020 PARTITION OF timetable
    FOR VALUES FROM ('2020-01-01') to ('2020-12-31');

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

Упрощают обычные SQL-запросы
Возвращают значение для каждой строки таблицы (может зависеть от других строк)

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

Конструкция OVER

Ключевое слово OVER указывает на оконную функцию
Пример: таблица regional_timetable — дата, время, идентификатор, задержки и регион
Без PARTITION BY оконная функция обрабатывает всю таблицу как одну секцию

SELECT region, train_id, delay, AVG(delay) OVER (PARTITION BY region)
FROM regional_timetable;

|region|train_id|   delay|     avg|
|------|--------|--------|--------|
| north|       6|00:00:05|00:00:05|
| north|       2|00:00:03|00:00:05|
| north|       5|00:00:07|00:00:05|
|  east|       1|00:00:10|00:00:21|
|  east|       3|00:00:32|00:00:21|
| south|       8|00:01:00|00:01:00|

Давайте потренируемся!

Анализ временных рядов в PostgreSQL