TimescaleDB によるデータ分析

TimescaleDB は、IoT 機器のセンサーやスマートメーターが生成するデータのように、時系列に沿って発生するデータを扱うのに適した時系列データベースの機能を、リレーショナルデータベース PostgreSQL に追加する拡張モジュールです。

この記事では、TimescaleDB の備える時系列データ向けの分析機能について紹介します。TimescaleDB の概要やインストール、基本的な使い方については前回の TimescaleDB の紹介 を参照してください。

時系列データの分析関数

TimescaleDB には時系列データの分析に便利な関数がいくつかあります。ここでは、前回生成したデータを使って各関数の概要と使い方について紹介していきます。それには、まず、test データベースに接続しておきます。

$ psql test
psql (13.2)
"help"でヘルプを表示します。

=#

time_bucket 関数

time_bucket 関数は時間を任意の間隔で丸める関数です。同じような関数は PostgreSQL にもあって date_trunc 関数と言います。

date_trunc 関数を使うと、例えば、1 分間隔で収集した温度のデータに対し、1 時間ごとの平均を求められます。

=# SELECT date_trunc('hour', time) AS time_1h,
     location,
     avg(temperature)
   FROM conditions
   GROUP BY time_1h, location
   ORDER BY time_1h, location;
       time_1h       | location |        avg
---------------------+----------+--------------------
 2020-01-30 00:00:00 | nagoya   | 19.883333333333333
 2020-01-30 00:00:00 | osaka    |  20.68333333333333
 2020-01-30 00:00:00 | tokyo    |              20.73
 2020-01-30 01:00:00 | nagoya   |  19.65666666666666
 2020-01-30 01:00:00 | osaka    |              20.82
 2020-01-30 01:00:00 | tokyo    |  20.04000000000001
 2020-01-30 02:00:00 | nagoya   | 18.854999999999993
 2020-01-30 02:00:00 | osaka    |  19.89833333333333
 2020-01-30 02:00:00 | tokyo    | 20.345000000000006
 2020-01-30 03:00:00 | nagoya   | 20.338333333333335
 2020-01-30 03:00:00 | osaka    |             20.395
 2020-01-30 03:00:00 | tokyo    | 20.781666666666673
(省略)

同じことは time_bucket 関数でもできます。

=# SELECT time_bucket('1 hour', time) AS time_1h,
     location,
     avg(temperature)
   FROM conditions
   GROUP BY time_1h, location
   ORDER BY time_1h, location;
       time_1h       | location |        avg
---------------------+----------+--------------------
 2020-01-30 00:00:00 | nagoya   | 19.883333333333333
 2020-01-30 00:00:00 | osaka    |  20.68333333333333
 2020-01-30 00:00:00 | tokyo    |              20.73
 2020-01-30 01:00:00 | nagoya   |  19.65666666666666
 2020-01-30 01:00:00 | osaka    |              20.82
 2020-01-30 01:00:00 | tokyo    |  20.04000000000001
 2020-01-30 02:00:00 | nagoya   | 18.854999999999993
 2020-01-30 02:00:00 | osaka    |  19.89833333333333
 2020-01-30 02:00:00 | tokyo    | 20.345000000000006
 2020-01-30 03:00:00 | nagoya   | 20.338333333333335
 2020-01-30 03:00:00 | osaka    |             20.395
 2020-01-30 03:00:00 | tokyo    | 20.781666666666673
(省略)

date_trunc 関数と time_bucket 関数の違いは、date_trunc 関数が 1 時間や 1 日など、固定の時間でしか丸められないのに対し、time_bucket 関数は 3 時間や 5 日など、任意の時間で丸められることです。time_bucket 関数を使うと、例えば、3 時間ごとの平均を求められます。

=# SELECT time_bucket('3 hours', time) AS time_3h,
     location,
     avg(temperature)
   FROM conditions
   GROUP BY time_3h, location
   ORDER BY time_3h, location;
       time_3h       | location |        avg
---------------------+----------+--------------------
 2020-01-30 00:00:00 | nagoya   |             19.465
 2020-01-30 00:00:00 | osaka    |  20.46722222222223
 2020-01-30 00:00:00 | tokyo    | 20.371666666666673
 2020-01-30 03:00:00 | nagoya   | 20.202777777777776
 2020-01-30 03:00:00 | osaka    |  19.89277777777778
 2020-01-30 03:00:00 | tokyo    | 21.122222222222224
 2020-01-30 06:00:00 | nagoya   |  19.99055555555557
 2020-01-30 06:00:00 | osaka    | 19.288333333333338
 2020-01-30 06:00:00 | tokyo    | 19.353333333333335
 2020-01-30 09:00:00 | nagoya   | 20.950000000000003
 2020-01-30 09:00:00 | osaka    | 20.285000000000007
 2020-01-30 09:00:00 | tokyo    | 20.203333333333326
