如何查询出date最新时间num=3和num最开始等于3，中间不能有num=1的并且时间要大于一年的两列数据

Question

如何查询出date最新时间num=3和num最开始等于3，中间不能有num=1的并且时间要大于一年的两列数据

Answer 1

写个滑动窗口,将上一行和当前行求和,满足等于6,且两个日期差大于等于365天,提取出满足此条件的当前行,然后再以此为条件,从原数据里把对应的上一行也找到

with t(dt,n) as(
select date'2019-01-02',1 from dual union all
select date'2020-02-02',1 from dual union all
select date'2020-05-05',1 from dual union all
select date'2020-12-01',3 from dual union all
select date'2022-01-01',3 from dual 
),
t1 as (
select t.*,
sum(n) over (order by dt rows between 1 preceding and current row) as  sum_n,
max(dt) over (order by dt rows between 1 preceding and current row) -
min(dt) over (order by dt rows between 1 preceding and current row) as dif_d
 from t
)
select * from t where exists (
select 1 from t1 where sum_n=6 and dif_d>=365
and t.dt in (t1.dt-dif_d , t1.dt)
)