Temporal Database 入門

1.8K Views

December 28, 24

#Temporal Database #PostgreSQL #MariaDB #SQL #データベース

スライド概要

勤め先の社内勉強会での発表資料です。
https://en.wikipedia.org/wiki/Temporal_database に記載されている例を用いて、PostgreSQL の範囲型と MariaDB の Temporal Tables の機能を紹介しました。

profile-image
スライド一覧

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

シェア

またはPlayer版

埋め込む »CMSなどでJSが使えない場合

ダウンロード

ダウンロード(pdf - 1.2MB)

関連スライド

slide-thumbnail

猫でも分かるUnreal Engineの学び方 - 超初心者向け編 - 2023 v1.0
ue4 ue5 ue-beginner
user-img エピック ゲームズ ジャパン 1.4M
slide-thumbnail

Unreal Engine5 Lumenの仕組みと肝心なところ
ue5 ue-rendering ue-lumen
user-img エピック ゲームズ ジャパン 1.2M
slide-thumbnail

最新の6.0で学ぶ！初めてのひとのためのSpring Security
java spring security
user-img tada 1M
slide-thumbnail

猫でも分かる UE5.0, 5.1 におけるアニメーションの新機能について【CEDEC+KYUSHU 2022】
ue5 cedec+kyushu ue-animation ue-optimize ue-bp ue-physics ue-sequencer
user-img エピック ゲームズ ジャパン 874.1K
slide-thumbnail

SQL滅ぶべし
user-img 株式会社ホクソエム 869.2K
slide-thumbnail

SPAセキュリティ超入門
security php javascript
user-img 徳丸 浩 828.5K
各ページのテキスト
1.

Temporal Database 入門 2024-06-20 社内勉強会 内山 雄司

2.

目次 Temporal Database とは PostgreSQL -- 範囲型 MariaDB -- SQL:2011 2024-06-20 TEMPORAL DATABASE 入門 2

3.

Temporal Database とは 2024-06-20 TEMPORAL DATABASE 入門 3

4.

John Doe の生涯 事 実 1975-04-03 Smallville で生まれる 1994-08-26 高校を卒業して Bigtown に引っ越す 1995-06-01 Beachy に引っ越す 届出を忘れていた 記 録 出典 https://en.wikipedia.org/wiki/Temporal_database 2001-04-01 事故に遭い死亡する 2000-09-03 Bigtown に戻る 住民税が高いので隠していた 2001-02-02 税務調査でバレる 1975-04-04 父親が出生届を提出する 2024-06-20 1994-12-27 転居届を提出する TEMPORAL DATABASE 入門 2001-04-01 死亡が確認される 4

5.

Non-temporal database 現在の情報のみを管理する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 CREATE TABLE person ( name varchar PRIMARY KEY, address varchar ); -- 氏名 -- 居住地 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 name 死亡 address (no rows) TEMPORAL DATABASE 入門 5

6.

Non-temporal database 1975-04-04: 父親が John Doe の出生届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 INSERT INTO person VALUES ( 'John Doe', 'Smallville' ); Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address John Doe Smallville TEMPORAL DATABASE 入門 6

7.

Non-temporal database 1994-12-27: John Doe が転居届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 UPDATE person SET address = 'Bigtown' WHERE name = 'John Doe' ; Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address John Doe Bigtown TEMPORAL DATABASE 入門 7

8.

Non-temporal database 2001-02-02: 税務調査によって Beachy に住んでいたことがバレる 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 -- ここでは何も記録することはない --- すでに Bigtown に戻っているので -- John Doe の現在の居住地は Bigtown のままである Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address John Doe Bigtown TEMPORAL DATABASE 入門 8

9.

Non-temporal database 2001-04-01: John Doe の死亡が確認される 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 DELETE FROM person WHERE name = 'John Doe' ; Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 name 死亡 address (no rows) TEMPORAL DATABASE 入門 9

10.

Non-temporal database 現在の情報を問い合わせることができる 日付 1975-04-03 事実 Smallville で出生 記録 name address (no rows) 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 -- John Doe は今どこに住んでいるか SELECT address FROM person WHERE name = 'John Doe'; 死亡 ==> 結果なし 2001-02-02 2001-04-01 2024-06-20 TEMPORAL DATABASE 入門 10

11.

