16.20 - Sequenced Valid-Time問合わせ - Teradata Vantage NewSQL Engine

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

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

sequencedテンポラル問合わせを使用すると、テンポラル テーブルの過去、現在または将来の状態を抽出できるようになります。有効時間でsequencedになる問合わせは、問合わせの適用期間と重なる有効期間を含む行を対象にします。行にさらに細かいフィルタを掛けるために、必要に応じてvalid-time列に対する追加の条件を指定できます。

有効時間でsequencedになる問合わせは、適用期間に指定した各時点でのテーブルの状態を抽出します。結果のテーブルはvalid-timeテーブルになります。問合わせは1つ以上のvalid-timeテーブルになり、valid-time結果を生成します。

valid-time列にNULLがある行は結果に含まれません。

結果セットにはVALIDTIMEという名前の新しい行が含まれています。この行は、システムによって自動的に追加されます。VALIDTIMEは、問合わせの結果セットに含まれる行の有効時間を示すものです。これは、行に最初に定義されていた有効時間とは異なります。sequenced問合わせでは特定の時間の適用期間を指定するため、その適用期間によって結果の有効期間が制限されます。そのため、結果セットに含まれる各行の有効時間は、問合わせの適用期間と、対象行の有効期間との共通部分になります。

例: sequenced valid-time問合わせ

SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)'
SELECT *
FROM Policy;

Policy_ID Customer_ID Policy_Type Policy_Details      VALIDTIME
--------- ----------- ----------- ------------------- ------------------------
   541077   766492008 AU          STD-CH-344-YXY-00   ('09/12/21', '09/12/31')
   541008   246824626 AU          STD-CH-345-NXY-00   ('09/10/01', '09/12/31')
   541145   616035020 AU          STD-CH-348-YXN-01   ('09/12/03', '09/12/31')

VALIDTIMEは結果セットのvalid-time列ですが、Validityは最初に問合わせの対象になったPolicyテーブルのvalid-time列です。valid-time列名はPAを含む問合わせのどこにも表示できないので、結果にValidity列を表示するにはsubqueryが必要です。PAを指定しないSEQUENCED VALIDTIME subqueryを使用してください。Validity列は派生テーブルのvalid-time列ではないので、PAを含むsequenced外部問合わせを使用して取得できます。

例: 元のvalid-time列を返すsequenced valid-time問合わせ

SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)' 
SELECT Policy_ID, Customer_ID, Validity FROM (
   SEQUENCED VALIDTIME SELECT Policy.*, Validity 
   FROM Policy) AS my_derived_table;

Policy_ID   Customer_ID  Validity                 VALIDTIME
----------- -----------  -----------------------  ------------------------
     541077   766492008  ('09/12/21', '99/12/31') ('09/12/21', '09/12/31')
     541008   246824626  ('09/10/01', '99/12/31') ('09/10/01', '09/12/31')
     541145   616035020  ('09/12/03', '10/12/01') ('09/12/03', '09/12/31')

ValidityとVALIDTIMEの比較によって、結果セットのvalid-time期間が、PolicyテーブルのValidity列のvalid-time期間とsequenced select文に指定したPAとの共通部分であることが証明されます。

この例では、SELECT *アスタリスク表記をテンポラル列の明示的な指定と組み合わせて使用します。アスタリスクの詳細については、選択リストのアスタリスクを参照してください。

SELECT文のWHEREなどの句では、新しいVALIDTIME列を参照できません。また、VALIDTIMEをその他の射影された列の別名として使用することはできません。

ビューや派生テーブルが結果としてvalid-timeテーブルになる場合、そのテーブルは問合わせに指定したその他のvalid-timeテーブルと同様に扱われます。それ以外の場合は、非テンポラル テーブルとして扱われます。

sequenced形式のテンポラル問合わせは、単一のvalid-timeテーブルからの単純SELECT、または複数のテーブルからの内部結合を使用した単純SELECTに制限されています。テンポラル問合わせには、非相関スカラーsubquery が使用できます。

