Particionamento e funções de janela

Análise de Séries Temporais no PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Particionamento

Divida uma tabela grande em subconjuntos (partições)
Útil com conjuntos de dados grandes
Séries temporais:
- dados novos todo dia
- particione por intervalo de datas

Ilustração de uma tabela maior sendo dividida em dois subconjuntos

Um intervalo

Limites do intervalo:
- inclusivo no limite inferior
- exclusivo no limite superior
Alternativa ao intervalo: particionar por evento ou categoria

Intervalo 1: 2000-2010

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

Intervalo 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 é uma tabela particionada, particionada em date_info usando o método RANGE

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

Funções de janela

Simplificam consultas SQL comuns
Retornam um valor por linha (podem depender de outras linhas)

Janela: conjunto de linhas em que a função atua
Função de janela: a função aplicada a essas linhas, ou janela
Funções de agregação são comparativas, mas o resultado não é agrupado em um único valor

Cláusula OVER

A cláusula OVER indica uma função de janela
Exemplo: regional_timetable com data, horários, id, atrasos e região
Se não houver PARTITION BY: a função de janela trata a tabela inteira como uma partição

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|

Vamos praticar!

Análise de Séries Temporais no PostgreSQL