Uni-temporal database 事実 (valid-time) または記録 (transaction-time) のいずれかを管理する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2024-06-20 CREATE TABLE person ( name varchar, address varchar, valid_from date, valid_to date ); name address -- 氏名 -- 居住地 -- 居住開始日 (その日付を含む) -- 居住終了日 (その日付を含まない) valid_from valid_to (no rows) 2001-02-02 2001-04-01 記録 死亡 TEMPORAL DATABASE 入門 11

12.

Uni-temporal database 1975-04-04: 父親が John Doe の出生届を提出する 日付 1975-04-03 事実 Smallville で出生 記録 INSERT INTO person VALUES ('John Doe', 'Smallville', '1975-04-03', NULL); 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 name address valid_from valid_to John Doe Smallville 1975-04-03 NULL 死亡 TEMPORAL DATABASE 入門 12

13.

Uni-temporal database 1994-12-27: John Doe が転居届を提出する 日付 1975-04-03 事実 Smallville で出生 UPDATE person SET valid_to = '1994-08-26' WHERE name = 'John Doe' AND valid_to IS NULL; Bigtown に転居 INSERT INTO person VALUES ('John Doe', 'Bigtown', '1994-08-26', NULL); 1975-04-04 1994-08-26 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 記録 死亡 name address valid_from valid_to John Doe Smallville 1975-04-03 1994-08-26 John Doe Bigtown 1994-08-26 NULL TEMPORAL DATABASE 入門 13

14.

Uni-temporal database 2001-02-02: 税務調査によって Beachy に住んでいたことがバレる 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 記録 UPDATE person SET valid_to = '1995-06-01' WHERE name = 'John Doe' AND valid_to IS NULL; INSERT INTO person VALUES ('John Doe', 'Beachy', '1995-06-01', '2000-09-03'), ('John Doe', 'Bigtown', '2000-09-03', NULL); name address valid_from valid_to John Doe Smallville 1975-04-03 1994-08-26 John Doe Bigtown 1994-08-26 1995-06-01 John Doe Beachy 1995-06-01 2000-09-03 John Doe Bigtown 2000-09-03 NULL TEMPORAL DATABASE 入門 14

15.

Uni-temporal database 2001-04-01: John Doe の死亡が確認される 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 UPDATE person SET valid_to = '2001-04-01' WHERE name = 'John Doe' AND valid_to IS NULL; Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address valid_from valid_to John Doe Smallville 1975-04-03 1994-08-26 John Doe Bigtown 1994-08-26 1995-06-01 John Doe Beachy 1995-06-01 2000-09-03 John Doe Bigtown 2000-09-03 2001-04-01 TEMPORAL DATABASE 入門 15

16.

Uni-temporal database 過去から現在に至る事実を問い合わせることができる 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 死亡 記録 name address valid_from valid_to John Doe Smallville 1975-04-03 1994-08-26 John Doe Bigtown 1994-08-26 1995-06-01 John Doe Beachy 1995-06-01 2000-09-03 John Doe Bigtown 2000-09-03 2001-04-01 -- John Doe は 2000-01-01 には, どこに住んでいたか SELECT address FROM person WHERE name = 'John Doe' AND valid_from <= '2000-01-01' AND COALESCE('2000-01-01' < valid_to, TRUE); ==> Beachy 2024-06-20 TEMPORAL DATABASE 入門 16

17.

Bi-temporal database 事実 (valid-time) と記録 (transaction-time) の両方を管理する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2024-06-20 CREATE TABLE person ( name varchar, address varchar, valid_from date, valid_to date, transaction_from date, transaction_to date); -- 氏名 -- 居住地 -- 居住開始日 (日付を含む) -- 居住終了日 (日付を含まない) -- 登録日 (日付を含む) -- 登録抹消日 (日付を含まない) name v_to address v_from tr_from tr_to (no rows) 2001-02-02 2001-04-01 記録 死亡 TEMPORAL DATABASE 入門 17

18.

Bi-temporal database 1975-04-04: 父親が John Doe の出生届を提出する 日付 1975-04-03 事実 Smallville で出生 記録 INSERT INTO person VALUES ('JD', 'Sml.', '1975-04-03', NULL, '1975-04-04', NULL); 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 name address v_from v_to tr_from tr_to JD Sml. 75-04-03 NULL 75-04-04 NULL 死亡 TEMPORAL DATABASE 入門 18

19.