次に示す項目は、sequenced問合わせではサポートされません。
  • 外部結合
  • 集合演算
  • 順序付き分析関数
  • 非相関スカラーsubquery 以外のsubquery
  • WITH, WITH RECURSIVE, TOP n, DISTINCT
sequenced内部結合に適用されるルールは、次のとおりです。
  • sequenced内部結合のコンテキストで言及されているまたはという用語は、有効期間が問合わせの適用期間と重複し、指定された単一テーブル条件を満たすテーブルのすべての行を意味します。
  • 結合された行には、指定した順序の射影された列に加え、適用期間、左の行の有効期間、および右の行の有効期間の交差の結果が設定された値を持つvalid-time列が格納されます。
  • 結合に関与する各テーブルのvalid-time列と適用期間の粒度が同じ場合には、結果としての有効期間の粒度は適用期間の粒度と同じになります。それ以外の場合は、結果の有効期間の粒度がそれらの中で最も高い粒度になり、結合を実行する前に各valid-time列は暗黙的に最も高いvalid-time粒度に変換されます。

    たとえば、適用期間がPERIOD(DATE)のsequenced結合、PERIOD(TIMESTAMP(3))のvalid-time列を含むテーブル、およびPERIOD(TIMESTAMP(5))のvalid-time列を含むテーブルについて考えてみます。この結果としてのVALIDTIME列のデータ型はPERIOD(TIMESTAMP(5))になります。

    通常、粒度が異なっても問題は発生しません。ただし、いくつかのタイプの射影列では、混乱が発生する可能性があります。例えば、元のテーブルで1日ごとのチャージを表現する列が、時間単位の有効性を持つ結果に射影されている場合などに、混乱が発生する可能性があります。そのような状況は、粒度が似ているテーブルを結合したり、データ モデルで結合対象のテーブルに同じ粒度を指定しておいたりすると回避できます。

    同様に、非テンポラル テーブルがテンポラル テーブルと結合する場合、結果の行はテンポラル テーブルの有効性を反映します。この有効性は、元来はテンポラルの性質を持たないデータに適用された場合、誤解を生む可能性があります。

  • sequenced内部結合では、行の有効期間が重なるときにのみ、左の行が右のテーブルの一致する行と結合されます。

ORDER BY句を指定したsequenced問合わせでは、結果のVALIDTIME列の順序付けを指定できます。この列をORDER BY句に指定するには、VALIDTIMEキーワードを使用するか、その列の名前を二重引用符で区切って("VALIDTIME")使用します。順序付け句でVALIDTIMEを指定しないと、結果のVALIDTIME列は、デフォルトのASC (昇順)順序付けのリストの最後の構成要素として、自動的にORDER BYリストに組み込まれます。

問合わせに二重テンポラル テーブルまたはtransaction-timeテーブルが関与する場合は、transaction-time次元に適用される追加情報に関する以下のトピックを参照してください。

Sequenced問合わせでの集約関数

sequenced valid-time問合わせの結果は、VALIDTIME列を含む行があるテンポラル テーブルです。VALIDTIME列は、結果セットの行に対して、有効時間、つまり行情報が有効な時間を示します。この結果セットの有効時間は、最初に問合わせたテーブルの対象行の有効期間を持つ問合わせの適用期間の共通部分になります。

sequenced valid-time問合わせに、集約関数を含めることができます。集約は、結果セットのVALIDTIME期間の組み合わせにより定義される、すべての固有の継続期間で実行されます。

SEQUENCED TRANSACTIONTIME集約はサポートされません。

使用上の注意

valid-timeテーブルは、指定された期間、つまり行の有効時間について、有効だと見なされる状態の情報を表現します。有効なデータは、その期間中を通じた定数であるか、または、その有効期間中に集積する値を表現するものとすることが可能です。状態の情報の例には、次のものがあります。

  • 指定された期間での在庫品目の数量。
  • 特定の期間で有効な保険契約。
  • あるプロジェクトでの1日当たりの労働時間など、単位時間当たりの労働時間数。ここで、有効時間は、PERIOD (DATE)データ型を使用し、期間全体の単位で表現します。