(省略)

time_bucket_gapfill 関数

time_bucket_gapfill 関数は time_bucket 関数と同じく時間を任意の間隔で丸める関数ですが、欠落したデータを補ってくれることが異なります。

例えば、2020 年 2 月 1 日、東京のデータを削除し、人為的にデータを欠落させ、time_bucket 関数を使って 3 時間ごとの平均を求めると、6 時、9 時のデータが欠落しているのが分かります。

=# DELETE FROM conditions
   WHERE time >= '2020-02-01 06:00:00'
     AND time < '2020-02-01 12:00:00' AND location = 'tokyo'; DELETE 360 =# SELECT time_bucket('3 hours', time) AS time_3h, location, avg(temperature) FROM conditions WHERE time >= '2020-02-01'
     AND time < '2020-02-02'
   GROUP BY time_3h, location
   ORDER BY time_3h, location;
       time_3h       | location |        avg
---------------------+----------+--------------------
 2020-02-01 00:00:00 | nagoya   |  19.75833333333333
 2020-02-01 00:00:00 | osaka    |  20.46555555555556
 2020-02-01 00:00:00 | tokyo    | 19.315555555555555
 2020-02-01 03:00:00 | nagoya   | 19.328888888888887
 2020-02-01 03:00:00 | osaka    | 20.167777777777783
 2020-02-01 03:00:00 | tokyo    | 19.592777777777773
 2020-02-01 06:00:00 | nagoya   |  19.87055555555555
 2020-02-01 06:00:00 | osaka    |  20.07277777777777
 2020-02-01 09:00:00 | nagoya   | 19.240555555555563
 2020-02-01 09:00:00 | osaka    |             19.915
 2020-02-01 12:00:00 | nagoya   | 20.018888888888895
 2020-02-01 12:00:00 | osaka    |  19.66388888888888
 2020-02-01 12:00:00 | tokyo    | 19.437777777777782
 2020-02-01 15:00:00 | nagoya   | 19.904444444444444
 2020-02-01 15:00:00 | osaka    |  20.11388888888888
 2020-02-01 15:00:00 | tokyo    | 19.707222222222217
 2020-02-01 18:00:00 | nagoya   | 20.251111111111115
 2020-02-01 18:00:00 | osaka    | 20.023888888888884
 2020-02-01 18:00:00 | tokyo    | 20.571666666666676
 2020-02-01 21:00:00 | nagoya   |  19.91722222222222
 2020-02-01 21:00:00 | osaka    | 20.952777777777772
 2020-02-01 21:00:00 | tokyo    | 19.904444444444447
(22 行)

データ欠落

それに対し、time_bucket_gapfill 関数を使うと、欠落したデータに NULL が補われるようになります。

=# SELECT time_bucket_gapfill('3 hours', time) AS time_3h,
     location,
     avg(temperature)
   FROM conditions
   WHERE time >= '2020-02-01'
     AND time < '2020-02-02'
   GROUP BY time_3h, location
   ORDER BY time_3h, location;
       time_3h       | location |        avg
---------------------+----------+--------------------
 2020-02-01 00:00:00 | nagoya   |  19.75833333333333
 2020-02-01 00:00:00 | osaka    |  20.46555555555556
 2020-02-01 00:00:00 | tokyo    | 19.315555555555555
 2020-02-01 03:00:00 | nagoya   | 19.328888888888887
 2020-02-01 03:00:00 | osaka    | 20.167777777777783
 2020-02-01 03:00:00 | tokyo    | 19.592777777777773
 2020-02-01 06:00:00 | nagoya   |  19.87055555555555
 2020-02-01 06:00:00 | osaka    |  20.07277777777777
 2020-02-01 06:00:00 | tokyo    |
 2020-02-01 09:00:00 | nagoya   | 19.240555555555563
 2020-02-01 09:00:00 | osaka    |             19.915
 2020-02-01 09:00:00 | tokyo    |
 2020-02-01 12:00:00 | nagoya   | 20.018888888888895
 2020-02-01 12:00:00 | osaka    |  19.66388888888888
 2020-02-01 12:00:00 | tokyo    | 19.437777777777782
 2020-02-01 15:00:00 | nagoya   | 19.904444444444444
 2020-02-01 15:00:00 | osaka    |  20.11388888888888
 2020-02-01 15:00:00 | tokyo    | 19.707222222222217
 2020-02-01 18:00:00 | nagoya   | 20.251111111111115
 2020-02-01 18:00:00 | osaka    | 20.023888888888884
 2020-02-01 18:00:00 | tokyo    | 20.571666666666676
 2020-02-01 21:00:00 | nagoya   |  19.91722222222222
 2020-02-01 21:00:00 | osaka    | 20.952777777777772
 2020-02-01 21:00:00 | tokyo    | 19.904444444444447
