將 Teradata RESET WHEN 功能轉換為 Amazon Redshift SQL

由 Po Hong (AWS) 建立

Summary

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

Amazon Redshift 在 SQL 視窗函數中不支援 RESET WHEN。若要實作此功能，您必須將 RESET WHEN 轉換為 Amazon Redshift 中的原生 SQL 語法，並使用多個巢狀函數。此模式示範如何使用 Teradata RESET WHEN 功能，以及如何將其轉換為 Amazon Redshift SQL 語法。

先決條件和限制

先決條件

Teradata 資料倉儲及其 SQL 語法的基本知識
充分了解 Amazon Redshift 及其 SQL 語法

架構

來源技術堆疊

Teradata 資料倉儲

目標技術堆疊

Amazon Redshift

架構

如需將 Teradata 資料庫遷移至 Amazon Redshift 的高階架構，請參閱使用 AWS SCT 資料擷取代理程式將 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 RESET WHEN 使用案例

為了分析此資料，Teradata SQL 使用具有巢狀彙總和 RESET WHEN 片語的視窗函數，如下所示：


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（平衡） 彙總函數會計算指定月份中指定帳戶的所有餘額總和。
我們會檢查指定月份的餘額（針對指定帳戶）是否大於上個月的餘額。
如果餘額增加，我們會追蹤累積計數值。如果 RESET WHEN 條件評估為 false，表示餘額已連續幾個月增加，我們會繼續增加計數。
ROW_NUMBER() 排序分析函數會計算計數值。當我們達到餘額小於或等於上個月餘額的月份時，RESET WHEN 條件會評估為 true。若是如此，我們會啟動新的分割區，ROW_NUMBER() 會從 1 重新啟動計數。我們使用介於 1 PRECEDING 和 1 PRECEDING 之間的 ROWS 來存取上一列的值。
我們減去 1，以確保計數值以 0 開頭。

Amazon Redshift 對等 SQL

Amazon Redshift 不支援 SQL 分析視窗函數中的 RESET WHEN 片語。若要產生相同的結果，您必須使用 Amazon Redshift 原生 SQL 語法和巢狀子查詢重寫 Teradata SQL，如下所示：


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) 中，會產生新的_dynamic_part 屬性作為 SUM 視窗函數的結果。
最後，將 new_dynamic_part 作為新的分割區屬性 (動態分割區) 新增至現有的分割區屬性 (account_id)，並套用與 Teradata （和減一）相同的 ROW_NUMBER() 視窗函數。

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

史詩

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