根据sql server中的相关值进行数据分组

根据sql server中的相关值进行数据分组

由于您的问题发生了很大的变化，因此这里是新问题的新答案，我只包含了代码部分。

declare @t table(time datetime, value int)declare @variation floatset @variation = 2set nocount oninsert @t values('2010-01-01 00:15',3)insert @t values('2010-01-01 00:30',2)insert @t values('2010-01-01 00:45',4)insert @t values('2010-01-01 01:00',5)insert @t values('2010-01-01 01:15',1)insert @t values('2010-01-01 01:30',3)insert @t values('2010-01-01 01:45',4)insert @t values('2010-01-01 02:00',52)insert @t values('2010-01-01 02:15',5)insert @t values('2010-01-01 02:30',52)insert @t values('2010-01-01 02:45',54)insert @t values('2010-01-01 03:00',55)insert @t values('2010-01-01 03:15',3)insert @t values('2010-01-01 03:30',2)insert @t values('2010-01-01 03:45',3)insert @t values('2010-01-01 04:00',5)declare @result table(mintime datetime, maxtime datetime)a:delete @result;with t as(select *, rn = row_number() over(order by time), log(value) lv from @t where datediff(day, time, '2010-01-01') = 0), a as(select time, lv, rn, 0 grp from t where rn = 1union allselect t1.time, a.lv, t1.rn, case when exists (select 1 from t t2 where t1.rn between rn + 1 and rn + 3 and lv between t1.lv - @variation and t1.lv +@variation) then grp else grp + 1 endfrom t t1 join a on t1.rn = a.rn +1)insert @resultselect min(time), max(time) from a group by grpif @@rowcount > 10 begin     set @variation=@variation + .5     goto a endselect * from @result

结果：

mintime          maxtime2010-01-01 00:15:00.000     2010-01-01 01:45:00.0002010-01-01 02:00:00.000     2010-01-01 03:00:00.0002010-01-01 03:15:00.000     2010-01-01 04:00:00.000