ヒストリカル・データの照会

照会は、コマンド行または Web コンソール内の照会エディターを使用して実行できます。

照会の例では、以下の表定義が使用されます。

CREATE TABLE PRODUCT (PRODUCTID INTEGER, DESC VARCHAR (100), PRICE DECIMAL) DATA_VERSION_RETENTION_TIME 30;

以下の行は、異なる時点で挿入されます。挿入のコミット時間 (挿入タイム・スタンプまたは _SYS_START 値) は、SQL コメントに示されます。

INSERT INTO PRODUCT VALUES(1001, 'Jacket', 102.00); -- 2020-10-23 16:00:00
INSERT INTO PRODUCT VALUES(1002, 'Gloves',  20.50); -- 2020-10-23 16:05:00
INSERT INTO PRODUCT VALUES(1003, 'Hat',     18.99); -- 2020-10-23 16:10:00
INSERT INTO PRODUCT VALUES(1004, 'Shoes', 125.25);  -- 2020-10-23 16:15:00

挿入タイム・スタンプと削除タイム・スタンプを含むデータの表示

この SELECT コマンドは、照会が発行された時点で、関連する挿入および削除タイム・スタンプ値を持つ表データを表示します。 _SYS_START および _SYS_END タイム・スタンプはタイム・トラベル照会でのみ使用可能であるため、 AS OF NOW () を使用します。

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF NOW();

例:

SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME AS OF NOW();
PRODUCTID | DESCRIPTION | PRICE   |     _SYS_START      | _SYS_END
----------+-------------+---------+---------------------+-----------
     1001 | Jacket      | 102.00  | 2020-10-23 16:00:00 |
     1002 | Gloves      |  20.50  | 2020-10-23 16:05:00 |
     1003 | Hat         |  18.99  | 2020-10-23 16:10:00 |
     1004 | Shoes       | 125.25  | 2020-10-23 16:15:00 |
(4 rows)

AS OF を使用した特定の時刻のデータの照会

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME AS OF <TIMESTAMP EXPRESSION>;

例:

SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME AS OF '2020-10-23 16:30:00';
PRODUCTID  | DESCRIPTION | PRICE  |     _SYS_START      |      _SYS_END
-----------+-------------+--------+---------------------+---------------------
      1001 | Jacket      | 102.00 | 2020-10-23 16:00:00 |
      1002 | Gloves      |  20.50 | 2020-10-23 16:05:00 |
      1003 | Hat         |  18.99 | 2020-10-23 16:10:00 |
      1004 | Shoes       | 125.25 | 2020-10-23 16:15:00 | 2020-10-23 17:00:00
(4 rows)

この例では、 Shoes の価格は、指定された AS OF タイム・スタンプの後に変更されています。システムは、前の有効な行を返します。

AS OF サブ節も参照してください。

BEFORE を使用した特定の時刻のデータの照会

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BEFORE <TIMESTAMP EXPRESSION>;

例:

SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME BEFORE '2020-10-23 17:00:00';
PRODUCTID  | DESCRIPTION | PRICE  |     _SYS_START      |      _SYS_END
-----------+-------------+--------+---------------------+---------------------
      1001 | Jacket      | 102.00 | 2020-10-23 16:00:00 |
      1002 | Gloves      |  20.50 | 2020-10-23 16:05:00 |
      1003 | Hat         |  18.99 | 2020-10-23 16:10:00 |
      1004 | Shoes       | 125.25 | 2020-10-23 16:15:00 | 2020-10-23 17:00:00
(4 rows)

この例では、 Shoes の価格は、 BEFORE タイム・スタンプの後または BEFORE タイム・スタンプの時点で変更されています。システムは、前の有効な行を返します。

BEFORE サブ節も参照してください。

一定期間にわたるすべての行のデータの照会

FROM を使用して ...TO サブ節

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME FROM <TIMESTAMP EXPRESSION 1> TO “<TIMESTAMP EXPRESSION 2>” WHERE <condition>;

ヒストリカル・データをできるだけさかのぼって照会する場合は、タイム・トラベル照会で RETENTION_START_TIMESTAMP キーワードを使用できます。これを行うと、自分で正しいタイム・スタンプを計算する必要がなくなります。延長すると、値が古すぎる (保存開始タイム・スタンプより古い) ことが判明した場合にエラーが発生するリスクが軽減されます。

