The problem of remembering the SQL time series grouping summary

Implement date interval grouping, interval less than or equal to 3s data is a group, the data source is as follows

CREATE table #tmptable (ID nvarchar (), DD date, dt datetime) Goinsert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:01 Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:02 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:03 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:04 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:05 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:06 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ') , ' 2010-1-1 00:00:07 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:09 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:11 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:12 ') Insert #tmptable values (' 1 ' , ' 2010-1-1 ', ' 2010-1-1 00:00:15 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:19 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:20 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:24 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:25 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:26 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:27 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:28 ') Insert #tmptable values (' 1 ', ' 2010-1-1 ', ' 2010-1-1 00:00:29 ') Insert #tmptable values (' 1 ', ' 2010-1-2 ', ' 2010-1-2 00:00:36 ') Insert #tmptable values (' 1 ', ' 2010-1-2 ', ' 2010-1-2 00:00:37 ') Insert #tmptable values (' 1 ', ' 2010-1-2 ') , ' 2010-1-2 00:00:48 ') Insert #tmptable values (' 1 ', ' 2010-1-2 ', ' 2010-1-2 00:00:59 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:09 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:11 ') Insert #tmptable values (' 2 ' , ' 2010-1-1 ', ' 2010-1-1 00:00:12 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:15 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:19 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:20 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:24 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:25 ') Insert #tmptable VALUES (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:26 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-100:00:27 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:28 ') Insert #tmptable values (' 2 ', ' 2010-1-1 ', ' 2010-1-1 00:00:29 ') Insert #tmptable values (' 2 ', ' 2010-1-2 ', ' 2010-1-2 00:00:36 ') Insert #tmptable values (' 2 ', ' 2010-1-2 ') , ' 2010-1-2 00:00:37 ') Insert #tmptable values (' 2 ', ' 2010-1-2 ', ' 2010-1-2 00:00:48 ') Insert #tmptable values (' 2 ', ' 2010-1-2 ', ' 2010-1-2 00:00:59 ') go

Detecting data breakpoints

; With MyList as (select Row_number () Up (ORDER by ID,DD, DT ASC) rn,t.* from  #tmptable T) SELECT A.rn,a.id,a.dd,a.dt,ca SE when b.dt are null then 0ELSE DATEDIFF (SS,B.DT,A.DT) END as secspan,case when B.DT is null or DATEDIFF (SS,B.DT,A.DT) < ; = 3 then 1 ELSE 0 END as Flag--into #TMP1 from MyList a left join MyList bon b.rn = a.rn-1 and a.id = b.ID and A.dd
   = B.DD

　

Aggregation time period

Select Id,dd,min (DT) as Sdt,max (DT) as EDT from (Select ss= (select SUM (Flags) from #TMP1 where DT <= a.dt and id = a.id and DD = a.dd), * from #TMP1 a where id= a.id and dd = a.dd) Agroup by (RN-SS), ID,DD

　

　

The problem of remembering the SQL time series grouping summary