Teradata NORMALIZE 時間的特徴量を Amazon Redshift SQL に変換

Po Hong (Amazon Web Services)

概要

NORMALIZE は ANSI SQL 標準に対するTeradata 拡張です。SQL テーブルに PERIOD データ型の列が含まれている場合、NORMALIZE はその列と一致するか重複する値を組み合わせて、複数の個別の期間値を統合する 1 つの期間を形成します。NORMARISEを使用するには、SQL の選択リストに少なくとも１つの列がテラデータの一時的な期間データ型である必要があります。NORMALIZEの詳細については、「Teradata のドキュメント」を参照してください。 

Amazon Redshift にNORMALIZEは適用されませんが、ネイティブ SQL 構文と Amazon Redshift の LAG ウィンドウ関数を使用することでこの機能を実装できます。このパターンは、最も一般的な形式である ON MEETS OR OVERLAPS 条件で、Teradata NORMALIZEの拡張機能を使用することにフォーカスします。この特徴量が Teradata でどのように機能するかについて、また Amazon Redshift ネイティブ SQL 構文に変換する方法についても説明しています。

前提条件と制限

前提条件 

アーキテクチャ

ソーステクノロジースタック

ターゲットテクノロジースタック

ターゲットアーキテクチャ

Teradata データベースを Amazon Redshift に移行するための高レベルのアーキテクチャについては、「AWS SCT データ抽出エージェントを使用して、Teradataのデータベースを Amazon Redshift に移行する」 というパターンを参照してください。移行しても、TeradataのNORMALIZEフレーズが Amazon Redshift SQL に自動的に変換されるわけではありません。このTeradata 拡張は、このパターンのガイドラインに従って変換できます。

ツール

Code

NORMALIZEの概念と機能を説明するために、Teradata で以下のテーブル定義を考慮します:

CREATE TABLE systest.project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          duration      PERIOD(DATE)
     );

次の SQL コードを実行して、サンプルデータをテーブルに挿入します：

BEGIN TRANSACTION;

INSERT INTO systest.project VALUES (10, 'First Phase', 1000,  PERIOD(DATE '2010-01-10', DATE '2010-03-20') );
INSERT INTO systest.project VALUES (10, 'First Phase', 2000,  PERIOD(DATE '2010-03-20', DATE '2010-07-15') );

INSERT INTO systest.project VALUES (10, 'Second Phase', 2000,  PERIOD(DATE '2010-06-15', DATE '2010-08-18') );
INSERT INTO systest.project VALUES (20, 'First Phase', 2000,  PERIOD(DATE '2010-03-10', DATE '2010-07-20') );

INSERT INTO systest.project VALUES (20, 'Second Phase', 1000,  PERIOD(DATE '2020-05-10', DATE '2020-09-20') );

END TRANSACTION;

結果:

select * from systest.project order by 1,2,3;
 
 *** Query completed. 4 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.
 
     emp_id  project_name              dept_id  duration
-----------  --------------------  -----------  ------------------------
         10  First Phase                  1000  ('10/01/10', '10/03/20')        
         10  First Phase                  2000  ('10/03/20', '10/07/15')
         10  Second Phase                 2000  ('10/06/15', '10/08/18')
         20  First Phase                  2000  ('10/03/10', '10/07/20')
         20  Second Phase                 1000  ('20/05/10', '20/09/20')

Teradata NORMALIZEのユースケース

ここで、Teradata NORMALIZE SQL 句を SELECT ステートメントに追加します:

SELECT NORMALIZE ON MEETS OR OVERLAPS emp_id, duration 
FROM systest.project 
ORDER BY 1,2;

この NORMALIZE オペレーションは 1 つの列 () で実行されますemp_id。の場合emp_id=10、継続期間の 3 つの重複する期間値は、次のように 1 つの期間値にまとめられます。 

     emp_id  duration
-----------  ------------------------
         10  ('10/01/10', '10/08/18')
         20  ('10/03/10', '10/07/20')
         20  ('20/05/10', '20/09/20')

次の SELECT ステートメントは、 project_nameおよび に対して NORMALIZE オペレーションを実行しますdept_id。SELECT リストには、PERIOD 列 が 1 つだけ含まれることに注意してくださいduration。

SELECT NORMALIZE project_name, dept_id, duration 
FROM systest.project;

出力:

project_name              dept_id  duration
--------------------  -----------  ------------------------
First Phase                  1000  ('10/01/10', '10/03/20')
Second Phase                 1000  ('20/05/10', '20/09/20')
First Phase                  2000  ('10/03/10', '10/07/20')
Second Phase                 2000  ('10/06/15', '10/08/18')

