y_uti のブログ

統計、機械学習、自然言語処理などに興味を持つエンジニアの技術ブログです

Temporal Database 入門 (社内勉強会発表資料)

勤め先の社内勉強会で temporal database について紹介しました。発表資料を公開します。

Temporal Database 入門 by @y-uti

Temporal database とは、時間の経過にともなって変化する情報を格納するデータベースです。Wikipedia 英語版に説明があります。
en.wikipedia.org

Wikipedia のページ内にある例を借りて説明すると、たとえば John がある期間に住んでいた場所を以下のテーブルで表現しようとします。John は 1975 年 4 月 3 日から Smallville に住んでいて、1994 年 8 月 26 日に Bigtown に引っ越したという具合です。valid_to はその日付を含まないこととします。

name address valid_from valid_to
John Smallville 1975-04-03 1994-08-26
John Bigtown 1994-08-26 2001-04-01

ここで発生する問題は、valid_from と valid_to の組が在住期間を意味しているという制約を表現できないことです。このテーブルには以下のように行を追加できてしまい、John は 1995 年 6 月 1 日から 2000 年 9 月 2 日まで Bigtown と Beachy の両方に住んでいたことになってしまいます。

name address valid_from valid_to
John Smallville 1975-04-03 1994-08-26
John Bigtown 1994-08-26 2001-04-01
John Beachy 1995-06-01 2000-09-03

SQL 標準の SQL:2011 では、リレーショナルデータベースで temporal database を扱うための言語拡張が行われています。SQL:2011 では以下のように記述することで、valid_from と valid_to の組が期間 (valid_period) を表すことを指定できます。

CREATE TABLE person (
  name varchar(10),
  address varchar(10),
  valid_from date,
  valid_to date,
  PERIOD FOR valid_period (valid_from, valid_to),
  PRIMARY KEY (name, valid_period WITHOUT OVERLAPS)
);

さらに、以下のようなクエリでテーブルを更新できます。このクエリでは、John は 1995 年 6 月 1 日から 2000 年 9 月 2 日まで Beachy に住んでいたという情報を与えています。

UPDATE person
  FOR PORTION OF valid_period FROM '1995-06-01' TO '2000-09-03'
  SET address = 'Beachy'
  WHERE name = 'John';

最初に示したテーブルに対してこのクエリを発行すると、John が Bigtown に住んでいた期間のうち 1995 年 6 月 1 日から 2000 年 9 月 2 日の範囲のみが更新され、テーブルは以下のようになります。便利ですね。

name address valid_from valid_to
John Smallville 1975-04-03 1994-08-26
John Bigtown 1994-08-26 1995-06-01
John Beachy 1995-06-01 2000-09-03
John Bigtown 2000-09-03 2001-04-01

勉強会では、リレーショナルデータベースで時間情報を扱う方法として、MariaDB の Temporal Tables と PostgreSQL の範囲型を紹介しました。MariaDB では SQL:2011 に沿った文法で temporal database を扱えます。PostgreSQL は SQL:2011 をサポートしていませんが、範囲型を使って時間範囲に関する制約などを記述できます。

なお、勉強会の発表資料では MariaDB の timestamp 型の最大値を 2038-01-19 としていますが、MariaDB 11.5 以降では 2106-02-07 まで扱えるようになっています。
mariadb.com