分区与窗口函数

PostgreSQL 中的时间序列分析

Jasmin Ludolf

Content Developer, DataCamp

分区

将大表拆分为更小的子集（分区）
处理大数据集时很有用
时间序列数据：
- 每天有新数据
- 按日期范围分区

将大表拆分为两个子集的示意图

一个范围

范围边界：
- 下界包含
- 上界不包含
范围以外的选择：按事件或类别分区

范围 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 为按 date_info 使用 RANGE 方法分区的表

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|

Passons à la pratique !

PostgreSQL 中的时间序列分析