Розбиття на секції та віконні функції

Аналіз часових рядів у 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