例:

SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME FROM RETENTION_START_TIMESTAMP TO '2020-10-23 17:10:00' WHERE PRODUCTID = 1004;
PRODUCTID  | DESCRIPTION | PRICE  |     _SYS_START      |      _SYS_END
-----------+-------------+--------+---------------------+---------------------
      1004 | Shoes       | 125.25 | 2020-10-23 16:15:00 | 2020-10-23 17:00:00
      1004 | Shoes       | 100.00 | 2020-10-23 17:00:00 |
(2 rows)

この例では、照会は、 TO タイム・スタンプを含めずに、指定された期間中に PRODUCTID 1004 に対して行われたすべての変更を検索しました。

FROM ... も参照してください。TO subclause。

BETWEEN を使用して ...AND 副節

SELECT *, _SYS_START, _SYS_END FROM <table_name> FOR SYSTEM_TIME BETWEEN <TIMESTAMP EXPRESSION 1> AND <TIMESTAMP EXPRESSION 2>;

例:

SELECT *, _SYS_START, _SYS_END FROM PRODUCT FOR SYSTEM_TIME BETWEEN '2020-10-23 16:00:00' AND '2020-10-23 17:10:00';
PRODUCTID  | DESCRIPTION | PRICE  |     _SYS_START      |      _SYS_END
-----------+-------------+--------+---------------------+---------------------
      1001 | Jacket      | 102.00 | 2020-10-23 16:00:00 |
      1002 | Gloves      |  20.50 | 2020-10-23 16:05:00 |
      1003 | Hat         |  18.99 | 2020-10-23 16:10:00 |
      1004 | Shoes       | 125.25 | 2020-10-23 16:15:00 | 2020-10-23 17:00:00
      1004 | Shoes       | 100.00 | 2020-10-23 17:00:00 |
(5 rows)

この例では、照会は、 AND タイム・スタンプ (AND タイム・スタンプを含む) までの一定期間に製品表に対して行われたすべての変更を検索しました。

BETWEEN ... も参照してください。AND subclause。

表のリカバリー

BEGIN;
ALTER TABLE <table_name> RENAME TO <new_table_name>;
CREATE TABLE <table_ name> AS
  SELECT * FROM <new_table_name> FOR SYSTEM_TIME <temporal_clause>;
DROP TABLE <new_table_name>; -- or, keep it for diagnostics
COMMIT;

例:

BEGIN;
ALTER TABLE PRODUCT RENAME TO PRODUCT_BAK;
CREATE TABLE PRODUCT AS
  SELECT * FROM FLIGHT_BAK FOR SYSTEM_TIME AS OF '2022-11-01 11:30:00';
DROP TABLE FLIGHT_BAK; -- or, keep it for diagnostics
COMMIT;

この例では、 PRODUCT 表に対して誤った一括変更が行われた疑いがあり、それらを元に戻す必要があるとします。

更新された行の復元

UPDATE <table> SET <col> = <expression> [, <col> = <expression>...]
  FROM (SELECT <col> [, <col> ...] FROM <fromlist> WHERE <condition> FOR SYSTEM_TIME <temporal_clause>) AS <alias>;

例:

UPDATE PRODUCT SET PRICE=P.PRICE
  FROM (SELECT PRICE FROM PRODUCT WHERE PRODUCTID=’1002’ FOR SYSTEM_TIME BEFORE ‘2022-11-01 09:22:41’) AS P;

この例では、商品の価格が誤って更新されたため、復元する必要があります。

タイム・トラベル照会の構文とタイム・スタンプも参照してください。

削除された行の復元

INSERT INTO <table>
  SELECT * FROM <table>
  WHERE <condition> FOR SYSTEM_TIME <temporal_clause>;

例:

INSERT INTO PRODUCT
  SELECT * FROM PRODUCT
  WHERE PRODUCTID=’1004’ FOR SYSTEM_TIME BEFORE ‘2022-11-01 12:45:07’;

この例では、プロダクトが誤って削除され、復元する必要がありました。

タイム・トラベル照会の構文とタイム・スタンプ。