Блог

Ищу некоторую помощь в этой сложной группировке запросов SQL.

У меня есть ниже набор данных. Я ищу, чтобы найти разницу во времени первой (in1 = 1 amp; inputs = 9) строки и первой строки in1 = 0, затем следующей (in1 = 1 amp; inputs = 9) и следующей in1 = 0 и так далее. Пожалуйста, обратите внимание, что у меня есть несколько наборов V_Id, и внутри них у меня есть несколько групп (in1 = 1 amp; inputs = 9) amp; in1 = 0.

По сути, я хочу найти разницу во времени в две строки для каждой группы в группе V_Id, где значение меняется с 1 на 0 и от 0 до 1. Я хочу добавить разницу во времени для нескольких разделов для каждого V_Id. Желаемый вывод прилагается.

Я надеюсь, что мое объяснение имеет смысл.

Заранее спасибо.

 declare @t table(v_id int, _time datetime, inputs tinyint, in1 bit);

insert into @t(v_id, _time, inputs, in1)
values
(1243, '20210114 09:59:50', 0, 0), (1243, '20210114 09:59:58', 0, 0),
(1243, '20210114 10:00:00', 9, 1), (1243, '20210114 10:00:10', 9, 1), (1243, '20210114 10:00:20', 9, 1),
(1243, '20210114 10:00:21', 1, 0), (1243, '20210114 10:00:22', 1, 0), (1243, '20210114 10:00:25', 1, 0),
(1243, '20210114 10:00:27', 9, 1), (1243, '20210114 10:00:28', 9, 1), (1243, '20210114 10:00:29', 9, 1),
(1243, '20210114 10:00:30', 1, 0),
(1243, '20210114 10:00:31', 9, 1), (1243, '20210114 10:00:34', 9, 1), (1243, '20210114 10:00:37', 9, 1) --no following out yet
--,(1243, '20210114 10:00:40', 1, 0)
;

select v_id, sum(secdiff) as sumsecdiff
from
(
    select v_id, grpid, datediff(second, min(starttime), max(endtime)) as secdiff
    from
    (
        select *,
            --groupid
            row_number() over(partition by v_id order by _time) 
            -
            row_number() over(partition by v_id, inputs, in1 order by _time)
            as grpid,
            --starttime of group if previous is out
            case when lag(in1, 1, 0/*in1*/) over(partition by v_id order by _time) = 0 and inputs = 9 and in1 = 1 then _time end as starttime,
            ----endtime of group is the following/next out (if any)
            --case when lead(in1, 1, 1) over(partition by v_id order by _time) = 0 and inputs = 9 and in1 = 1 then lead(_time) over(partition by v_id order by _time) end as endtime

            --endtime of group is the following/next out, if last row is in1=1 (and 9) then getdate() /*it*/ accounts as out
            case when lead(in1, 1, 0) over(partition by v_id order by _time) = 0 and inputs = 9 and in1 = 1 then isnull(lead(_time) over(partition by v_id order by _time), getdate() /*_time*/) end as endtime

        from @t
    ) as t
    where inputs = 9
    and in1 = 1
    group by v_id, grpid
) as src
group by v_id;