將太子數據正常化臨時功能轉換為 Amazon Redshift SQL - AWS Prescriptive Guidance

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

將太子數據正常化臨時功能轉換為 Amazon Redshift SQL

由寶康 (AWS) 創作

來源:Terdata 資料倉儲

目標:Amazon Redshift

R 類型:重新架構

環境:生產

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

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

AWS 服務:Amazon Redshift

Summary

正常化是 ANSI SQL 標準的 Terdata 擴充功能。當 SQL 資料表包含具有PERIOD資料類型,正常化會結合該欄中符合或重疊的值,以形成合併多個個別期間值的單一期間。使用正常化,SQL 中至少有一個資料欄SELECT列表必須是 Terdata 的臨時PERIOD資料類型。如需有關 的詳細資訊正常化,請參閱Terdata 文件。 

Amazon Redshift 不支援正常化,但您可以使用原生 SQL 語法和LAG窗口 Amazon Redshift。這種模式著重於使用 Terdata正常化擴充功能遇見或重疊條件,這是最流行的格式。它解釋了這個功能是如何在 Terdata 中工作的,以及如何將它轉換成 Amazon Redshift 本地 SQL 語法。

先決條件和限制

先決條件

  • 基本的太數據 SQL 知識和經驗

  • Amazon Redshift 知識和經驗

Architecture

來源技術堆疊

  • Terdata 資料倉儲

目標技術堆疊

  • Amazon Redshift

目標架構

如需將 Terdata 資料庫遷移到 Amazon Redshift 的高階架構,請參閱使用 AWS SCT 資料擷取代理程式將 Terdata 資料資料庫遷移到 Amazon Redshift。遷移不會自動轉換 Terdata正常化短語添加到 Amazon Redshift SQL。您可以通過按照此模式中的指導方針轉換此 Terdata 擴展。

Tools

Code

為了說明的概念和功能正常化,請考慮以下 Terdata 中的表格定義:

CREATE TABLE systest.project      (    emp_id        INTEGER,           project_name  VARCHAR(20),           dept_id       INTEGER,           duration      PERIOD(DATE)      );

執行下列 SQL 程式碼,將範例資料插入資料表:

BEGIN TRANSACTION; INSERT INTO systest.project VALUES (10, 'First Phase', 1000, PERIOD(DATE '2010-01-10', DATE '2010-03-20') ); INSERT INTO systest.project VALUES (10, 'First Phase', 2000, PERIOD(DATE '2010-03-20', DATE '2010-07-15') ); INSERT INTO systest.project VALUES (10, 'Second Phase', 2000, PERIOD(DATE '2010-06-15', DATE '2010-08-18') ); INSERT INTO systest.project VALUES (20, 'First Phase', 2000, PERIOD(DATE '2010-03-10', DATE '2010-07-20') ); INSERT INTO systest.project VALUES (20, 'Second Phase', 1000, PERIOD(DATE '2020-05-10', DATE '2020-09-20') ); END TRANSACTION;

結果:

select * from systest.project order by 1,2,3;    *** Query completed. 4 rows found. 4 columns returned.  *** Total elapsed time was 1 second.        emp_id  project_name              dept_id  duration -----------  --------------------  -----------  ------------------------          10  First Phase                  1000  ('10/01/10', '10/03/20')                  10  First Phase                  2000  ('10/03/20', '10/07/15')          10  Second Phase                 2000  ('10/06/15', '10/08/18')          20  First Phase                  2000  ('10/03/10', '10/07/20')          20  Second Phase                 1000  ('20/05/10', '20/09/20')

太子數據正常化用例

現在新增 Terdata正常化SQL 子句新增至SELECT陳述式:

SELECT NORMALIZE ON MEETS OR OVERLAPS emp_id, duration  FROM systest.project  ORDER BY 1,2;

這一個正常化操作是在單個列(emp_id)上執行的。對於 emp_id=10,持續時間中的三個重疊期間值會合併為單一期間值,如下所示: 

    emp_id  duration -----------  ------------------------          10  ('10/01/10', '10/08/18')          20  ('10/03/10', '10/07/20')          20  ('20/05/10', '20/09/20')

如下所示SELECT陳述式會執行正常化項目名稱和代碼上的操作。請注意,SELECT清單只包含一個PERIOD資料行,持續時間。

SELECT NORMALIZE project_name, dept_id, duration  FROM systest.project;

輸出:

project_name              dept_id  duration --------------------  -----------  ------------------------ First Phase                  1000  ('10/01/10', '10/03/20') Second Phase                 1000  ('20/05/10', '20/09/20') First Phase                  2000  ('10/03/10', '10/07/20') Second Phase                 2000  ('10/06/15', '10/08/18')

Amazon Redshift 等效 SQL

