将 Teradata RESET WHEN 功能转换为亚马逊 Redshift SQL

由 Po Hong 创作 (AWS)

摘要

RESETWHEN是SQL分析窗口函数中使用的 Teradata 功能。它是该ANSISQL标准的扩展。 RESETWHEN根据某些指定条件确定SQL窗口函数在其上运行的分区。如果条件评估为 TRUE，则会在现有窗口分区内创建一个新的动态子分区。有关的更多信息 RESETWHEN，请参阅 Teradata 文档。

亚马逊 Redshift 不支持RESETWHENSQL窗口功能。要实现此功能，您必须转换为 RESETWHENAmazon Redshift 中的原生SQL语法，并使用多个嵌套函数。此模式演示了如何使用 Teradata RESETWHEN功能以及如何将其转换为 Amazon SQL Redshift 语法。

先决条件和限制

先决条件

Teradata 数据仓库及其语法的基础知识 SQL
对亚马逊 Redshift 及其语法有很好的了解 SQL

架构

源技术堆栈

Teradata 数据仓库

目标技术堆栈

Amazon Redshift

架构

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

工具

代码

为了说明的概念 RESETWHEN，请考虑 Teradata 中的下表定义：


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	month_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

对于每个账户，假设您想要分析每月余额连续增加的顺序。当一个月的余额小于或等于上个月的余额时，要求将计数器重置为零，并重新启动。

Teradata RESET WHEN 用例

为了分析这些数据，Teradata SQL 使用了一个带有嵌套聚合和RESETWHEN短语的窗口函数，如下所示：


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	month_id	balance	balance_increase
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

在 Teradata 中，查询处理方式如下：

SUM（余额）汇总函数计算给定账户在给定月份内所有余额的总和。
我们会检查给定月份（给定账户）的余额是否大于上个月的余额。
如果余额增加，我们将跟踪累积计数值。如果RESETWHEN条件评估为假，这意味着余额在连续几个月中有所增加，则我们会继续增加计数。
ROW_ NUMBER () 有序分析函数计算计数值。当我们到达一个月的余额小于或等于前一个月的余额时，RESETWHEN条件的计算结果为 true。如果是这样，我们开始一个新分区然后 ROW_ NUMBER () 从 1 重新开始计数。我们使用 ROWSBETWEENPRECEDINGAND1 1 PRECEDING 来访问前一行的值。
我们减去 1，以确保计数值以 0 开头。

相当于亚马逊 Redshift SQL

Amazon Redshift 不支持SQL分析窗口函数中的RESETWHEN短语。要生成相同的结果，您必须使用 Amazon SQL Redshift 原生SQL语法和嵌套子查询重写 Teradata，如下所示：


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 不支持在单个SQL语SELECT句的子句中嵌套窗口函数，因此您必须使用两个嵌套的子查询。

在内部子查询(别名 A)中，创建并填充动态分区指示器 (dynamic_part)。如果一个月的余额小于或等于前一个月的余额，则 dynamic_part 设置为 1；否则，设置为 0。
在下一层（别名 B）中，将生成一个 n ew_dynamic_part 属性作为窗口函数的结果。SUM
最后，将 n ew_dynamic_part 作为新的分区属性（动态分区）添加到现有分区属性 (account_id) 中，并应用与 Teradata 中相同的 ROW_ NUMBER () 窗口函数（减一）。

进行这些更改后，Amazon Redshift SQL 生成的输出与 Teradata 相同。

操作说明

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