Partitionering en vensterfuncties

Tijdreeksanalyse in PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Partitionering

Splits een grote tabel in kleinere subsets of partities
Handig voor grote datasets
Tijdreeksdata:
- elke dag nieuwe data
- partitioneren op datumbereiken

Illustratie van een grotere tabel die in twee subsets wordt gesplitst

Een bereik

Bereikgrenzen:
- ondergrens inclusief
- bovengrens exclusief
Alternatief voor bereik: partitioneren op gebeurtenis of categorie

Bereik 1: 2000-2010

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

Bereik 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 is een gepartitioneerde tabel, gepartitioneerd op date_info met de methode RANGE

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

Vensterfuncties

Maakt reguliere SQL-queries eenvoudiger
Geeft per rij een waarde (kan afhangen van andere rijen)

Venster: set rijen waarop de functie werkt
Vensterfunctie: de functie op dat venster
Aggregaties zijn vergelijkend, maar resultaat wordt niet tot één waarde gegroepeerd

OVER-clausule

De OVER-clausule geeft een vensterfunctie aan
Voorbeeld: regional_timetable met datum, tijden, id, vertraging en regio
Zonder PARTITION BY: de hele tabel is één partitie

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|

Laten we oefenen!

Tijdreeksanalyse in PostgreSQL