Amazon Redshift 目前不支援PERIOD數據類型。相反,你需要劃分一個 TerdataPERIOD數據字段分成兩個部分:起始日期,結束日期,如下所示: 

CREATE TABLE systest.project      (    emp_id        INTEGER,           project_name  VARCHAR(20),           dept_id       INTEGER,           start_date  DATE,           end_date    DATE      );

將範例資料插入至資料表:

BEGIN TRANSACTION;   INSERT INTO systest.project VALUES (10, 'First Phase', 1000,  DATE '2010-01-10', DATE '2010-03-20' ); INSERT INTO systest.project VALUES (10, 'First Phase', 2000,  DATE '2010-03-20', DATE '2010-07-15');   INSERT INTO systest.project VALUES (10, 'Second Phase', 2000,  DATE '2010-06-15', DATE '2010-08-18' ); INSERT INTO systest.project VALUES (20, 'First Phase', 2000,  DATE '2010-03-10', DATE '2010-07-20' );   INSERT INTO systest.project VALUES (20, 'Second Phase', 1000,  DATE '2020-05-10', DATE '2020-09-20' );   END TRANSACTION;

輸出:

emp_id | project_name | dept_id | start_date |  end_date --------+--------------+---------+------------+------------      10 | First Phase  |    1000 | 2010-01-10 | 2010-03-20      10 | First Phase  |    2000 | 2010-03-20 | 2010-07-15      10 | Second Phase |    2000 | 2010-06-15 | 2010-08-18      20 | First Phase  |    2000 | 2010-03-10 | 2010-07-20      20 | Second Phase |    1000 | 2020-05-10 | 2020-09-20 (5 rows)

要重寫 Terdata 的正常化子句中,您可以使用LAG 視窗函數Amazon Redshift。此函數傳回分割區中目前列上方 (之前) 給定位移那一列的值。

您可以使用LAG函式來識別開始新週期的每個資料列,藉由判斷週期是否符合或與前一週期重疊 (如果是則為 0,如果沒有則為 1)。當這個旗標累計總結時,它會提供一個群組識別碼,可用於外部分組依據子句在 Amazon Redshift 中達到所需的結果。 

以下是一個示例 Amazon Redshift SQL 語句,它使用LAG ()

SELECT emp_id, start_date, end_date,              (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag FROM systest.project  ORDER BY 1,2;

輸出:

emp_id | start_date |  end_date  | groupstartflag --------+------------+------------+----------------      10 | 2010-01-10 | 2010-03-20 |              1      10 | 2010-03-20 | 2010-07-15 |              0      10 | 2010-06-15 | 2010-08-18 |              0      20 | 2010-03-10 | 2010-07-20 |              1      20 | 2020-05-10 | 2020-09-20 |              1 (5 rows)

以下 Amazon Redshift SQL 語句僅在 emp_id 列上進行標準化:

SELECT T2.emp_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date FROM  ( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY emp_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID FROM ( SELECT emp_id, start_date, end_date,              (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY emp_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag FROM systest.project ) T1 ) T2 GROUP BY T2.emp_id, T2.GroupID ORDER BY 1,2;

輸出: 

emp_id | new_start_date | new_end_date --------+----------------+------------------------------------      10 | 2010-01-10     | 2010-08-18      20 | 2010-03-10     | 2010-07-20      20 | 2020-05-10     | 2020-09-20 (3 rows)

 

下面的 Amazon Redshift SQL 語句正常化兩個項目名稱和數據列:

SELECT T2.project_name, T2.dept_id, MIN(T2.start_date) as new_start_date, MAX(T2.end_date) as new_end_date FROM  ( SELECT T1.*, SUM(GroupStartFlag) OVER (PARTITION BY project_name, dept_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) As GroupID FROM ( SELECT project_name, dept_id, start_date, end_date,              (CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY project_name, dept_id ORDER BY start_date, end_date) THEN 0 ELSE 1 END) AS GroupStartFlag FROM systest.project ) T1 ) T2 GROUP BY T2.project_name, T2.dept_id, T2.GroupID ORDER BY 1,2,3;

輸出:

project_name | dept_id | new_start_date | new_end_date --------------+---------+----------------+--------------  First Phase  |    1000 | 2010-01-10     | 2010-03-20  First Phase  |    2000 | 2010-03-10     | 2010-07-20  Second Phase |    1000 | 2020-05-10     | 2020-09-20  Second Phase |    2000 | 2010-06-15     | 2010-08-18 (4 rows)

Epics

任務描述所需的技能
創建您的太數據 SQL 代碼。

根據您的需求使用正常化短語。

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

若要轉換程式碼,請遵循此模式的 < 工具 > 一節中的指導方針。

SQL Developer
在 Amazon Redshift 運行代碼。

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

SQL Developer

References

工具

合作夥伴