Partitionierung und Window-Funktionen

Zeitreihenanalyse in PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Partitionierung

Große Tabelle in kleinere Teilmengen (Partitionen) aufteilen
Nützlich bei sehr großen Datensätzen
Zeitreihendaten:
- jeden Tag neue Daten
- nach Datumsbereichen partitionieren

Illustration einer großen Tabelle, die in zwei Teilmengen aufgeteilt wird

Ein Bereich

Bereichsgrenzen:
- untere Grenze inklusive
- obere Grenze exklusiv
Alternative zu Bereichen: nach Ereignis oder Kategorie partitionieren

Bereich 1 : 2000-2010

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

Bereich 2 : 2010-2020

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

Partitionieren nach

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 von

PARITION OF FOR VALUES FROM
timetable ist eine partitionierte Tabelle, partitioniert über date_info mit der Methode RANGE

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

Window-Funktionen

Vereinfacht reguläre SQL-Abfragen
Gibt für jede Zeile einen Wert zurück (kann von anderen Zeilen abhängen)

Window: die Zeilenmenge, auf der die Funktion arbeitet
Window-Funktion: die Funktion auf diesen Zeilen bzw. dem Window
Aggregatfunktionen sind vergleichend, aber das Ergebnis wird nicht zu einem Wert gruppiert

OVER-Klausel

Die OVER-Klausel kennzeichnet eine Window-Funktion
Beispiel: regional_timetable mit Datum, Zeiten, ID, Verspätungen und Region
Ohne PARTITION BY: betrachtet die Window-Funktion die ganze Tabelle als eine 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|

Lass uns üben!

Zeitreihenanalyse in PostgreSQL