Partitionnement et fonctions Fenêtre

Analyse de séries chronologiques dans PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Partitionnement

Scinder une grande table en sous-ensembles, ou partitions
Utile avec de grands ensembles de données
Données de séries chronologiques :
- nouvelles données chaque jour
- partitionner par plages de dates

Illustration d'une grande table scindée en deux sous-ensembles

Une plage

Bornes de plage :
- inclusive à l'extrémité inférieure
- exclusive à l'extrémité supérieure
Variante : partitionner par événement ou catégorie

Plage 1 : 2000-2010

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

Plage 2 : 2010-2020

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

Partitionner par

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 de

PARITION OF FOR VALUES FROM
timetable est une table partitionnée, sur date_info avec la méthode RANGE

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

Fonctions fenêtre

Simplifient les requêtes SQL courantes
Retourne une valeur par ligne (peut dépendre des autres lignes)

Fenêtre : ensemble de lignes sur lesquelles la fonction opère
Fonction fenêtre : fonction appliquée à ces lignes, ou fenêtre
Les fonctions d'agrégation comparent, mais le résultat n'est pas réduit à une seule valeur

Clause OVER

La clause OVER indique une fonction fenêtre
Exemple : regional_timetable contient date, heures, id, retards et région
Sans PARTITION BY : la fonction fenêtre traite toute la table comme une seule partition

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|

Passons à la pratique !

Analyse de séries chronologiques dans PostgreSQL