teradata - Teradata 查询中的领先和滞后 - 对于类别

Question

我正在编写一个查询以从数据转储中获取 SCD 2 类型的数据。
我的数据和代码如下:

create table promotions 
(
start_date date,
end_date date,
promotion_name varchar(50));

用于填充表的插入语句:

insert into promotions values ('9/1/2017','9/2/2017','P1');
insert into promotions values ('9/2/2017','9/3/2017','P1');
insert into promotions values ('9/3/2017','9/4/2017','P1');
insert into promotions values ('9/4/2017','9/5/2017','P1');
insert into promotions values ('9/5/2017','9/6/2017','P2');
insert into promotions values ('9/6/2017','9/7/2017','P2');
insert into promotions values ('9/7/2017','9/8/2017','P2');
insert into promotions values ('9/8/2017','9/9/2017','P2');
insert into promotions values ('9/9/2017','9/10/2017','P2');
insert into promotions values ('9/10/2017','9/11/2017','P2');
insert into promotions values ('9/11/2017','9/12/2017','P3');
insert into promotions values ('9/12/2017','9/13/2017','P3');
insert into promotions values ('9/13/2017','9/14/2017','P3');
insert into promotions values ('9/14/2017','9/15/2017','P3');

预期结果:

Date_Start      Date_End        Promotion Name
9/1/2017        9/4/2017        P1
9/5/2017        9/10/2017       P2
9/11/2017       9/13/2017       P3

我写的查询:

with cte as (select rank() over (partition by promotion_name order by start_date asc) as "Rank"
,start_date
,dateadd(day,-1,start_date) as EndDate
,promotion_name
--first_name, last_name
from dbo.promotions)
select * from cte where rank=1;

查询输出

start_date  EndDate promotion_name
2017-09-01  2017-08-31  P1
2017-09-05  2017-09-04  P2
2017-09-11  2017-09-10  P3

上述查询的问题是 EndDate 以错误的方式显示，
与上面的输出表相比。

在 SQL 服务器上的超前和滞后功能解决了这个问题，但在 TERADATA 上我无法获得超前/滞后功能的等效项。

我该怎么办。我不想创建任何 volatile /临时表，它只是一个简单的 ETL 查询。

Answer 1

LAG和 LEAD只是更短的语法，你可以像这样重写它:

LAG(col1, n) OVER (PARTITION BY ... ORDER BY col2)
=
MIN(col1) OVER (PARTITION BY ... ORDER BY col2
                ROWS BETWEEN n PRECEDING AND n PRECEDING), 0)

LEAD(col1, n) OVER (PARTITION BY ... ORDER BY col2)
=
MIN(col1) OVER (PARTITION BY ... ORDER BY col2
                ROWS BETWEEN n FOLLOWING AND n FOLLOWING), 0)

要获得默认值，只需使用 COALESCE :

LAG(col1, n, default) OVER (PARTITION BY ... ORDER BY col2)
=
COALESCE(MIN(col1) OVER (PARTITION BY ... ORDER BY col2
                         ROWS BETWEEN n PRECEDING AND n PRECEDING), 0)
        ,default)