SQL题请教~

select id from (select *,sum(case when datediff(date-lag_date)<=1 and rate>=0.5 and lag_rate>=0.5 then 0 else 1                   end) over(partiton by id rows between unbounded preceding and current row) as s   from (select id,date,rate,            lag(date,1,null) over(partition by id order by date) as lag_date,            lag(rate,1,null) over(partition by id order by date) as lag_rate            from table)a )tab group by id,s having count(1)>=3

思路：lag（date）over as lag_date sum（case when datediff（date-lag_date）<=1 and rate>=0.5 then 1 else 0)over（）后面取大于等于3的就行