(24 行)

time_bucket_gapfill 関数を使う場合には、どこからどこまでデータを補うかを指定するため、時間の範囲を WHERE 句か startfinish 引数で指定する必要があります。指定しないとエラーになります。

(以下、例示のため、実行不要)
=# SELECT time_bucket_gapfill('3 hours', time) AS time_3h,
     location,
     avg(temperature)
   FROM conditions
   GROUP BY time_3h, location
   ORDER BY time_3h, location;
ERROR:  missing time_bucket_gapfill argument: could not infer start from WHERE clause
HINT:  Specify start and finish as arguments or in the WHERE clause.

NULL の代わりに値を補うこともできます。それには、locf または interpolate 関数を使います。

locf (last observation carried forward; 最後に観測された値を繰り越す) 関数を使うと、直前の値がそのまま補われます。ここでは、6 時、9 時には直前の 3 時と同じ 19.592度 (以下、小数点第 4 位以下切り捨て) が補われます。

=# SELECT time_bucket_gapfill('3 hours', time) AS time_3h,
     location,
     locf(avg(temperature))
   FROM conditions
   WHERE time >= '2020-02-01'
     AND time < '2020-02-02'
   GROUP BY time_3h, location
   ORDER BY time_3h, location;
       time_3h       | location |        locf
---------------------+----------+--------------------
 2020-02-01 00:00:00 | nagoya   |  19.75833333333333
 2020-02-01 00:00:00 | osaka    |  20.46555555555556
 2020-02-01 00:00:00 | tokyo    | 19.315555555555555
 2020-02-01 03:00:00 | nagoya   | 19.328888888888887
 2020-02-01 03:00:00 | osaka    | 20.167777777777783
 2020-02-01 03:00:00 | tokyo    | 19.592777777777773
 2020-02-01 06:00:00 | nagoya   |  19.87055555555555
 2020-02-01 06:00:00 | osaka    |  20.07277777777777
 2020-02-01 06:00:00 | tokyo    | 19.592777777777773
 2020-02-01 09:00:00 | nagoya   | 19.240555555555563
 2020-02-01 09:00:00 | osaka    |             19.915
 2020-02-01 09:00:00 | tokyo    | 19.592777777777773
 2020-02-01 12:00:00 | nagoya   | 20.018888888888895
 2020-02-01 12:00:00 | osaka    |  19.66388888888888
 2020-02-01 12:00:00 | tokyo    | 19.437777777777782
 2020-02-01 15:00:00 | nagoya   | 19.904444444444444
 2020-02-01 15:00:00 | osaka    |  20.11388888888888
 2020-02-01 15:00:00 | tokyo    | 19.707222222222217
 2020-02-01 18:00:00 | nagoya   | 20.251111111111115
 2020-02-01 18:00:00 | osaka    | 20.023888888888884
 2020-02-01 18:00:00 | tokyo    | 20.571666666666676
 2020-02-01 21:00:00 | nagoya   |  19.91722222222222
 2020-02-01 21:00:00 | osaka    | 20.952777777777772
 2020-02-01 21:00:00 | tokyo    | 19.904444444444447
(24 行)

locf 関数によるデータ補間

interpolate 関数を使うと、直前と直後の値をもとに線形補間によって値が補われます。ここでは、直前の 3 時は 19.592 度、直後の 12 時は19.437 度で、6 時は線形補間によって19.592 + (19.437 – 19.592) / 3 = 19.541 度、9 時も同じように 19.592 + (19.437 – 19.592) × 2 / 3 = 19.489 度が補われます。

=# SELECT time_bucket_gapfill('3 hours', time) AS time_3h,
     location,
     interpolate(avg(temperature))
   FROM conditions
   WHERE time >= '2020-02-01'
     AND time < '2020-02-02'
   GROUP BY time_3h, location
   ORDER BY time_3h, location;
       time_3h       | location |    interpolate
---------------------+----------+--------------------
 2020-02-01 00:00:00 | nagoya   |  19.75833333333333
 2020-02-01 00:00:00 | osaka    |  20.46555555555556
 2020-02-01 00:00:00 | tokyo    | 19.315555555555555
 2020-02-01 03:00:00 | nagoya   | 19.328888888888887
 2020-02-01 03:00:00 | osaka    | 20.167777777777783
 2020-02-01 03:00:00 | tokyo    | 19.592777777777773
 2020-02-01 06:00:00 | nagoya   |  19.87055555555555
 2020-02-01 06:00:00 | osaka    |  20.07277777777777
 2020-02-01 06:00:00 | tokyo    |  19.54111111111111
 2020-02-01 09:00:00 | nagoya   | 19.240555555555563
 2020-02-01 09:00:00 | osaka    |             19.915
 2020-02-01 09:00:00 | tokyo    | 19.489444444444445
 2020-02-01 12:00:00 | nagoya   | 20.018888888888895
 2020-02-01 12:00:00 | osaka    |  19.66388888888888
 2020-02-01 12:00:00 | tokyo    | 19.437777777777782
 2020-02-01 15:00:00 | nagoya   | 19.904444444444444
 2020-02-01 15:00:00 | osaka    |  20.11388888888888
 2020-02-01 15:00:00 | tokyo    | 19.707222222222217
 2020-02-01 18:00:00 | nagoya   | 20.251111111111115
 2020-02-01 18:00:00 | osaka    | 20.023888888888884
 2020-02-01 18:00:00 | tokyo    | 20.571666666666676
 2020-02-01 21:00:00 | nagoya   |  19.91722222222222
 2020-02-01 21:00:00 | osaka    | 20.952777777777772
 2020-02-01 21:00:00 | tokyo    | 19.904444444444447
