目的
system-time次元基準を使用して、SELECT問合わせの対象となるANSI system-timeテーブルの行を決めます。
構文
FROM system_time_table [ FOR SYSTEM TIME { AS OF point_in_time | BETWEEN point_in_time_1 AND point_in_time_2 | FROM point_in_time_1 TO point_in_time_2 | CONTAINED IN ( point_in_time_1, point_in_time_2 ) } ]
- system_time_table
- 問合わせ対象のsystem-timeテーブル。このテーブルは、テンポラル構文の対象となるシステムのバージョン情報を含むテーブルでなければなりません。
- AS OF
- 所定の時点でオープンだったsystem-timeテーブルの行を修飾します。AS OF修飾子を使用して、AS OF時点に存在したテーブルに問合わせを実行します。返される行は、問合わせにより指定された時点ではオープン(データベース内で有効)でしたが、問合わせが送信される前にクローズになっている可能性があります。このような行には、9999-12-31 23:59:59.999999+00:00より前の終了境界のタイムスタンプが表示されます。結果のクローズ行は、AS OF時点後に変更または削除された行を示します。
- point_in_time point_in_time_1 and point_in_time_2
- タイムスタンプ式(DATEまたはTIMESTAMP[(n)] [WITH TIME ZONE]値で評価される定数、スカラーUDF、スカラー副問合わせ、またはビジネス カレンダ関数など)。
この式は、パラメータ化された値およびCURRENT DATE、CURRENT_TIMESTAMP、TEMPORAL_DATE、またはTEMPORAL_TIMESTAMPなどのビルトイン関数を含めた任意の式にすることができます。この式は列を参照することはできませんが、自己格納型の非相関スカラー副問合わせにすることができます。
- BETWEEN ... AND
- point_in_time_1とpoint_in_time_2で定義される期間と重なる、またはこれらの期間が直後に続くsystem-time期間を持つすべての行を修飾します。これは、一般に理解されている意味での「between(間)」ではありません。BETWEENはpoint_in_time_1の時点より前に始まるsystem-time期間の行、およびpoint_in_time_2時点の直後に始まり、それを超える行を修飾するからです。一般に理解されているBETWEENの意味を反映する修飾子としては、CONTAINED IN修飾子を使用します。
- FROM ... TO
- point_in_time_1とpoint_in_time_2で定義される期間と重なるsystem-time期間を持つすべての行を修飾します。
- CONTAINED IN
- point_in_time_1とpoint_in_time_2の間のsystem-time期間を持つすべての行を修飾します。point_in_time_1以降に始まり、point_in_time_2以前に終わるsystem-time期間。CONTAINED INは、ANSIに対するTeradataの拡張機能です。
ANSI準拠
この文はANSI SQL:2011に準拠していますが、非ANSI Teradata拡張機能を含みます。
使用上の注意
- FROM句でテンポラル修飾子を使用しない場合、system-timeテーブルのデフォルトは現在(問合わせの時点で)オープンのすべての行を修飾することです。
- BETWEEN ... AND、FROM ...TO、およびCONTAINED INはすべて期間に従って、テンポラル テーブルの問合わせを修飾します。指定期間中(またはBETWEEN ... ANDの場合、指定期間の直後)の任意の時点でデータベースで有効、システム時間でオープンだった任意のテーブルの行が問合わせ用に修飾されます。3つのテンポラル修飾子の違いはわずかですが、行の正確なセットを修飾する問合わせを定義できます。
FROM句の例
以下の例では、オープン行とクローズ行の混合を含むemployee_systimeという名前の次のシステムのバージョン情報を含むsystem-timeテーブルに対して問合わせが実行されると仮定しています。
eid ename deptno sys_start sys_end ---- ------ ------ -------------------------------- -------------------------------- 1002 Ash 333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00 1004 Fred 222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00 1001 Sania 111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1003 SRK 111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00 1004 Fred 555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
sys_end日付が9999-12-31 23:59:59.999999+00:00でない行は、システム時間でクローズしています。これらの行は最初にテーブルに追加されましたが、テーブルの以前の状態の永続的な記録としてテーブルに残っているため、テーブルから論理的に削除されているか、変更されています。これらの行はUPDATEやDELETEなどのほとんどのSQL操作に参加できませんが、テンポラル修飾子を使用して、system-timeテーブルから取り出すことができます。
この場合、テーブルはFredとAliceの部門値(deptno列)が2005-05-01で変更されたことを示しています。この日付はFredとAliceのクローズ行の終了日であり、新しい情報を含む新しい行の開始日です。
SRKには、9999-12-31 23:59:59.999999+00:00より前のsys_end日付も表示されますが、SRKのテーブルにはオープン行は残っていないため、sys_end日付は行がテーブルから(論理的に)削除された時を示します。
テンポラル修飾子のない簡単なSELECTは、system-timeテーブルのオープンでアクティブな行のみを表示します。オープン行は終了境界が9999-12-31 23:59:59.999999+00:00のsystem-time期間によって示されます。
SELECT * FROM employee_systime; eid ename deptno sys_start sys_end ---- ------ ------ -------------------------------- -------------------------------- 1002 Ash 333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1001 Sania 111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1004 Fred 555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
これは、今日、問合わせが処理される時点の情報の状態を反映しています。テンポラル修飾子を使用して、過去の任意の時間に存在したテーブルのスナップショットを表示し、テーブルの前の状態に対して真であった結果を返すことができます。
例: ANSI system-timeテーブルでのAS OF問合わせ
次のAS OF問合わせでは、問合わせで指定された時点でオープンで有効であったすべてのテーブルの行を、それらの行が現在オープンかクローズかに関係なく取得します。
SELECT * FROM employee_systime FOR SYSTEM_TIME AS OF TIMESTAMP'2005-01-01 00:00:01.000000-08:00'; eid ename deptno sys_start sys_end ---- ------ ------ -------------------------------- -------------------------------- 1002 Ash 333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1001 Sania 111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1003 SRK 111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00 1004 Fred 222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00 1005 Alice 222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00
sys_end値と違って、これらの行のデータは2005年1月1日に存在したテーブルの状態を反映しています。その時点でFredとAliceはdeptno 222に属し、SRKがまだ採用されていました(SRK行はまだ削除されていませんでした)。
Fred、Alice、およびSRKの行のsys_end値は9999-12-31 23:59:59.999999+00:00より小さいため、この情報が2005年1月1日のもので、最新ではないことがわかります。AS OF日付以降にこれらの行への1つ以上の変更が発生し、これらの変更の最初の時間がsys_end値によって反映されます。9999-12-31 23:59:59.999999+00:00のsys_end日付の行はAS OF日付以降に変更または削除されていません。
テーブルの問合わせAS OF 2005-05-02は、FredとAliceの部門が変わった直後のテーブルの問合わせを反映します。
SELECT * FROM employee_systime FOR SYSTEM_TIME AS OF TIMESTAMP'2005-01-01 00:00:01.000000-08:00'; eid ename deptno sys_start sys_end ---- ------ ------ -------------------------------- -------------------------------- 1002 Ash 333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1001 Sania 111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1003 SRK 111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00 1004 Fred 555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
AS OF問合わせは所定の時点のテーブルの状態を反映するため、返されるクローズ行に発生した変更の性質を示すものではありません。system-timeテーブルの行への変更の性質を判断するには、変更の前後に存在した行を見る必要があります。残りのテンポラル問合わせ修飾子、BETWEEN ... AND、FROM ... TO、およびCONTAINED INを使用して、インスタンスではなく、有効時間の期間を指定できます。指定された期間中に行が変更された場合、古いバージョンと新しいバージョンの両方の行が返されるため、システム時間で行をクローズする原因になった変更の性質を判断することができます。
例: ANSI system-timeテーブルでのBETWEEN ... AND問合わせ
FredとAliceの行への変更の性質を見るには、変更の前後に存在した行を返す問合わせが必要です。変更の時間を測定するBETWEEN ... ANDテンポラル修飾子付きの問合わせを送信できます。AS OF問合わせから変更が2005年5月1日に発生したことがわかっています。
SELECT * FROM employee_systime FOR SYSTEM_TIME BETWEEN TIMESTAMP'2005-04-30 00:00:00.000001-08:00' AND TIMESTAMP'2005-05-02 00:00:00.000001-08:00' WHERE ename='Fred' OR ename='Alice' ORDER BY ename; eid ename deptno sys_start sys_end ---- ------ ------ -------------------------------- -------------------------------- 1005 Alice 222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00 1005 Alice 555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00 1004 Fred 222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00 1004 Fred 555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
この結果から、2004年12月1日から2005年5月1日までAliceが222部門にいたことは明らかです。2005年5月1日から現在まで、Aliceは555部門にいます。同様に、Fredは222部門から始まり、2002年7月1日から2005年5月1日までそこにいました。その後彼の所属部門は555に変わり、今日も555のままです。おそらく、222部門の社員は2005年に部門が変わったか、部門番号が変更された可能性があります。
例: ANSI system-timeテーブルでのFROM ...TO問合わせ
指定される期間が十分に広い場合、時間の期間を測定するテンポラル修飾子を使用して、すべてのオープンおよびクローズ行を含めたsystem-timeテーブルのすべての行を選択することもできます。
SELECT * FROM employee_systime FOR SYSTEM_TIME FROM TIMESTAMP'1900-01-01 00:00:00.000001-08:00' TO CURRENT_TIMESTAMP; eid ename deptno sys_start sys_end ---- ------ ------ -------------------------------- -------------------------------- 1002 Ash 333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00 1004 Fred 222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00 1005 Alice 555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00 1004 Fred 555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00 1001 Sania 111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1003 SRK 111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00
これにより、system-timeテーブルのすべての行が返されました。