将 Teradata NORMALIZE 时态特征转换为 Amazon Redshift SQL

由 Po Hong 创作 (AWS)

来源：Teradata 数据仓库	目标：Amazon Redshift	R 类型：重构
环境：生产	技术：分析；数据库；迁移	工作负载：所有其他工作负载
AWS服务：亚马逊 Redshift

Summary

NORMALIZE是该标准的 Teradata 扩展。ANSI SQL当SQL表包含具有PERIOD数据类型的列时，会将该列中相交或重叠的值NORMALIZE组合在一起，形成一个合并多个单独周期值的单个周期。要使用 NORMALIZE，SQLSELECT列表中至少有一列必须是 Teradata 的时态PERIOD数据类型。有关的更多信息 NORMALIZE，请参阅 Teradata 文档。

Amazon Redshift 不支持 NORMALIZE，但你可以使用原生SQL语法和 Amazon Redshift 中的LAG窗口函数来实现此功能。这种模式侧重于使用带有 ON O MEETSR OVERLAPS 条件的 Teradata NORMALIZE扩展，这是最流行的格式。它解释了该功能在 Teradata 中的工作原理以及如何将其转换为 Amazon Red SQL shift 原生语法。

先决条件和限制

先决条件

基本的 Teradata SQL 知识和经验
Amazon Redshift 知识和经验

架构

源技术堆栈

Teradata 数据仓库

目标技术堆栈

Amazon Redshift

目标架构

有关将 Teradata 数据库迁移到 Amazon Redshift 的高级架构，请参阅使用数据提取代理将 Teradata 数据库迁移到 Amazon Redshift 的模式。AWS SCT迁移不会自动将 Teradata NORMALIZE短语转换为 Amazon Redshift。SQL您可按照此模式中的指导方针转换此 Teradata 扩展。

工具

代码

为了说明的概念和功能 NORMALIZE，请考虑 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')

Teradata NORMALIZE 用例

现在在语句中添加 Teradata NORMALIZESQL子句：SELECT


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

此NORMALIZE操作是在单列 (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语句对 project_name 和 dept_id 执行NORMALIZE操作。请注意，该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')

相当于亚马逊 Redshift SQL

Amazon Redshift 目前不支持表中的PERIOD数据类型。相反，您需要将 Teradata PERIOD数据字段分为两部分：开始日期、结束日期，如下所示：


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)

要重写 Teradata 的NORMALIZE子句，你可以使用 Amazon Redshift 中的LAG窗口函数。此函数返回位于分区中当前行的上方（之前）的某个给定偏移量位置的行的值。

您可以使用该LAG函数通过确定一个时段是否与前一时段相交或重叠来标识开始新时段的每一行（如果是，则为 0，如果不是，则为 1）。累积汇总此标志后，它会提供组标识符，该标识符可用于外部 Group By 子句中，从而在 Amazon Redshift 中得出所需结果。

以下是使用 LAG() 的亚马逊 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 语句在 project_name 和 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)

操作说明

任务	描述	所需技能
创建你的 Teradata 代码SQL。	根据需要使用该NORMALIZE短语。	SQL开发者
将代码转换为亚马逊 Redshift SQL。	若要转换您的代码，请按照此模式的“工具”部分中的指南进行操作。	SQL开发者
在 Amazon Redshift 中运行代码。	创建您的表，将数据加载至表中，然后在 Amazon Redshift 中运行您的代码。	SQL开发者