將太數據重置功能轉換為 Amazon Redshift SQL

由寶康 (AWS) 創建

資料來源：泰瑞資料倉儲	目標：Amazon Redshift	R 型：重新建築
環境：生產	技術：分析；資料庫；移轉	工作負載：所有其他工作
AWS 服務：Amazon Redshift

Summary

重置時間是 SQL 分析窗口函數中使用的 Teradata 功能。它是一個擴展到 ANSI SQL 標準。RESET WH EN 決定 SQL 視窗函式根據某些指定條件來運作的分割區。如果條件評估為 TRUE，則在現有窗口分區內創建一個新的動態子分區。如需有關重設時機的詳細資訊，請參閱 Teradata 文件。

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

先決條件和限制

先決條件

Teradata 資料倉儲及其 SQL 語法的基本知識
對 Amazon Redshift 及其 SQL 語法有很好的理解

架構

源, 技術, 堆棧

太级数据仓储

目標技術堆疊

Amazon Redshift

架構

如需將 Teradata 資料庫遷移到 Amazon Redshift 的高階架構，請參閱使用 AWS SCT 資料擷取代理程式將 Teradata 資料庫遷移至 Amazon Redshift 移的模式。遷移不會自動將太數據重置時短語轉換為 Amazon Redshift SQL。您可以按照下一節中的準則轉換此 Teradata 擴展。

工具

Code

若要說明「重設時機」的概念，請在 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	月份	平衡
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 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	月份	平衡	餘額 _ 增加
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 WH EN 條件評估為 false，這意味著餘額在連續幾個月內增加，我們將繼續增加計數。
ROW_NUMBER () 有序的解析函數計算計數值。當我們到達一個月的餘額小於或等於上個月的餘額時，RESET WHEN 條件評估為 true。如果是這樣，我們啟動一個新的分區，ROW_NUMBER () 從 1 重新啟動計數。我們使用前 1 和 1 之間的行來訪問前一行的值。
我們減去 1 以確保計數值從 0 開始。

Amazon Redshift 等效 SQL

Amazon Redshift 不支持 SQL 分析窗口函數中的重置時間短語。若要產生相同的結果，您必須使用 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 陳述式的 SELE CT 子句中不支援巢狀視窗函數，因此您必須使用兩個巢狀子查詢。

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

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

史诗

任務	描述	所需技能
建立您的視窗功能。	根據您的需要使用嵌套聚合和 RESET 時短語。	SQL Developer
將代碼轉換為 Amazon Redshift SQL。	要轉換您的代碼，請遵循此模式的「工具」部分中的準則。	SQL Developer
在 Amazon Redshift 中運行代碼。	在 Amazon Redshift 中建立您的資料表、將資料載入資料表，然後在資料表中執行程式碼。	SQL Developer