Amazon Redshift と同等の SQL

Amazon Redshift には現在、テーブルの期間データタイプが適用されません。代わりに、start_date, end_dateTeradata PERIOD データフィールドを次のように の 2 つの部分に分割する必要があります。 

CREATE TABLE systest.project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          start_date  DATE,
          end_date    DATE
     );

テーブルにデータ行を挿入します:

BEGIN TRANSACTION;
 
INSERT INTO systest.project VALUES (10, 'First Phase', 1000,  DATE '2010-01-10', DATE '2010-03-20' );
INSERT INTO systest.project VALUES (10, 'First Phase', 2000,  DATE '2010-03-20', DATE '2010-07-15');
 
INSERT INTO systest.project VALUES (10, 'Second Phase', 2000,  DATE '2010-06-15', DATE '2010-08-18' );
INSERT INTO systest.project VALUES (20, 'First Phase', 2000,  DATE '2010-03-10', DATE '2010-07-20' );
 
INSERT INTO systest.project VALUES (20, 'Second Phase', 1000,  DATE '2020-05-10', DATE '2020-09-20' );
 
END TRANSACTION;

出力:

 emp_id | project_name | dept_id | start_date |  end_date
--------+--------------+---------+------------+------------
     10 | First Phase  |    1000 | 2010-01-10 | 2010-03-20
     10 | First Phase  |    2000 | 2010-03-20 | 2010-07-15
     10 | Second Phase |    2000 | 2010-06-15 | 2010-08-18
     20 | First Phase  |    2000 | 2010-03-10 | 2010-07-20
     20 | Second Phase |    1000 | 2020-05-10 | 2020-09-20
(5 rows)

TeradataのNORMALIZE 句を書き直すには、Amazon Redshift の 「LAG ウィンドウ関数」 を使用できます。この機能では、パーティションの現在の行より上 (以前) の指定されたオフセットの行の値を返します。

LAG 関数を使用して、ある期間が前の期間と一致するか重複しているかどうか (「はい」の場合は 0、「いいえ」の場合は 1) により、新しい期間を開始する各行を識別できます。このフラグを累積的に合計して、Amazon Redshift で望ましい結果を得るために、外部の グループ分け 句で使用できるグループ ID を提供します。 

LAG () を使用する Amazon Redshift SQL ステートメントのサンプルは次のとおりです:

SELECT emp_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project 
ORDER BY 1,2;

出力:

 emp_id | start_date |  end_date  | groupstartflag
--------+------------+------------+----------------
     10 | 2010-01-10 | 2010-03-20 |              1
     10 | 2010-03-20 | 2010-07-15 |              0
     10 | 2010-06-15 | 2010-08-18 |              0
     20 | 2010-03-10 | 2010-07-20 |              1
     20 | 2020-05-10 | 2020-09-20 |              1
(5 rows)

次の Amazon Redshift SQL ステートメントは、 emp_id列でのみ正規化されます。

SELECT T2.emp_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date
FROM 
( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY emp_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID
FROM ( SELECT emp_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project ) T1
) T2
GROUP BY T2.emp_id, T2.GroupID
ORDER BY 1,2;

出力:  

 emp_id | new_start_date | new_end_date
--------+----------------+------------------------------------
     10 | 2010-01-10     | 2010-08-18
     20 | 2010-03-10     | 2010-07-20
     20 | 2020-05-10     | 2020-09-20
(3 rows)

次の Amazon Redshift SQL ステートメントは、 列project_nameと dept_id列の両方で正規化されます。

SELECT T2.project_name, T2.dept_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date
FROM 
( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY project_name, dept_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID
FROM ( SELECT project_name, dept_id, start_date, end_date, 
            (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY project_name, dept_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag
FROM systest.project ) T1
) T2
GROUP BY T2.project_name, T2.dept_id, T2.GroupID
ORDER BY 1,2,3;

出力:

 project_name | dept_id | new_start_date | new_end_date
--------------+---------+----------------+--------------
 First Phase  |    1000 | 2010-01-10     | 2010-03-20
 First Phase  |    2000 | 2010-03-10     | 2010-07-20
 Second Phase |    1000 | 2020-05-10     | 2020-09-20
 Second Phase |    2000 | 2010-06-15     | 2010-08-18
(4 rows)

エピック

関連リソース

リファレンス

ツール

パートナー