Partiționare și funcții de fereastră

Analiza seriilor de timp în PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Partiționare

Împarte un tabel mare în subseturi mai mici, numite partiții
Util pentru seturi mari de date
Date de tip serie temporală:
- date noi în fiecare zi
- partiționare după interval de date

Ilustrație a unui tabel mare împărțit în două subseturi

Un interval

Limitele intervalului:
- inclusiv la capătul inferior
- exclusiv la capătul superior
Alternativă la interval: partiționare după eveniment sau categorie

Interval 1 : 2000-2010

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

Interval 2 : 2010-2020

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

Partition by

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);

Partition of

PARITION OF FOR VALUES FROM
timetable este un tabel partiționat, partiționat pe date_info prin metoda RANGE

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

Funcții de fereastră

Simplifică interogările SQL obișnuite
Returnează o valoare pentru fiecare rând (poate depinde de alte rânduri)

Fereastră : setul de rânduri pe care operează funcția
Funcție de fereastră : funcția aplicată pe acele rânduri
Funcțiile agregate sunt comparative, dar rezultatul nu este grupat într-o singură valoare

Clauza OVER

Clauza OVER indică o funcție de fereastră
Exemplu: regional_timetable – dată, ore, id, întârzieri și regiune
Fără PARTITION BY: funcția tratează întregul tabel ca o singură partiție

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|

Să exersăm!

Analiza seriilor de timp în PostgreSQL