分割與視窗函式

PostgreSQL 的時間序列分析

Jasmin Ludolf

Content Developer, DataCamp

分割（Partitioning）

將大型資料表切成較小子集（分割）
處理大型資料集很實用
時間序列資料：
- 每天都有新資料
- 依日期範圍分割

Illustration of a larger table being split into two subsets

一個範圍

範圍邊界：
- 下界含括
- 上界不含
範圍以外的作法：依事件或類別分割

範圍 1：2000-2010

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

範圍 2：2010-2020

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

依欄位分割

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);

分割子表

PARITION OF FOR VALUES FROM
timetable 是一個以 RANGE 方法、在 date_info 上分割的分割表

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

視窗函式

簡化一般 SQL 查詢
對表中每列回傳一個值（可依賴其他列）

「視窗」：函式作用的一組資料列
「視窗函式」：用於該視窗的函式
聚合函式做比較，但結果不會彙總成單一值

OVER 子句

OVER 子句表示視窗函式
範例：regional_timetable 含日期、時間、id、延誤與地區
若無 PARTITION BY：視窗函式會把整個表視為一個分割

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|

一起來練習吧！

PostgreSQL 的時間序列分析