Сравнение строк в одной таблице MS SQL Server

Question

Подскажите, как лучше сделать:

Есть датчики, когда они срабатывают - происходить запись в БД (MS SQL Server) Есть время начала срабатывания, есть время окончания срабатывания но пишет всё в один столбец (EvTime), различия между началом и концом в другом столбце (EvType) значение 1 или 0 У каждого датчика сой ИД (EqipID) Иногда не приходят сигналы о начале или конце срабатывания и в БД подряд идут несколько строк с одинаковым значением EvType Мне нужно как-то сравнивать значения EvType чтобы убирать строки с подряд идущими одинаковыми значениями и вывести в новую таблицу вида: ИД датчика | Начало события | Конец события

Я вижу это примерно следующим образом: группировать по EqipID, сортировать по времени события по убыванию сравнить строки идущие друг за другом: если EqipID равны и EvType равны - то не включать эту строку

Буду признательна Вашим советам, ссылкам или литературе;)

Answer 1

Если версия сервера 2012+ то можно так. Предполагаем, что запись в БД может не сработать как на включение, так и на выключение. Пытаемся сопоставить события. Считаем, что если датчик переходит из состояния 1 в 0 - это одно и то же событие. Во всех остальных случаях это новое событие. Вычисляем для каждой строки столбец IsNewEvent. Далее накопительным итогом по IsNewEvent вычисляем идентификатор события EventId. Таким образом мы уже можем различать разные события. Далее группируем по EqipId, EventId, чтобы схлопнуть события. Ну и после группировки вычисляем начало и конец события. А уж дальше я думаю сможете с этим сделать всё, что хотите

КОД:

DECLARE
  @Table TABLE(
    EvTime DATETIME,
    EvType BIT,
    EqipId INT
  )
INSERT @Table VALUES
  ('2020-01-01T00:00:00.000', 1, 123),
  ('2020-01-02T00:00:00.000', 1, 123),
  ('2020-01-03T00:00:00.000', 0, 123),
  ('2020-01-04T00:00:00.000', 1, 123),
  ('2020-01-01T00:00:00.000', 0, 777),
  ('2020-01-02T00:00:00.000', 0, 777),
  ('2020-01-03T00:00:00.000', 1, 777),
  ('2020-01-01T00:00:00.000', 0, 1555),
  ('2020-01-02T00:00:00.000', 1, 1555),
  ('2020-01-03T00:00:00.000', 0, 1555),
  ('2020-01-04T00:00:00.000', 1, 1555),
  ('2020-01-05T00:00:00.000', 0, 1555),
  ('2020-01-06T00:00:00.000', 1, 1555)

SELECT EqipId, EventId, 
  --вычисляем начало и конец события.
  MAX(CASE WHEN EvType = 1 THEN EvTime END)EvStart,
  MAX(CASE WHEN EvType = 0 THEN EvTime END)EvEnd
FROM(
  SELECT *, 
    --Далее накопительным итогом по IsNewEvent вычисляем идентификатор события EventId.
    SUM(IsNewEvent)OVER(PARTITION BY EqipId ORDER BY EvTime) EventId
  FROM(
    SELECT *,
      --Вычисляем для каждой строки столбец IsNewEvent
      CASE WHEN 
       --Считаем, что если датчик переходит из состояния 1 в 0 - это одно и то же событие.
       LAG(EvType)OVER(PARTITION BY EqipId ORDER by EvTime) = 1 AND EvType = 0 
      THEN 0 
      --Во всех остальных случаях это новое событие
      ELSE 1 
      END IsNewEvent
    FROM @Table
  )T
)T
--Далее группируем по EqipId, EventId, чтобы схлопнуть события.
GROUP BY EqipId, EventId
ORDER BY EqipId, EventId

Исходные данные:

EvTime	EqipId	EvType
2020-01-01 00:00:00.000	123	1
2020-01-02 00:00:00.000	123	1
2020-01-03 00:00:00.000	123	0
2020-01-04 00:00:00.000	123	1
2020-01-01 00:00:00.000	777	0
2020-01-02 00:00:00.000	777	0
2020-01-03 00:00:00.000	777	1
2020-01-01 00:00:00.000	1555	0
2020-01-02 00:00:00.000	1555	1
2020-01-03 00:00:00.000	1555	0
2020-01-04 00:00:00.000	1555	1
2020-01-05 00:00:00.000	1555	0
2020-01-06 00:00:00.000	1555	1

Результат:

EqipId	EventId	EvStart	EvEnd
123	1	2020-01-01 00:00:00.000	NULL
123	2	2020-01-02 00:00:00.000	2020-01-03 00:00:00.000
123	3	2020-01-04 00:00:00.000	NULL
777	1	NULL	2020-01-01 00:00:00.000
777	2	NULL	2020-01-02 00:00:00.000
777	3	2020-01-03 00:00:00.000	NULL
1555	1	NULL	2020-01-01 00:00:00.000
1555	2	2020-01-02 00:00:00.000	2020-01-03 00:00:00.000
1555	3	2020-01-04 00:00:00.000	2020-01-05 00:00:00.000
1555	4	2020-01-06 00:00:00.000	NULL

Можно всё тоже самое сделать и для более ранних версий сервера почти без потери производительности, особенно с правильными индексами на таблице. Так что скажите, если нужно. В прочем алгоритм тот же.

БЛОГ НА HUSL

Сравнение строк в одной таблице MS SQL Server

Ответы (1 шт):