Partizionamento e funzioni finestra

Analisi delle serie temporali in PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Partizionamento

Suddividi una tabella grande in sottoinsiemi (partizioni)
Utile con dataset di grandi dimensioni
Dati di serie temporali:
- nuovi dati ogni giorno
- partiziona per intervalli di date

Illustrazione di una tabella grande suddivisa in due sottoinsiemi

Un intervallo

Confini degli intervalli:
- inclusivi all’estremo inferiore
- esclusivi all’estremo superiore
Alternativa agli intervalli: partiziona per evento o categoria

Intervallo 1: 2000-2010

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

Intervallo 2: 2010-2020

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

Partiziona per

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 è una tabella partizionata su date_info con metodo RANGE

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

Funzioni finestra

Semplificano le query SQL standard
Restituiscono un valore per ogni riga (può dipendere dalle altre)

Finestra: insieme di righe su cui opera la funzione
Funzione finestra: la funzione applicata a quella finestra
Le funzioni di aggregazione sono comparative, ma il risultato non è un unico valore

Clausola OVER

La clausola OVER indica una funzione finestra
Esempio: regional_timetable con data, orari, id, ritardi e regione
Senza PARTITION BY: la funzione tratta l’intera tabella come un’unica partizione

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|

Passiamo alla pratica !

Analisi delle serie temporali in PostgreSQL