將平方數據重置功能轉換為 Amazon Redshift SQL - AWS Prescriptive Guidance

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

將平方數據重置功能轉換為 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

工具

合作夥伴