在3秒内排除相同类型的事务

Question

例如，一个表如下

ACDCCDE

类型		时间戳	结果
1	2021-06-25 14:21:00
1	2021-06-25 14:21:03
1	2021-06-25 14:21:06
1	2021-06-25 14:23:00
2	2021-06-25 14:21:02
2	2021-06-25 14:21:06
2	2021-06-25 14:21:09
3	2021-06-25 14:21:06

基本上，您想要相同类型的下一行领先三秒以上的行：

select t.*
from (select t.*,
lead(timestamp) over (partition by type order by timestamp) as next_timestamp
from t
) t
where next_timestamp is null or
next_timestamp > timestamp + interval '3' second;

使用LEAD和Teradata专有的QUALIFY关键字的默认值简化Gordon的查询：

select *
from vt
qualify 
lead(timestamp, 1, date '9999-12-31')
over (partition by type
order by timestamp) > timestamp_ + interval '3' second;