翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。 # Teradata NORMALIZE 時間的特徴量を Amazon Redshift SQL に変換 *Po Hong (Amazon Web Services)* ## 概要 **NORMALIZE** は ANSI SQL 標準に対するTeradata 拡張です。SQL テーブルに **PERIOD** データ型の列が含まれている場合、**NORMALIZE** はその列と一致するか重複する値を組み合わせて、複数の個別の期間値を統合する 1 つの期間を形成します。**NORMARISE**を使用するには、SQL の**選択**リストに少なくとも１つの列がテラデータの一時的な**期間**データ型である必要があります。**NORMALIZE**の詳細については、「[Teradata のドキュメント](https://docs.teradata.com/reader/2_MC9vCtAJRlKle2Rpb0mA/MIGI5UMEwgERC3Un7tEZ6g)」を参照してください。 Amazon Redshift に**NORMALIZE**は適用されませんが、ネイティブ SQL 構文と Amazon Redshift の** LAG **ウィンドウ関数を使用することでこの機能を実装できます。このパターンは、最も一般的な形式である **ON MEETS OR OVERLAPS** 条件で、Teradata **NORMALIZE**の拡張機能を使用することにフォーカスします。この特徴量が Teradata でどのように機能するかについて、また Amazon Redshift ネイティブ SQL 構文に変換する方法についても説明しています。 ## 前提条件と制限 **前提条件 ** + Teradata SQL の基本的な知識と経験 + Amazon Redshift の知識と経験 ## アーキテクチャ **ソーステクノロジースタック** + Teradataデータウェアハウス **ターゲットテクノロジースタック** + Amazon Redshift **ターゲットアーキテクチャ** Teradata データベースを Amazon Redshift に移行するための高レベルのアーキテクチャについては、「[AWS SCT データ抽出エージェントを使用して、Teradataのデータベースを Amazon Redshift に移行する](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-a-teradata-database-to-amazon-redshift-using-aws-sct-data-extraction-agents.html)」というパターンを参照してください。移行しても、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_date`Teradata **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 ウィンドウ関数](https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LAG.html)」を使用できます。この機能では、パーティションの現在の行より上 (以前) の指定されたオフセットの行の値を返します。 **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) ``` ## エピック ### NORMALIZE を Amazon Redshift SQL に変換 | タスク | 説明 | 必要なスキル | | --- | --- | --- | | Teradata SQL コードを作成します。 | 必要に応じて NORMALIZE フレーズを使用してください。 | SQL Developer | | コードを Amazon Redshift SQL に変換します。 | コードを変換するには、このパターンの「ツール」セクションのガイドラインに従います。 | SQL Developer | | Amazon Redshift でコードを実行します。 | テーブルを作成し、テーブルにデータをロードして、Amazon Redshift でコードを実行します。 | SQL Developer | ## 関連リソース **リファレンス** + 「[Teradata NORMALIZE 時間的特徴量](https://docs.teradata.com/reader/2_MC9vCtAJRlKle2Rpb0mA/MIGI5UMEwgERC3Un7tEZ6g)」 (テラデータのドキュメント) + 「[LAG ウィンドウ関数](https://docs.aws.amazon.com/redshift/latest/dg/r_WF_LAG.html)」 (Amazon Redshift ドキュメント) + 「[Amazon Redshiftに移行](https://aws.amazon.com/redshift/data-warehouse-migration/)」(AWS ウェブサイト) + 「[AWS SCT データ抽出エージェントを使用してTeradata　データベースを Amazon Redshift に移行する](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-a-teradata-database-to-amazon-redshift-using-aws-sct-data-extraction-agents.html)」(AWS 規範ガイダンス) + 「[Teradata RESET WHEN 特徴量を Amazon Redshift SQL」 (AWS 規範ガイダンス) に変換](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/convert-the-teradata-reset-when-feature-to-amazon-redshift-sql.html) ツール + 「[AWS Schema Conversion Tool (AWS SCT)](https://aws.amazon.com/dms/schema-conversion-tool/)」 **パートナー** + 「[AWS 移行コンピテンシーパートナー](https://aws.amazon.com/migration/partner-solutions/#delivery)」