Bi-temporal database 1994-12-27: John Doe が転居届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 記録 UPDATE person -- 現在の情報を無効にする (抹消) SET transaction_to = '1994-12-27' WHERE name = 'JD' AND valid_to IS NULL AND transaction_to IS NULL; 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 name address v_from v_to tr_from tr_to JD Sml. 75-04-03 NULL 75-04-04 94-12-27 死亡 TEMPORAL DATABASE 入門 19

20.

Bi-temporal database 1994-12-27: John Doe が転居届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 記録 INSERT INTO person VALUES -- 新しい情報を登録する ('JD', 'Sml.', '1975-04-03', '1994-08-26', '1994-12-27', NULL), ('JD', 'Big.', '1994-08-26', NULL, '1994-12-27', NULL); 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address v_from v_to tr_from tr_to JD Sml. 75-04-03 NULL 75-04-04 94-12-27 JD Sml. 75-04-03 94-08-26 94-12-27 NULL JD Big. 94-08-26 NULL 94-12-27 NULL TEMPORAL DATABASE 入門 20

21.

Bi-temporal database 2001-02-02: 税務調査によって Beachy に住んでいたことがバレる 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 記録 UPDATE person -- 現在の情報を無効にする (抹消) SET transaction_to = '2001-02-02' WHERE name = 'JD' AND valid_to IS NULL AND transaction_to IS NULL; 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address v_from v_to tr_from tr_to JD Sml. 75-04-03 NULL 75-04-04 94-12-27 JD Sml. 75-04-03 94-08-26 94-12-27 NULL JD Big. 94-08-26 NULL 94-12-27 01-02-02 TEMPORAL DATABASE 入門 21

22.

Bi-temporal database 2001-02-02: 税務調査によって Beachy に住んでいたことがバレる 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 記録 INSERT INTO person VALUES -- 新しい情報を登録する ('JD', 'Big.', '1994-08-26', '1995-06-01', '2001-02-02', NULL), ('JD', 'Beachy', '1995-06-01', '2000-09-03', '2001-02-02', NULL), ('JD', 'Big.', '2000-09-03', NULL, '2001-02-02', NULL); name address v_from v_to tr_from tr_to JD Sml. 75-04-03 NULL 75-04-04 94-12-27 JD Sml. 75-04-03 94-08-26 94-12-27 NULL JD Big. 94-08-26 NULL 94-12-27 01-02-02 JD Big. 94-08-26 95-06-01 01-02-02 NULL JD Beachy 95-06-01 00-09-03 01-02-02 NULL JD Big. 00-09-03 NULL 01-02-02 NULL TEMPORAL DATABASE 入門 22

23.

Bi-temporal database 2001-04-01: John Doe の死亡が確認される 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 記録 UPDATE person -- 現在の情報を無効にする (抹消) SET transaction_to = '2001-04-01' WHERE name = 'JD' AND valid_to IS NULL AND transaction_to IS NULL; 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address v_from v_to tr_from tr_to JD Sml. 75-04-03 NULL 75-04-04 94-12-27 JD Sml. 75-04-03 94-08-26 94-12-27 NULL JD Big. 94-08-26 NULL 94-12-27 01-02-02 JD Big. 94-08-26 95-06-01 01-02-02 NULL JD Beachy 95-06-01 00-09-03 01-02-02 NULL JD Big. 00-09-03 NULL 01-02-02 01-04-01 TEMPORAL DATABASE 入門 23

24.

Bi-temporal database 2001-04-01: John Doe の死亡が確認される 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 INSERT INTO person VALUES -- 新しい情報を登録する ('JD', 'Big.', '2000-09-03', '2001-04-01', '2001-04-01', NULL); Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address v_from v_to tr_from tr_to JD Sml. 75-04-03 NULL 75-04-04 94-12-27 JD Sml. 75-04-03 94-08-26 94-12-27 NULL JD Big. 94-08-26 NULL 94-12-27 01-02-02 JD Big. 94-08-26 95-06-01 01-02-02 NULL JD Beachy 95-06-01 00-09-03 01-02-02 NULL JD Big. 00-09-03 NULL 01-02-02 01-04-01 JD Big. 00-09-03 01-04-01 01-04-01 NULL TEMPORAL DATABASE 入門 24

25.
[beta] 
Bi-temporal database
過去の任意の時点でのデータベースの状態を問い合わせ (または復元) できる
日付
1975-04-03

事実
Smallville で出生

1975-04-04
1994-08-26

