將太數據標準化時間功能轉換為 Amazon Redshift SQL

由寶康 (AWS) 創建

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

Summary

規範化是 ANSI SQL 標準的太數據擴展。當 SQL 表格包含具有「期間」資料類型的資料行時，NORMALIZE 會合併該欄中符合或重疊的值，以形成合併多個個別週期值的單一期間。若要使用標準化，SQL SELECT 清單中至少有一個資料行必須是 Teradata 的暫時週期資料類型。如需有關規範化的詳細資訊，請參閱 Teradata 文件。

Amazon Redshift 不支援規範化，但您可以使用原生 SQL 語法和 Amazon Redshift 中的 LAG 視窗函數來實作此功能。這種模式著重於使用 Teradata 規範化擴展與「開符合或重疊」條件，這是最流行的格式。本文說明此功能在 Teradata 中的運作方式，以及如何將其轉換為 Amazon Redshift 原生 SQL 語法。

先決條件和限制

先決條件

基本的 SQL 知識和經驗
Amazon Redshift 知識和經驗

架構

源, 技術, 堆棧

太级数据仓储

目標技術堆疊

Amazon Redshift

目標架構

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

工具

Code

為了說明正常化的概念和功能，請考慮 Teradata 中的下列表格定義：


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')

太數據標準化用例

現在，將太元數據標準化 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')

下面的 SELE CT 語句執行對項目名稱和 DEPT_ID 標準化操作。請注意，SELE CT 列表只包含一個期間列，持續時間。


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 目前不支持表中的期間數據類型。相反地，您需要將 Teradata 週期資料欄位分成兩部分：起始日期、結束日期，如下所示：


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)

要重寫太元數據的正常化子句，您可以在 Amazon Redshift 使用 LAG 窗口函數。此函數會傳回位於分割區中目前資料列上方 (之前) 指定偏移量的列值。

您可以使用 LAG 函數來識別開始新期間的每個資料列，方法是判斷期間是否符合或與先前期間重疊 (若為 0，若否則為 1)。當此旗標累計總結時，它會提供一個群組識別碼，可在外部 Group By 子句中使用，以便在 Amazon Redshift 中達到所需的結果。

以下是使用 LAG () 的 Amazon Redshift SQL 陳述式範例：


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 語句在項目名稱和 dept_id 列上正常化：


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)

史诗

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