パーティショニングとウィンドウ関数

PostgreSQLで学ぶ時系列分析

Jasmin Ludolf

Content Developer, DataCamp

パーティショニング

  • 大きな表を小さな集合（パーティション）に分割
  • 大規模データで有用
  • 時系列データ：
    • 毎日新しいデータ
    • 日付範囲でパーティション

大きな表が2つのサブセットに分割される図

PostgreSQLで学ぶ時系列分析

範囲の例

  • 範囲の境界：

    • 下端は含む
    • 上端は含まない

  • 代替：イベントやカテゴリでパーティション

範囲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

PostgreSQLで学ぶ時系列分析

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);
PostgreSQLで学ぶ時系列分析

Partition of

  • 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');
PostgreSQLで学ぶ時系列分析

ウィンドウ関数

  • 通常のSQLを簡潔化
  • 各行に値を返す（他の行に依存可）

 

  • ウィンドウ：関数を適用する行集合
  • ウィンドウ関数：その行集合に使う関数
  • 集約は比較だが、結果は単一値にまとめない
PostgreSQLで学ぶ時系列分析

OVER 句

  • OVER 句でウィンドウ関数を指定
  • 例：regional_timetable に日付、時刻、ID、遅延、地域
  • PARTITION BY がない場合：表全体を1つのパーティションとして扱う
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で学ぶ時系列分析

演習に進みましょう！

PostgreSQLで学ぶ時系列分析

Preparing Video For Download...