Bigtown に転居

1994-12-27

記録

name

address

v_from

v_to

tr_from

tr_to

JD

Sml.

75-04-03

NULL

75-04-04

94-12-27

JD

Sml.

75-04-03

94-08-26

94-12-27

NULL

JD

Big.

94-08-26

NULL

94-12-27

01-02-02

JD

Big.

94-08-26

95-06-01

01-02-02

NULL

1995-06-01

Beachy に転居

JD

Beachy

95-06-01

00-09-03

01-02-02

NULL

2000-09-03

Bigtown に転居

JD

Big.

00-09-03

NULL

01-02-02

01-04-01

JD

Big.

00-09-03

01-04-01

01-04-01

NULL

2001-02-02
2001-04-01

死亡

-- 2001-01-01 時点の登録情報に基づくと, John Doe は 2000-01-01 には, どこに住んでいることになっていたか
SELECT address FROM person
WHERE transaction_from <= '2001-01-01' AND COALESCE('2001-01-01' < transaction_to, TRUE)
AND name = 'JD' AND valid_from <= '2000-01-01' AND COALESCE('2000-01-01' < valid_to, TRUE);
==> Big.
2024-06-20

TEMPORAL DATABASE 入門

25
26.
[beta] 
Bi-temporal database
過去の任意の時点でのデータベースの状態を問い合わせ (復元) できる
日付
1975-04-03

事実
Smallville で出生

1975-04-04
1994-08-26

Bigtown に転居

1994-12-27

記録

name

address

v_from

v_to

tr_from

tr_to

JD

Sml.

75-04-03

NULL

75-04-04

94-12-27

JD

Sml.

75-04-03

94-08-26

94-12-27

NULL

JD

Big.

94-08-26

NULL

94-12-27

01-02-02

JD

Big.

94-08-26

95-06-01

01-02-02

NULL

未登録

抹消済

1995-06-01

Beachy に転居

JD

Beachy

95-06-01

00-09-03

01-02-02

NULL

未登録

2000-09-03

Bigtown に転居

JD

Big.

00-09-03

NULL

01-02-02

01-04-01

未登録

JD

Big.

00-09-03

01-04-01

01-04-01

NULL

未登録

2001-02-02
2001-04-01

死亡

-- 2001-01-01 時点の登録情報に基づくと, John Doe は 2000-01-01 には, どこに住んでいることになっていたか
SELECT address FROM person
WHERE transaction_from <= '2001-01-01' AND COALESCE('2001-01-01' < transaction_to, TRUE)
AND name = 'JD' AND valid_from <= '2000-01-01' AND COALESCE('2000-01-01' < valid_to, TRUE);
==> Big.
2024-06-20

TEMPORAL DATABASE 入門

26
27.

Tri-temporal database (省略) 事実 (valid-time) 記録 (transaction-time) 決定 (decision-time) の 3 種類の情報を管理する 2024-06-20 TEMPORAL DATABASE 入門 27

28.

PostgreSQL -- 範囲型 2024-06-20 TEMPORAL DATABASE 入門 28

29.

PostgreSQL の範囲型 https://www.postgresql.jp/document/16/html/rangetypes.html 値の範囲を表すデータ型 ◦ 両端の値について「その値を含む」か「その値を含まない」かを表現できる ◦ 便利な演算 (範囲の重なり "&&" や包含 "@>"、隣接 "-|-" など) や関数が定義されている ◦ GiST インデックスで制約を表現できる PostgreSQL 16 で定義されている範囲型 (他の範囲型もユーザ定義可能) ◦ 数値の範囲 ◦ 日時の範囲 2024-06-20 int4range, int8range, numrange tsrange, tstzrange, daterange TEMPORAL DATABASE 入門 29

30.

Bi-temporal database (範囲型) 事実 (valid-time) と記録 (transaction-time) の両方を管理する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 死亡 記録 CREATE EXTENSION btree_gist; CREATE TABLE person ( name varchar, address varchar, valid_range daterange, transaction_range daterange, EXCLUDE USING gist ( name WITH =, valid_range WITH &&, transaction_range WITH &&) ); name address valid_range -- 排他制約を定義できる transaction_range (no rows) 2024-06-20 TEMPORAL DATABASE 入門 30

31.