テンポラル テーブルで集約が適用される列を注意深く評価して選択することが重要です。一定期間の集約が非状態列に適用される場合、誤解を生む、不正確な結果になる可能性があります。

  • valid-timeテンポラル テーブルで使用する場合、SUM、AVG、MIN、MAXなどの集約は、状態列でのみ実行する必要があります。非状態列でそのような集約関数を使用すると、意味のない結果や、誤解を生む結果が生じる可能性があります。
  • COUNT集約は、通常は実行しても安全であり、簡単な結果を生成します。ただし、集約に使用された列に対応する適切な方法で、結果が解釈されていることを確認してください。

例: sequenced問合わせでのCOUNT集約

航空機の機体保守サービスを提供する企業によって実行される3つのジョブに関する次の情報を検討します。

ID Job_Type Charge Duration NumWorkersAssigned
123 Wing 80 4 Jan 2011 – 8 Jan 2011 5
123 Fuselage 20 5 Jan 2011 – 7 Jan 2011 3
123 Landing Gear 6 6 Jan 2011 – 9 Jan 2011 1

継続期間が有効時間列であるテンポラル テーブルに情報が格納される場合、すべての行の有効時間を次のように配置できます。

Jan 4 5 6 7 8 9
    |-------|
      |---|
        |-----|

sequenced問合わせの集約は、すべての行の組み合わせされたvalid-timeを、重なり合う期間と重なり合わない期間に分割します。この例では、有効時間の期間は、テーブルのすべての行に対するすべてのvalid-time期間の境界により定義される、次の5つの固有の継続期間を記述します。

Jan 4 5 6 7 8 9
    |-|-|-|-|-|

Jan 4-5、5-6、6-7、7-8 および 8-9。

(WHERE句の対象がない)テーブル全体のsequenced問合わせは、次の5つの固有の継続期間のそれぞれを通じての集約を返します。

SEQUENCED VALIDTIME
SELECT id, COUNT(*) jobcount
FROM aircraft_service
GROUP BY 1
ORDER BY VALIDTIME;

次の結果が得られます。

ID Jobcount VALIDTIME
123 1 ('11/01/04', '11/01/05')
123 2 ('11/01/05', '11/01/06')
123 3 ('11/01/06', '11/01/07')
123 2 ('11/01/07', '11/01/08')
123 1 ('11/01/08', '11/01/09')
集約を含むsequenced valid-time問合わせとともにORDER BY VALIDTIME句を使用する場合、順序には集約を実行するために計算された生成済みのVALIDTIME固有期間が考慮されます。

GROUP BY VALIDTIME句を使用する場合、グループ化は固有期間を使用しませんが、代わりに非集約問合わせの結果に割り当てられる同じVALIDTIME値を使用します。それは、最初に問合わせが実行されたテーブルの該当行の有効期間を含む問合わせの適用期間の交差です。

例: sequenced問合わせでのMIN集約とMAX集約

MIN関数とMAX関数を、テンポラル テーブルを持つsequenced集約で使用できますが、適切な列に適用することが重要です。機体保守サービスの例では、Charge列の情報は状態値ではありません。MINとMAXを、時間依存のsequenced集約のこの列に適用すると、誤解されやすい結果や、意味がない結果が生成されます。

ただし次のように、任意の時点のすべてのジョブを対象として、割り当てられる従業員の最小人数と最大人数を求めるのは意味があります。

SEQUENCED VALIDTIME
SELECT id,
       min(NumWorkersAssigned) as Minworkers,
       max(NumWorkersAssigned) as Maxworkers
FROM aircraft_service
GROUP BY 1
ORDER BY VALIDTIME;
ID Minworkers Maxworkers VALIDTIME
123 5 5 ('11/01/04', '11/01/05')
123 3 5 ('11/01/05', '11/01/06')
123 1 5 ('11/01/06', '11/01/07')
123 1 5 ('11/01/07', '11/01/08')
123 1 1 ('11/01/08', '11/01/09')