(24 行)

interpolate 関数によるデータ補間

firstlast 関数

firstlast 関数は別の列をキーに並び替え、最初、最後の値を取得する集約関数です。時間ごとに平均などでなく、素の値を抜き出すのに使います。並び替えにインデックスは使えないので、実行に時間がかかる場合があります。

例えば、2020 年 3 月 1 日、東京の温度のデータを 3 時間ごとに最初の値を取得してみます。

=# SELECT time_bucket('3 hours', time) AS time_3h,
     first(temperature, time)
   FROM conditions
   WHERE time >= '2020-03-01'
     AND time < '2020-03-02'
     AND location = 'tokyo'
   GROUP BY time_3h
   ORDER BY time_3h;
       time_3h       | first
---------------------+-------
 2020-03-01 00:00:00 |  24.4
 2020-03-01 03:00:00 |  18.9
 2020-03-01 06:00:00 |  25.1
 2020-03-01 09:00:00 |  22.8
 2020-03-01 12:00:00 |  25.8
 2020-03-01 15:00:00 |  23.1
 2020-03-01 18:00:00 |  11.6
 2020-03-01 21:00:00 |  21.3
(8 行)

histogram 関数

histogram 関数はヒストグラムを取得する集約関数です。データの度数分布を確認するのに使います。

例えば、2020 年 3 月 1 日、東京の温度のデータを 3 時間ごとに 12.5 度から 27.5 度まで 2.5 度刻みで 6 つに区切ったヒストグラムを取得してみます。

=# SELECT time_bucket('3 hours', time) AS time_3h,
     histogram(temperature, 12.5, 27.5, 5)
   FROM conditions
   WHERE time >= '2020-03-01'
     AND time < '2020-03-02'
     AND location = 'tokyo'
   GROUP BY time_3h
   ORDER BY time_3h;
       time_3h       |         histogram
---------------------+---------------------------
 2020-03-01 00:00:00 | {23,16,19,21,29,19,27,26}
 2020-03-01 03:00:00 | {21,17,21,25,25,21,29,21}
 2020-03-01 06:00:00 | {20,29,14,25,22,24,27,19}
 2020-03-01 09:00:00 | {17,24,19,27,18,28,28,19}
 2020-03-01 12:00:00 | {20,17,25,22,31,22,21,22}
 2020-03-01 15:00:00 | {28,23,17,29,25,18,18,22}
 2020-03-01 18:00:00 | {23,14,27,18,30,22,25,21}
 2020-03-01 21:00:00 | {20,20,20,32,19,27,25,17}
(8 行)

ヒストグラムは波カッコ {} で囲まれ、カンマ , で区切られた配列として出力されます。配列の各要素が区間内の度数を表します。ここでは、最初の要素が 12.5 度未満、次が 12.5 度以上、15 度未満、その次が 15 度以上、17.5 度未満と続いていき、最後が 27.5 超過のデータの度数になります。

approximate_row_count 関数

approximate_row_count 関数は統計情報を使ってテーブルのおおよその行数を取得する関数です。結果が正確でない代わりに、count 関数に比べて高速に動作します。

\timing コマンドで実行時間を比べると、approximate_row_count 関数が高速なのが分かります。

=# \timing
タイミングは on です。
=# SELECT count(*) FROM conditions;
  count
---------
 1455480
(1 行)

時間: 246.751 ミリ秒
=# SELECT approximate_row_count('conditions');
 approximate_row_count
-----------------------
               1455840
(1 行)

時間: 4.144 ミリ秒

おわりに

今回は、TimescaleDB の分析関数として、時間を任意の間隔で丸める time_bucket 関数や、別の列をキーに並び替え、最初、最後の値を取得する firstlast 関数を取り上げ、関数の概要や使い方について紹介しました。関数については TimescaleDB DocumentationAPI Reference もあわせて参照してください。

次回は、時系列データの自動集計や削除、圧縮の機能について紹介する予定です。