Bi-temporal database (範囲型) 1975-04-04: 父親が John Doe の出生届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 記録 INSERT INTO person VALUES ( 'JD', 'Sml.', daterange('1975-04-03', NULL, '[)'), '[1975-04-04,)' ); -- '[)' は省略可能 -- 簡単な書き方 name address valid_range transaction_range JD Sml. [75-04-03, NULL) [75-04-04, NULL) 死亡 TEMPORAL DATABASE 入門 31

32.

Bi-temporal database (範囲型) 1994-12-27: John Doe が転居届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 記録 UPDATE person -- 現在の情報を無効にする (抹消) SET transaction_range = daterange(lower(transaction_range), '1994-12-27') WHERE name = 'JD' AND upper_inf(valid_range) AND upper_inf(transaction_range); name address valid_range transaction_range JD Sml. [75-04-03, NULL) [75-04-04, 94-12-27) 死亡 TEMPORAL DATABASE 入門 32

33.

Bi-temporal database (範囲型) 1994-12-27: John Doe が転居届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 記録 INSERT INTO person VALUES -- 新しい情報を登録する ('JD', 'Sml.', '[1975-04-03,1994-08-26)', '[1994-12-27,)'), ('JD', 'Big.', '[1994-08-26,)', '[1994-12-27,)'); 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address valid_range transaction_range JD Sml. [75-04-03, NULL) [75-04-04, 94-12-27) JD Sml. [75-04-03, 94-08-26) [94-12-27, NULL) JD Big. [94-08-26, NULL) [94-12-27, NULL) TEMPORAL DATABASE 入門 33

34.

Bi-temporal database (範囲型) 2001-02-02: 税務調査によって Beachy に住んでいたことがバレる 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 記録 UPDATE person -- 現在の情報を無効にする (抹消) SET transaction_range = daterange(lower(transaction_range), '2001-02-02') WHERE name = 'JD' AND upper_inf(valid_range) AND upper_inf(transaction_range); name address valid_range transaction_range JD Sml. [75-04-03, NULL) [75-04-04, 94-12-27) JD Sml. [75-04-03, 94-08-26) [94-12-27, NULL) JD Big. [94-08-26, NULL) [94-12-27, 01-02-02) TEMPORAL DATABASE 入門 34

35.

Bi-temporal database (範囲型) 2001-02-02: 税務調査によって Beachy に住んでいたことがバレる 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 記録 INSERT INTO person VALUES -- 新しい情報を登録する ('JD', 'Big.', '[1994-08-26,1995-06-01)', '[2001-02-02,)') ('JD', 'Beachy', '[1995-06-01,2000-09-03)', '[2001-02-02,)'), ('JD', 'Big. ', '[2000-09-03,)', '[2001-02-02,)'); name address valid_range transaction_range JD Sml. [75-04-03, NULL) [75-04-04, 94-12-27) JD Sml. [75-04-03, 94-08-26) [94-12-27, NULL) JD Big. [94-08-26, NULL) [94-12-27, 01-02-02) JD Big. [94-08-26, 95-06-01) [01-02-02, NULL) JD Beachy [95-06-01, 00-09-03) [01-02-02, NULL) JD Big. [00-09-03, NULL) [01-02-02, NULL) TEMPORAL DATABASE 入門 35

36.

Bi-temporal database (範囲型) 2001-04-01: John Doe の死亡が確認される 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 記録 UPDATE person -- 現在の情報を無効にする (抹消) SET transaction_range = daterange(lower(transaction_range), '2001-04-01') WHERE name = 'JD' AND upper_inf(valid_range) AND upper_inf(transaction_range); name address valid_range transaction_range JD Sml. [75-04-03, NULL) [75-04-04, 94-12-27) JD Sml. [75-04-03, 94-08-26) [94-12-27, NULL) JD Big. [94-08-26, NULL) [94-12-27, 01-02-02) JD Big. [94-08-26, 95-06-01) [01-02-02, NULL) JD Beachy [95-06-01, 00-09-03) [01-02-02, NULL) JD Big. [00-09-03, NULL) [01-02-02, 01-04-01) TEMPORAL DATABASE 入門 36

37.

