將平方數據重置功能轉換為 Amazon Redshift SQL - AWS Prescriptive Guidance
Summary先決條件和限制ArchitectureToolsEpics相關資源

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

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

由寶康 (AWS) 創作

來源：Terdata 資料倉儲

目標：Amazon Redshift

R 類型：重新架構

環境：生產

技術：分析; 資料庫; 移轉

工作負載：所有其他工作負載

AWS 服務：Amazon Redshift

Summary

重設時是 SQL 分析窗口函數中使用的 Terdata 功能。它是 ANSI SQL 標準的延伸。重設時會根據某些指定的條件決定 SQL 視窗函數運作的分割區。若條件評估為TRUE，則會在現有的視窗磁碟分割內建立新的動態子磁碟分割。如需有關 的詳細資訊重設時，請參閱Terdata 文件。

Amazon Redshift 不支援重設時在 SQL 視窗函數中。要實現此功能，您必須將重設時添加到 Amazon Redshift 中的本地 SQL 語法，並使用多個嵌套函數。此模式演示如何使用 Terdata重設時功能，以及如何將其轉換為 Amazon Redshift SQL 語法。 

先決條件和限制

先決條件

  • Terdata 數據倉庫及其 SQL 語法的基本知識

  • 對 Amazon Redshift 及其 SQL 語法的良好理解

Architecture

來源技術堆疊

  • Terdata 資料倉儲

目標技術堆疊

  • Amazon Redshift

架構

如需將 Terdata 資料庫遷移到 Amazon Redshift 的高階架構，請參閱使用 AWS SCT 資料擷取代理程式將 Terdata 資料資料庫遷移到 Amazon Redshift。遷移不會自動轉換 Terdata重設時短語添加到 Amazon Redshift SQL。您可以按照下一節中的指導方針轉換此 Terdata 擴充功能。

Tools

Code

為了說明重設時，請考慮以下 Terdata 中的表格定義：

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

月份 (_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

對於每個帳戶，假設您想要分析連續每月餘額增加的順序。當一個月的餘額小於或等於上個月的餘額時，需要將計數器重設為零並重新啟動。

使用案例時重置太數據

為了分析這些數據，Terdata 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

月份 (_ID)

balance

平衡增加 (_A)

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

查詢的處理方式如下在 Terdata：

  1. 所以此SUM (餘額)彙總函數會計算指定月份內給定帳戶的所有餘額總和。

  2. 我們會檢查指定月份 (針對特定帳戶) 的餘額是否大於上個月的餘額。

  3. 如果餘額增加，我們會追蹤累計計數值。如果重設時條件評估為false，這意味著平衡已經在連續幾個月增加，我們繼續增加計數。

  4. 所以此列號碼 ()有序分析函數計算計數值。當我們達到一個月餘額小於或等於上個月餘額的月份時，重設時條件評估為true。如果是這樣，我們會啟動一個新的分割區，列號碼 ()從 1 重新啟動計數。我們使用前面 1 和前面 1 之間的行來訪問前一行的值。

  5. 我們減去 1，以確保計數值以 0 開始。

Amazon Redshift 等效 SQL

Amazon Redshift 不支援重設時短語中的 SQL 分析窗口函數。 若要產生相同的結果，您必須使用 Amazon Redshift 原生 SQL 語法和嵌套子查詢重寫 Terdata 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 不支援SELECT子句，則必須使用兩個嵌套的子查詢。

  • 在內部子查詢 (別名 A) 中，動態磁碟分割指示器 (動態零件）被創建並填充。動態零件設定為 1，如果一個月的餘額小於或等於前一個月的餘額；否則，它會設定為 0。 

  • 在下一層（別名 B）中，新建動態零件 (_E)屬性生成的結果SUM視窗函數。 

  • 最後，您將新建動態零件 (_E)作為新的分區屬性（動態分區) 新增至現有的磁碟分割屬性 (account_id）並應用相同的列號碼 ()窗口函數，如在 Terdata 中一樣（和減一）。 

這些變化後，Amazon Redshift SQL 生成相同的輸出為 Terradata。

Epics

任務描述所需技能
創建您的 Terdata 窗口功能。

根據您的需求，使用嵌套聚合和 RESET WHEN 短語。

SQL Developer
將代碼轉換為 Amazon Redshift SQL。

若要轉換程式碼，請遵循此模式的 ＜ 工具 ＞ 一節中的指導方針。

SQL Developer
在 Amazon Redshift 運行代碼。

建立表格、將資料載入表格，然後在 Amazon Redshift 中執行程式碼。

SQL Developer

References

工具

合作夥伴