Суммируйте промежуток времени, если последовательные отметки времени совпадают в течение 10 минут

Question

Я использую запрос ниже, чтобы найти нулевые значения столбца и получить время начала и время окончания значений NULL, используя запрос ниже для примерно 30 000 строк

    SELECT
    yt1.[timestamp] AS StartTime,
    MIN(yt2.[timestamp]) AS EndTime,
    DATEDIFF(MINUTE, yt1.[timestamp], MIN(yt2.[timestamp])) AS DifferenceInMinutes
    FROM
    Sheet1$ yt1
    LEFT JOIN Sheet1$ yt2 ON yt1.[timestamp] < yt2.[timestamp]
    WHERE
    yt1.TWSPD IS NULL
    GROUP BY yt1.[timestamp]

Выход

Start time                     Endtime                DifferenceInMinutes
2012-05-18 20:47:03.000    2012-05-18 20:57:04.000      10
2012-05-18 20:57:04.000    2012-05-18 21:07:04.000      10
2012-05-21 18:25:26.000    2012-05-21 18:35:26.000      10
2012-06-07 17:36:28.000    2012-06-07 17:46:28.000      10
2012-06-07 17:46:28.000    2012-06-07 17:56:28.000      10
2012-06-07 17:56:28.000    2012-06-07 18:06:28.000      10

И, например, теперь мне нужен вывод как (убрал некоторые строки для лучшего отображения)

Start time                     Endtime                DifferenceInMinutes
2012-05-18 20:47:03.000    2012-05-18 21:07:04.000      20
2012-05-21 18:25:26.000    2012-05-21 18:35:26.000      10
2012-06-07 17:36:28.000    2012-06-07 18:06:28.000      30

Временная метка указана для каждых 10 минут, если необходимо добавить нулевые значения для последовательных 10-минутных интервалов времени, а время начала и время окончания должны отображаться как от первого нулевого до последнего нулевого значения последовательной временной метки. Надеюсь, вопрос ясен. Пожалуйста, дайте мне знать, если мне не ясно. Пожалуйста помоги

Answer 1

 SELECT
    yt1.[timestamp] AS StartTime,
    MIN(yt2.[timestamp]) AS EndTime,
    DATEDIFF(MINUTE, yt1.[timestamp], MIN(yt2.[timestamp])) AS DifferenceInMinutes
    into #tmp1
    FROM
    Sheet1$ yt1
    LEFT JOIN Sheet1$ yt2 ON yt1.[timestamp] < yt2.[timestamp]
    WHERE
    yt1.TWSPD IS NULL
    GROUP BY yt1.[timestamp]


Select t1.* 
into #tmp2
from #tmp1 t1
left join #tmp1 t2 on t1.Starttime=t2.Endtime
where t2.Endtime is null

Declare @rcn int
Select @rcn=1
While @rcn>0
    begin
       Update #tmp2 set #tmp2.Endtime=t.endTime,#tmp2.DifferenceInMinutes=#tmp2.DifferenceInMinutes+t.DifferenceInMinutes
       from #tmp1 t
       where t.Starttime=#tmp2.Endtime
       select @rcn=@@Rowcount
    end


select * from #tmp2

Drop Table #tmp1
Drop Table #tmp2

Answer 2

Если вы хотите запросить исходную таблицу для группировки выходных данных - вы можете сделать это:

;with
CTE_start
as
(
    select T.timestamp, row_number() over(order by T.timestamp) as RowNum
    from temp1 as T
    where
        not exists 
        (
            select * 
            from temp1 as TT
            where TT.timestamp < T.timestamp and TT.timestamp >= dateadd(mi, -11, T.timestamp)
        )  
),
CTE_end
as
(
    select T.timestamp, row_number() over(order by T.timestamp) as RowNum
    from temp1 as T
    where
        not exists 
        (
            select * 
            from temp1 as TT
            where TT.timestamp > T.timestamp and TT.timestamp <= dateadd(mi, 11, T.timestamp)
        )  
)
select
    s.timestamp as [Start time],
    e.timestamp as [End time],
    datediff(mi, s.timestamp, e.timestamp) as [DifferenceInMinutes]
from CTE_start as s
    inner join CTE_end as e on e.RowNum = s.RowNum

Пример SQL FIDDLE

Еще один хороший вариант, но вы должны скопировать свои данные во временную (переменную) таблицу

declare @tmp table (timestamp datetime, RowNum int primary key)

insert into @tmp
select T.timestamp, row_number() over(order by T.timestamp) as RowNum
from temp1 as T

;with CTE
as
(
    select T.timestamp, T.RowNum, 1 as GroupNum
    from @tmp as T
    where RowNum = 1
    union all
    select
        T.timestamp, T.RowNum,
        C.GroupNum + case when datediff(mi, C.timestamp, T.timestamp) >= 11 then 1 else 0 end
    from @tmp as T
        inner join CTE as C on C.RowNum + 1 = T.RowNum
)
select
    min(C.timestamp) as [Start time],
    max(C.timestamp) as [End time],
    datediff(mi, min(C.timestamp), max(C.timestamp)) as [DifferenceInMinutes]
from CTE as C
group by C.GroupNum

Пример SQL FIDDLE