Teradata RESETWHEN機能を Amazon Redshift に変換する SQL

作成者: Po Hong (AWS）

ソース: Teradata データウェアハウス	ターゲット: Amazon Redshift	Rタイプ: リアーキテクト
環境:本稼働	テクノロジー: 分析、データベース、移行	ワークロード：その他すべてのワークロード
AWS サービス: Amazon Redshift

[概要]

RESET WHEN は、SQL分析ウィンドウ関数で使用される Teradata 機能です。これはANSISQL標準の延長です。RESET WHEN は、指定された条件に基づいて、SQLウィンドウ関数が動作するパーティションを決定します。条件がに評価TRUEされると、新しい動的サブパーティションが既存のウィンドウパーティション内に作成されます。の詳細については、「Teradata ドキュメントRESETWHEN」を参照してください。

Amazon Redshift は、SQLウィンドウ関数RESETWHENではをサポートしていません。この機能を実装するには、Amazon Redshift のネイティブSQL構文RESETWHENに変換し、複数のネストされた関数を使用する必要があります。このパターンは、Teradata RESETWHEN機能を使用する方法と、それを Amazon Redshift SQL構文に変換する方法を示しています。

前提条件と制限

前提条件

Teradata データウェアハウスとそのSQL構文に関する基本的な知識
Amazon Redshift とそのSQL構文を十分に理解している

アーキテクチャ

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

Teradataデータウェアハウス

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

Amazon Redshift

アーキテクチャ

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

ツール

Code

の概念を説明するにはRESETWHEN、Teradata で次のテーブル定義を検討してください。


create table systest.f_account_balance                                
( account_id integer NOT NULL,
  month_id integer,
  balance integer )
unique primary index (account_id, month_id);

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


BEGIN TRANSACTION;
Insert Into systest.f_account_balance values (1,1,60);
Insert Into systest.f_account_balance values (1,2,99);
Insert Into systest.f_account_balance values (1,3,94);
Insert Into systest.f_account_balance values (1,4,90);
Insert Into systest.f_account_balance values (1,5,80);
Insert Into systest.f_account_balance values (1,6,88);
Insert Into systest.f_account_balance values (1,7,90);
Insert Into systest.f_account_balance values (1,8,92);
Insert Into systest.f_account_balance values (1,9,10);
Insert Into systest.f_account_balance values (1,10,60);
Insert Into systest.f_account_balance values (1,11,80);
Insert Into systest.f_account_balance values (1,12,10);
END TRANSACTION;

このサンプルテーブルには、次のデータがあります：

account_id	month_id	balance
1	1	60
1	2	99
1	3	94
1	4	90
1	5	80
1	6	88
1	7	90
1	8	92
1	9	10
1	10	60
1	11	80
1	12	10

アカウントごとに、連続的な月次残高の増加のシーケンスを分析したいものとします。ある月の残高が前月の残高以下の場合、必要なことはカウンターをゼロにリセットして再起動します。

Teradata RESETWHENユースケース

このデータを分析するために、Teradata は次のようにネストされた集計とRESETWHENフレーズを持つウィンドウ関数SQLを使用します。


SELECT account_id, month_id, balance,
 ( ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY month_id
RESET WHEN balance <= SUM(balance) over (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ) -1 ) as balance_increase
FROM systest.f_account_balance
ORDER BY 1,2;

出力:

account_id	month_id	balance	balance_increase
1	1	60	0
1	2	99	1
1	3	94	0
1	4	90	0
1	5	80	0
1	6	88	1
1	7	90	2
1	8	92	3
1	9	10	0
1	10	60	1
1	11	80	2
1	12	10	0

Teradataにクエリが次のように処理されます:

SUM（バランス） 集計関数は、特定の月の特定のアカウントのすべての残高の合計を計算します。
特定の月の（特定の口座の）残高が前月残高を超えているかどうかを確認します。
残高が増加した場合、累積のカウント値を追跡します。RESET WHEN 条件が false と評価され、連続する数か月にわたって残高が増えたことを意味する場合、カウントは増加し続けます。
ROW_NUMBER() 順序分析関数はカウント値を計算します。残高が前月の残高以下である月に達すると、RESETWHEN条件は true に評価されます。その場合、新しいパーティションを開始し、ROW_NUMBER() は 1 からカウントを再開します。ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING を使用して、前の行の値にアクセスします。
1 を差し引くことで、カウント値が必ず0 から始まるようにします。

Amazon Redshift と同等のもの SQL

Amazon Redshift RESET WHEN は、SQL分析ウィンドウ関数のフレーズをサポートしていません。同じ結果を生成するには、次のように Amazon Redshift ネイティブSQL構文とネストされたサブクエリSQLを使用して Teradata を書き換える必要があります。


SELECT account_id, month_id, balance,
   (ROW_NUMBER() OVER(PARTITION BY account_id, new_dynamic_part ORDER BY month_id) -1) as balance_increase
FROM
( SELECT account_id, month_id, balance, prev_balance,
SUM(dynamic_part) OVER (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As new_dynamic_part
FROM ( SELECT account_id, month_id, balance,
SUM(balance) over (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as prev_balance,
(CASE When balance <= prev_balance Then 1 Else 0 END) as dynamic_part
FROM systest.f_account_balance ) A
) B
ORDER BY 1,2;

Amazon Redshift は 1 つのSQLステートメントの SELECT句でネストされたウィンドウ関数をサポートしていないため、2 つのネストされたサブクエリを使用する必要があります。

内部サブクエリ (エイリアス A ) では、動的パーティションインジケータ (dynamic_part) が作成され、入力されます。ある月の残高が前月残高の以下の場合、 dynamic_part が 1 に設定され、それ以外の場合は 0 に設定されます。
次のレイヤー (エイリアス B) では、SUMウィンドウ関数の結果として new_dynamic_part 属性が生成されます。
最後に、new_dynamic_part を新しいパーティション属性 (動的パーティション ) として既存のパーティション属性 (account_id ) に追加し、Teradata と同じ ROW_NUMBER() ウィンドウ関数 (および - 1) を適用します。

これらの変更後、Amazon Redshift は Teradata と同じ出力SQLを生成します。

エピック

タスク	説明	必要なスキル
Teradataのウィンドウ関数を作成します。	ニーズに応じて、ネストされた集計とRESETWHENフレーズを使用します。	SQL デベロッパー
コードを Amazon Redshift に変換しますSQL。	コードを変換するには、このパターンの「ツール」セクションのガイドラインに従います。	SQL デベロッパー
Amazon Redshift でコードを実行します。	テーブルを作成し、テーブルにデータをロードして、Amazon Redshift でコードを実行します。	SQL デベロッパー