將 Teradata RESETWHEN功能轉換為 Amazon Redshift SQL

由 Po Hong 建立（AWS）

來源：Teradata 資料倉儲	目標：Amazon Redshift	R 類型：重新架構
環境：生產	技術：分析；資料庫；遷移	工作負載：所有其他工作負載
AWS 服務：Amazon Redshift

Summary

RESET WHEN 是SQL分析視窗函數中使用的 Teradata 功能。這是 ANSISQL標準的延伸。RESET WHEN 會根據某些指定的條件，決定SQL視窗函數在其上執行的分割區。如果條件評估為 TRUE，則會在現有視窗分割區內建立新的動態子分割區。如需 RESET WHEN的詳細資訊，請參閱 Teradata 文件。

Amazon Redshift 不支援RESETWHENSQL視窗函數。若要實作此功能，您必須在 SQL Amazon Redshift 中轉換為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

若要說明 RESET WHEN的概念，請考慮 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	餘額
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 SQL使用具有巢狀彙總和RESETWHEN片語的視窗函數，如下所示：


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_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（平衡） 彙總函數會計算指定月份中指定帳戶的所有餘額總和。
我們會檢查特定月份（針對特定帳戶）中的餘額是否大於上個月的餘額。
如果餘額增加，我們會追蹤累積計數值。如果RESETWHEN條件評估為 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分析時段函數中的片語。若要產生相同的結果，您必須SQL使用 Amazon Redshift 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 不支援單一SQL陳述式子SELECT句中的巢狀視窗函數，因此您必須使用兩個巢狀子查詢。

在內部子查詢（別名 A）中，會建立並填入動態分割區指示器（dynamic_part ）。如果一個月的餘額小於或等於上個月的餘額，則 dynamic_part 會設定為 1；否則，會設為 0。
在下一層（別名 B）中，視窗SUM函數會產生新的_dynamic_part 屬性。
最後，您將 new_dynamic_part 作為新的分割區屬性（動態分割區 ）新增至現有的分割區屬性（account_id ），並套用與 Teradata （和減一）相同的 ROW_NUMBER（） 視窗函數。

在這些變更之後，Amazon Redshift SQL會產生與 Teradata 相同的輸出。

史詩

任務	描述	所需的技能
建立 Teradata 視窗函數。	根據您的需求使用巢狀彙總和RESETWHEN片語。	SQL 開發人員
將程式碼轉換為 Amazon Redshift SQL。	若要轉換程式碼，請遵循此模式「工具」區段中的準則。	SQL 開發人員
在 Amazon Redshift 中執行程式碼。	建立資料表、將資料載入資料表，並在 Amazon Redshift 中執行程式碼。	SQL 開發人員