16.20 - FROM句(ANSI System-Timeテーブル フォーム) - Teradata Vantage NewSQL Engine

Teradata Vantage™ ANSIテンポラル テーブル サポート

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1186-162K-JPN

目的

system-time次元基準を使用して、SELECT問合わせの対象となるANSI system-timeテーブルの行を決めます。

構文



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_1point_in_time_2
タイムスタンプ式(DATEまたはTIMESTAMP[(n)] [WITH TIME ZONE]値で評価される定数、スカラーUDF、スカラー副問合わせ、またはビジネス カレンダ関数など)。
この式は、パラメータ化された値およびCURRENT DATE、CURRENT_TIMESTAMP、TEMPORAL_DATE、またはTEMPORAL_TIMESTAMPなどのビルトイン関数を含めた任意の式にすることができます。この式は列を参照することはできませんが、自己格納型の非相関スカラー副問合わせにすることができます。
BETWEEN ... AND
point_in_time_1point_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_1point_in_time_2で定義される期間と重なるsystem-time期間を持つすべての行を修飾します。
CONTAINED IN
point_in_time_1point_in_time_2の間のsystem-time期間を持つすべての行を修飾します。point_in_time_1以降に始まり、point_in_time_2以前に終わるsystem-time期間。
CONTAINED INは、ANSIに対するTeradata Databaseの拡張機能です。

ANSI準拠

この文はANSI SQL:2011に準拠していますが、非ANSI Teradata Database拡張機能を含みます。

使用上の注意

  • 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テーブルのすべての行が返されました。