Bi-temporal database (範囲型) 2001-04-01: John Doe の死亡が確認される 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 INSERT INTO person VALUES -- 新しい情報を登録する ('JD', 'Big.', '[2000-09-03,2001-04-01)', '[2001-04-01,)'); Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address valid_range transaction_range JD Sml. [75-04-03, NULL) [75-04-04, 94-12-27) JD Sml. [75-04-03, 94-08-26) [94-12-27, NULL) JD Big. [94-08-26, NULL) [94-12-27, 01-02-02) JD Big. [94-08-26, 95-06-01) [01-02-02, NULL) JD Beachy [95-06-01, 00-09-03) [01-02-02, NULL) JD Big. [00-09-03, NULL) [01-02-02, 01-04-01) JD Big. [00-09-03, 01-04-01) [01-04-01, NULL) TEMPORAL DATABASE 入門 37

38.
[beta] 
Bi-temporal database (範囲型)
過去の任意の時点でのデータベースの状態を問い合わせ (または復元) できる
日付
1975-04-03

事実
Smallville で出生

1975-04-04
1994-08-26

Bigtown に転居

1994-12-27

記録

name

address

valid_range

transaction_range

JD

Sml.

[75-04-03, NULL)

[75-04-04, 94-12-27)

JD

Sml.

[75-04-03, 94-08-26)

[94-12-27, NULL)

JD

Big.

[94-08-26, NULL)

[94-12-27, 01-02-02)

JD

Big.

[94-08-26, 95-06-01)

[01-02-02, NULL)

1995-06-01

Beachy に転居

JD

Beachy

[95-06-01, 00-09-03)

[01-02-02, NULL)

2000-09-03

Bigtown に転居

JD

Big.

[00-09-03, NULL)

[01-02-02, 01-04-01)

JD

Big.

[00-09-03, 01-04-01)

[01-04-01, NULL)

2001-02-02
2001-04-01

死亡

-- 2001-01-01 時点の登録情報に基づくと, John Doe は 2000-01-01 には, どこに住んでいることになっていたか
SELECT address FROM person
WHERE transaction_range @> '2001-01-01'::date
AND name = 'JD' AND valid_range @> '2000-01-01'::date;
==> Big.
2024-06-20

TEMPORAL DATABASE 入門

38
39.

MariaDB -- SQL:2011 2024-06-20 TEMPORAL DATABASE 入門 39

40.

SQL:2011 撤回 (withdrawn) 済み。現行の SQL 標準は SQL:2023 2011 年に発行された SQL 標準 ◦ Temporal tables の作成と操作に関する標準が取り込まれた 資料 ◦ SQL 標準 ◦ ISO/IEC 9075-1:2011 https://www.iso.org/standard/53681.html ◦ ISO/IEC 9075-1:2023 https://www.iso.org/standard/76583.html ◦ ACM SIGMOD Record ◦ Temporal features in SQL:2011 https://sigmodrecord.org/2012/09/30/temporal-features-in-sql2011/ ◦ What's new in SQL:2011 https://sigmodrecord.org/2012/03/08/whats-new-in-sql2011/ ◦ Wikipedia ◦ SQL:2011 2024-06-20 https://en.wikipedia.org/wiki/SQL:2011 TEMPORAL DATABASE 入門 40

41.

MariaDB の temporal tables https://mariadb.com/kb/en/temporal-tables/ SQL:2011 の temporal features の一部をサポート + パーティショニング等の拡張 ◦ Application-time period tables ◦ テーブル作成 PERIOD FOR ◦ 更新・削除 UPDATE … FOR PORTION OF / DELETE … FOR PORTION OF ◦ 制約 PRIMARY KEY … WITHOUT OVERLAPS / UNIQUE … WITHOUT OVERLAPS ◦ テーブル作成 PERIOD FOR SYSTEM_TIME および WITH SYSTEM VERSIONING ◦ System-versioned tables ◦ クエリ SELECT … FOR SYSTEM_TIME ◦ Bitemporal tables ◦ これらの組み合わせ 2024-06-20 TEMPORAL DATABASE 入門 41

42.

Uni-temporal database (SQL:2011) 事実 (valid-time) または記録 (transaction-time) のいずれかを管理する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 記録 CREATE OR REPLACE 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) ); name 死亡 address valid_from valid_to (no rows) valid_period 2024-06-20 TEMPORAL DATABASE 入門 42

43.

Uni-temporal database (SQL:2011) 1975-04-04: 父親が John Doe の出生届を提出する 日付 1975-04-03 事実 Smallville で出生 記録 INSERT INTO person VALUES ('John Doe', 'Smallville', '1975-04-03', '9999-12-31'); 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 name address valid_from valid_to John Doe Smallville 1975-04-03 9999-12-31 死亡 TEMPORAL DATABASE 入門 43

