Как сделать запрос к базе данных SQL Server, чтобы получить сумму временных разниц, но только если они отличаются менее чем на 2 минуты

У меня есть следующие запрашиваемые данные:

SELECT * 
FROM MYTABLE 
WHERE tagid = '65'

Выход:

   tagid    floatvalue  t_stamp
   -------------------------------
    65  25.51477051 1455897455214
    65  35.71407318 1455897485215
    65  36.05856323 1455897515215
    65  35.72781372 1455897545214
    65  35.99771118 1455897575215
    65  35.87993622 1455897605215
    65  36.23326111 1455897665215
    65  35.8652153  1455897695215
    65  35.73075485 1455897725216
    65  35.94765472 1455897785216
    65  36.36379242 1455897815217
    65  35.93685913 1455897845216
    65  36.64154816 1455898025219
    65  36.44329071 1455898055218
    65  36.07524872 1455898085219
    65  36.40992355 1455898115217
    65  38.13336182 1455898145217

t_stamp колонка - это большой инт Unix time * 1000,

Эти данные регистрируются каждые ~30 секунд (30 000), если машина работает. Я пытаюсь запросить это для суммы временных различий, если они меньше чем две минуты (120 000) от строки перед этим. Если оно больше двух минут, я предполагаю, что машина была выключена, и в этом ряду будет новое время начала следующей суммы.

Моя цель здесь - получить сумму общего времени выполнения, используя метки времени.

Я в полной растерянности, с чего начать. Я с трудом пытался придать этому объяснению хоть какой-то смысл, тем более вы, ребята, и извинитесь, если я все испортил.

Источник

2 ответа

Решение

Если у вас нет lag() Есть и другие способы получить предыдущую временную метку.

;with step1 as (
    select
        t_stamp,
        case
            when t_stamp - lag(t_stamp) over (partition by tagid order by t_stamp) > 120000
            then 1 else 0
        end as brk
    from mytable
), step2 as (
    select t_stamp, sum(brk) over (partition by tagid order by t_stamp) as grp,
    from step1
)
select
    grp, min(t_stamp) as start_time, max(t_stamp) as end_time,
    max(t_stamp) - min(t_stamp) as total_time
from step2
group by tagid, grp;

Это довольно типичная проблема под заголовком "Разрывы и острова". Вы найдете много примеров, похожих на ваш вопрос.

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

Редактировать:

Вот быстрая разбивка логики.

Шаг 1 сравнивает каждый штамп с предыдущим значением в порядке времени, используя lag, Когда он находит зазор, превышающий пороговое значение, он помечается как 1. Все остальные получают нули (также будет работать ноль). Это фактически отмечает начало нового блока, также называемого "grp".

Шаг 2 вычисляет промежуточную сумму в том же порядке. Таким образом, промежуточная сумма изменяется только каждый раз, когда начинается новый блок, и, следовательно, каждая строка в блоке получает одно и то же значение. Это значение используется в group by и тогда разница во времени вычисляется как промежуток между минимальной и максимальной отметками времени.

Источник

Попробуйте курсор.

declare @sum bigint
declare @t_stamp bigint
declare @last bigint
declare @diff bigint
select @sum = 0
declare MyCursor cursor for select t_stamp from mytable where tagid=65
open MyCursor


fetch next from MyCursor into @t_stamp 
while @@fetch_Status = 0
begin

   if (not (@last is null))
   begin
       select @diff = @t_stamp - @last

       if (@diff < 120000)
       begin
           select @sum = @sum + @diff
       end 
   end
   select @last = @t_stamp

   fetch next from MyCursor into @t_stamp 
end
close MyCursor
deallocate MyCursor

print @sum
Источник
Другие вопросы по тегам