SQL Server 2012 - вычисление времени нахождения в состоянии на основе изменения состояния

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

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

Моя структура таблицы упорядочена по описанию:

+----------+-------------------------+
| state_id |         t_stamp         |
+----------+-------------------------+
|        0 | 2020-06-01 10:44:06.663 |
|        2 | 2020-06-01 10:43:56.660 |
|        0 | 2020-06-01 10:43:06.653 |
|        2 | 2020-06-01 10:42:56.653 |
|        0 | 2020-06-01 10:41:36.643 |
|        3 | 2020-06-01 10:41:26.640 |
|        0 | 2020-06-01 10:41:16.640 |
|        2 | 2020-06-01 10:40:56.637 |
|        0 | 2020-06-01 10:40:06.630 |
|        3 | 2020-06-01 10:39:56.630 |
+----------+-------------------------+

К чему я пытаюсь добраться:

+----------+------------------+
| state_id | duration_seconds |
+----------+------------------+
|        2 |               10 |
|        0 |               50 |
+----------+------------------+

sql-server date tsql window-functions gaps-and-islands
person Mason Younger    schedule 01.06.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Можно использовать оконные функции, затем агрегирование:

select 
    state_id, 
    sum(datediff(second, t_stamp, lead_t_stamp)) duration_second
from  ( 
    select
        t.*,
        lead(t_stamp) over(order by t_stamp) lead_t_stamp
    from mytable t
) t
where lead_t_stamp is not null
group by state_id
order by state_id

Эта демонстрация DB Fiddle с вашими данными uample возвращает:

state_id | duration_second
-------: | --------------:
       0 |             190
       2 |              40
       3 |              20
person GMB    schedule 01.06.2020