44.

Uni-temporal database (SQL:2011) 1994-12-27: John Doe が転居届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 記録 UPDATE person FOR PORTION OF valid_period FROM '1994-08-26' TO '9999-12-31' SET address = 'Bigtown' WHERE name = 'John Doe'; 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address valid_from valid_to John Doe Smallville 1975-04-03 1994-08-26 John Doe Bigtown 1994-08-26 9999-12-31 TEMPORAL DATABASE 入門 44

45.

Uni-temporal database (SQL:2011) 2001-02-02: 税務調査によって Beachy に住んでいたことがバレる 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 記録 UPDATE person FOR PORTION OF valid_period FROM '1995-06-01' TO '2000-09-03' SET address = 'Beachy' WHERE name = 'John Doe' ; name address valid_from valid_to John Doe Smallville 1975-04-03 1994-08-26 John Doe Bigtown 1994-08-26 1995-06-01 John Doe Beachy 1995-06-01 2000-09-03 John Doe Bigtown 2000-09-03 9999-12-31 TEMPORAL DATABASE 入門 45

46.

Uni-temporal database (SQL:2011) 2001-04-01: John Doe の死亡が確認される 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 DELETE FROM person FOR PORTION OF valid_period FROM '2001-04-01' TO '9999-12-31' WHERE name = 'John Doe'; Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 name address valid_from valid_to JD Smallville 1975-04-03 1994-08-26 JD Bigtown 1994-08-26 1995-06-01 JD Beachy 1995-06-01 2000-09-03 JD Bigtown 2000-09-03 2001-04-01 TEMPORAL DATABASE 入門 46

47.
[beta] 
Uni-temporal database (SQL:2011)
過去から現在に至る事実を問い合わせることができる
日付
1975-04-03

事実
Smallville で出生

1975-04-04
1994-08-26

Bigtown に転居

1994-12-27
1995-06-01

Beachy に転居

2000-09-03

Bigtown に転居

2001-02-02
2001-04-01

死亡

記録

name

address

valid_from

valid_to

JD

Smallville

1975-04-03

1994-08-26

JD

Bigtown

1994-08-26

1995-06-01

JD

Beachy

1995-06-01

2000-09-03

JD

Bigtown

2000-09-03

2001-04-01

-- John Doe は 2000-01-01 には, どこに住んでいたか
SELECT address FROM person
WHERE name = 'John Doe'
AND valid_from <= '2000-01-01'
AND '2000-01-01' < valid_to;
==> Beachy

2024-06-20

TEMPORAL DATABASE 入門

47
48.

Bi-temporal database (SQL:2011) 事実 (valid-time) と記録 (transaction-time) の両方を管理する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 死亡 記録 CREATE OR REPLACE TABLE person ( name varchar(10), address varchar(10), valid_from date, valid_to date, -- transaction_from timestamp(6) GENERATED ALWAYS AS ROW START, -- transaction_to timestamp(6) GENERATED ALWAYS AS ROW END, PERIOD FOR valid_period (valid_from, valid_to), -- PERIOD FOR SYSTEM_TIME (transaction_from, transaction_to), PRIMARY KEY (name, valid_period WITHOUT OVERLAPS) ) WITH SYSTEM VERSIONING; name address v_from v_to ROW_START ROW_END (no rows) ROW_START と ROW_END が追加される (隠しカラム) 2024-06-20 TEMPORAL DATABASE 入門 SYSTEM_TIME 48

49.

Bi-temporal database (SQL:2011) 1975-04-04: 父親が John Doe の出生届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 記録 SET @@timestamp = unix_timestamp('1975-04-04'); INSERT INTO person VALUES ('John Doe', 'Smallville', '1975-04-03', '9999-12-31'); Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 name address v_from v_to R_START R_END JD Sml. 75-04-03 99-12-31 75-04-04 38-01-19 死亡 TEMPORAL DATABASE 入門 49

50.

Bi-temporal database (SQL:2011) 1994-12-27: John Doe が転居届を提出する 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 記録 SET @@timestamp = unix_timestamp('1994-12-27'); UPDATE person FOR PORTION OF valid_period FROM '1994-08-26' TO '9999-12-31' SET address = 'Bigtown' WHERE name = 'John Doe'; name address v_from v_to R_START R_END JD Sml. 75-04-03 99-12-31 75-04-04 94-12-27 JD Sml. 75-04-03 94-08-26 94-12-27 38-01-19 JD Big. 94-08-26 99-12-31 94-12-27 38-01-19 TEMPORAL DATABASE 入門 50