例: sequenced問合わせでのSUM集約とAVG集約

SUM集約とAVG集約を、valid-timeテンポラル テーブルの状態列に対して使用できます。本書の機体保守サービスの例では、NumWorkersAssigned列の情報は状態値であり、valid-time列で指定された期間中、有効です。次の例のように、機体に割り当てられた従業員の合計数または平均数について尋ねるのは意味があります。

SEQUENCED VALIDTIME
SELECT id,
       SUM (NumWorkersAssigned) TotalWorkersAssigned,
       AVG (NumWorkersAssigned) AvgWorkersAssigned,
FROM aircradt_service
GROUP BY 1
ORDER BY VALIDTIME;
ID TotalWorkersAssigned AvgWorkersAssigned VALIDTIME
123 5 5 ('11/01/04', '11/01/05')
123 8 4 ('11/01/05', '11/01/06')
123 9 3 ('11/01/06', '11/01/07')
123 6 3 ('11/01/07', '11/01/08')
123 1 1 ('11/01/08', '11/01/09')

例: sequenced集約から意味のある結果を得る

Charge列で有意義に集約関数を使用するには、有効時間列の粒度に一致する累積値として表現する必要があります。例えば、この例では各機体サービス ジョブに対して1日当たりのチャージとしてチャージが示されている場合、SUMやAVGなどのsequenced集約を意味のある結果に適用できます。

例えば、航空会社のデータが、1日当たりのチャージとして表現されるサービス ジョブの各タイプのチャージを持っていたとします。

ID Job_Type ChargePerDay 期間
123 Wing 20 4 Jan 2011 – 8 Jan 2011
123 Fuselage 10 5 Jan 2011 – 7 Jan 2011
123 Landing Gear 2 6 Jan 2011 – 9 Jan 2011

次のsequenced集約問合わせは、意味のある結果を生成します。

SEQUENCED VALIDTIME
SELECT id,
       SUM (ChargePerDay) TotalChargePerDay ,
       AVG (ChargePerDay) AvgChargePerDay
FROM aircraft_service
GROUP BY 1
ORDER BY VALIDTIME;
ID TotalChargePerDay AveChargePerDay VALIDTIME
123 20 20 ('11/01/04', '11/01/05')
123 30 15 ('11/01/05', '11/01/06')
123 32 11 ('11/01/06', '11/01/07')
123 22 11 ('11/01/07', '11/01/08')
123 2 2 ('11/01/08', '11/01/09')

例: GROUP BYでのsequenced集約の使用

問合わせのGROUP BYを含むsequenced集約には、「空」のvalid-time期間があり、その間はテンポラル テーブルのいずれの行も有効ではありません。上の例のテンポラル テーブルに、valid-time期間が既存の有効期間と連続していない別の行が含まれていると想定します。

ID Job_Type ChargePerDay 期間
123 Cockpit 40 1 Jan 2012 – 1 Mar 2012

上記の問合わせは、1つの新しい継続期間と、もう1つの機体の保守作業のない期間の2つの追加の行を返します。

ID TotalChargePerDay AvgChargePerDay VALIDTIME
123 ? (NULL) ? (NULL) ('11/01/09', '12/01/01')
123 40 40 ('12/01/01', '12/03/01')

これにより、「テーブルがカバーしている期間で、いかなる航空機もサービスを受けていなかった時期はいつですか?」などの質問に答えることができます。

SEQUENCED VALIDTIME PERIOD(date'2011-01-01', date'2012-03-01')
SELECT id FROM aircraft_service
       HAVING COUNT(ChargePerDay)= 0
       GROUP BY 1
       ORDER BY 1;
ID VALIDTIME
123 ('11/01/09', '12/01/01')

「空」のvalid-time期間が返されないようにするには、HAVING句を使用して問合わせに条件を追加して、これらの行をフィルタで除外します。