CREATE TABLE t_log(f_time DATE, f_value int)
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-01', 100);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-02', 101);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-03', 102);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-04', 99);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-05', 105);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-06', 108);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-07', 105);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-08', 199);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-09', 108);
with a as(
select f_group,lead(f_group,1,'9999-01-01') over (order by f_group) as f_grouptwo from
(select F_TIME, F_VALUE,case when lag(f_value,1,0)over (order by f_time)<100 then f_time else null end f_group from t_log)t
where f_group is not null)
, b as(
select * from a
where datediff(dd,f_group,f_grouptwo)>=3)
select f_group,max(f_time) as maxtime
from b
inner join t_log c
on b.f_group<=c.f_time and b.f_grouptwo>c.f_time
where c.f_value>=100
group by f_group
现在的输出:
f_group maxtime
2016-10-01 2016-10-03
2016-10-05 2016-10-09
目标输出
2016-10-01
2016-10-02
2016-10-03
2016-10-05
2016-10-06
2016-10-07
2016-10-08
2016-10-09