51.

Bi-temporal database (SQL:2011) 2001-02-02: 税務調査によって Beachy に住んでいたことがバレる 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 記録 SET @@timestamp = unix_timestamp('2001-02-02'); UPDATE person FOR PORTION OF valid_period FROM '1995-06-01' TO '2000-09-03' SET address = 'Beachy' WHERE name = 'John Doe'; name address v_from v_to R_START R_END JD Sml. 75-04-03 99-12-31 75-04-04 94-12-27 JD Sml. 75-04-03 94-08-26 94-12-27 38-01-19 JD Big. 94-08-26 99-12-31 94-12-27 01-02-02 JD Big. 94-08-26 95-06-01 01-02-02 38-01-19 JD Beachy 95-06-01 00-09-03 01-02-02 38-01-19 JD Big. 00-09-03 99-12-31 01-02-02 38-01-19 TEMPORAL DATABASE 入門 51

52.

Bi-temporal database (SQL:2011) 2001-04-01: John Doe の死亡が確認される 日付 1975-04-03 事実 Smallville で出生 1975-04-04 1994-08-26 Bigtown に転居 1994-12-27 1995-06-01 Beachy に転居 2000-09-03 Bigtown に転居 2001-02-02 2001-04-01 2024-06-20 死亡 記録 SET @@timestamp = unix_timestamp('2001-04-01'); DELETE FROM person FOR PORTION OF valid_period FROM '2001-04-01' TO '9999-12-31' WHERE name = 'John Doe'; name address v_from v_to R_START R_END JD Sml. 75-04-03 99-12-31 75-04-04 94-12-27 JD Sml. 75-04-03 94-08-26 94-12-27 38-01-19 JD Big. 94-08-26 99-12-31 94-12-27 01-02-02 JD Big. 94-08-26 95-06-01 01-02-02 38-01-19 JD Beachy 95-06-01 00-09-03 01-02-02 38-01-19 JD Big. 00-09-03 99-12-31 01-02-02 01-04-01 JD Big. 00-09-03 01-04-01 01-04-01 38-01-19 TEMPORAL DATABASE 入門 52

53.
[beta] 
Bi-temporal database (SQL:2011)
過去の任意の時点でのデータベースの状態を問い合わせ (または復元) できる
日付
1975-04-03

事実
Smallville で出生

1975-04-04
1994-08-26

Bigtown に転居

1994-12-27

記録

name

address

v_from

v_to

R_START

R_END

JD

Sml.

75-04-03

99-12-31

75-04-04

94-12-27

JD

Sml.

75-04-03

94-08-26

94-12-27

38-01-19

JD

Big.

94-08-26

99-12-31

94-12-27

01-02-02

JD

Big.

94-08-26

95-06-01

01-02-02

38-01-19

1995-06-01

Beachy に転居

JD

Beachy

95-06-01

00-09-03

01-02-02

38-01-19

2000-09-03

Bigtown に転居

JD

Big.

00-09-03

99-12-31

01-02-02

01-04-01

JD

Big.

00-09-03

01-04-01

01-04-01

38-01-19

2001-02-02
2001-04-01

死亡

-- 2001-01-01 時点の登録情報に基づくと, John Doe は 2000-01-01 には, どこに住んでいることになっていたか
SELECT address FROM person FOR SYSTEM_TIME AS OF '2001-01-01'
WHERE name = 'John Doe'
AND valid_from <= '2000-01-01' AND '2000-01-01' < valid_to;
==> Big.
2024-06-20

TEMPORAL DATABASE 入門

53
54.

まとめ & 補足 話したこと ◦ Temporal database とは ◦ PostgreSQL の範囲型 ◦ MariaDB の SQL:2011 対応 話さなかったこと (まだ調べていないこと) ◦ PostgreSQL の SQL:2011 対応状況 ◦ https://wiki.postgresql.org/wiki/Temporal_Extensions ◦ 性能 (速度) 検証 ◦ 外部キー制約 2024-06-20 TEMPORAL DATABASE 入門 54

55.

おわり (質疑応答) 2024-06-20 TEMPORAL DATABASE 入門 55