Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вычисление разницы между значениями идущими подряд  [new]
relizx
Member

Откуда:
Сообщений: 2
Добрый день!
Прошу помощи с решением задачи.
Есть таблица вида:
EventDoorDateTime
Срабатывание датчика11-22019-10-31 08:40:18:453
Срабатывание датчика12-12019-10-31 08:41:12:451
Срабатывание датчика12-12019-10-31 08:43:54:211
Снят с охраны12-12019-10-31 09:15:17:111
Срабатывание датчика13-12019-10-31 09:21:22:222
Снят с охраны13-12019-10-31 09:22:34:531
Срабатывание датчика14-52019-10-31 09:22:54:213
Срабатывание датчика14-52019-10-31 09:23:37:513
Снят с охраны14-52019-10-31 09:23:39:444
Снят с охраны14-62019-10-31 09:25:18:351
Срабатывание датчика14-72019-10-31 09:25:31:415
Снят с охраны14-82019-10-31 09:26:34:512

Необходимо:
Брать событие "Снят с охраны", которое следует за предыдущим перед ним событием "Срабатывание датчика" (т.е., может быть подряд несколько событий "срабатывание датчика", но необходимо брать последнее идущее по времени перед значением "Снят с охраны")
Далее необходимо сравнивать время этих двух событий (из колонки "DateTime") и вывести разницу.
Для лучшего понимания, в таблице выделил цветом те события, между которыми необходимо вычислять разницу.

Читал про функции "LAG" и "LEAD", но так и не сообразил, как их здесь можно применить.
12 ноя 19, 10:51    [22014199]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы между значениями идущими подряд  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
Ну типа
WITH cte AS ( SELECT *, 
                     LAG(Event) OVER (PARTITION BY Door ORDER BY DateTime ASC) prev_Event,
                     LAG(DateTime) OVER (PARTITION BY Door ORDER BY DateTime ASC) prev_DateTime 
              FROM table )
SELECT *
FROM cte
WHERE Event = 'Снят с охраны'
  AND prev_Event = 'Срабатывание датчика'
А дальше уже посчитаешь, что надо...
12 ноя 19, 11:21    [22014234]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы между значениями идущими подряд  [new]
court
Member

Откуда:
Сообщений: 2021
declare @t table (Event	varchar(100), Door varchar(10), [DateTime] datetime2)
insert into @t values
('Срабатывание датчика',	'11-2',	'2019-10-31 08:40:18:453'),
('Срабатывание датчика',	'12-1',	'2019-10-31 08:41:12:451'),
('Срабатывание датчика',	'12-1',	'2019-10-31 08:43:54:211'),
('Снят с охраны',	'12-1',	'2019-10-31 09:15:17:111'),
('Срабатывание датчика',	'13-1',	'2019-10-31 09:21:22:222'),
('Снят с охраны',	'13-1',	'2019-10-31 09:22:34:531'),
('Срабатывание датчика',	'14-5',	'2019-10-31 09:22:54:213'),
('Срабатывание датчика',	'14-5',	'2019-10-31 09:23:37:513'),
('Снят с охраны',	'14-5',	'2019-10-31 09:23:39:444'),
('Снят с охраны',	'14-6',	'2019-10-31 09:25:18:351'),
('Срабатывание датчика',	'14-7',	'2019-10-31 09:25:31:415'),
('Снят с охраны',	'14-8',	'2019-10-31 09:26:34:512')

;with cte as (
	select * 
		,case when Event='Срабатывание датчика' then lead(case when Event='Снят с охраны' then [DateTime] end)over(partition by Door order by [DateTime]) end as DT_EventClose
	from @t 
)
select 
	Door
	,[DateTime] as DT_Alarm
	,DT_EventClose
	,datediff(ms, [DateTime], DT_EventClose) as Delta_ms
from cte 
--where DT_EventClose is not null


DoorDT_AlarmDT_EventCloseDelta_ms
11-22019-10-31 08:40:18.4530000NULLNULL
12-12019-10-31 08:41:12.4510000NULLNULL
12-12019-10-31 08:43:54.21100002019-10-31 09:15:17.11100001882900
12-12019-10-31 09:15:17.1110000NULLNULL
13-12019-10-31 09:21:22.22200002019-10-31 09:22:34.531000072309
13-12019-10-31 09:22:34.5310000NULLNULL
14-52019-10-31 09:22:54.2130000NULLNULL
14-52019-10-31 09:23:37.51300002019-10-31 09:23:39.44400001931
14-52019-10-31 09:23:39.4440000NULLNULL
14-62019-10-31 09:25:18.3510000NULLNULL
14-72019-10-31 09:25:31.4150000NULLNULL
14-82019-10-31 09:26:34.5120000NULLNULL
12 ноя 19, 11:23    [22014238]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы между значениями идущими подряд  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
WITH Data AS
(
 SELECT * FROM(VALUES
 (N'Срабатывание датчика','11-2','2019-10-31T08:40:18.453')
,(N'Срабатывание датчика','12-1','2019-10-31T08:41:12.451')
,(N'Срабатывание датчика','12-1','2019-10-31T08:43:54.211')
,(N'Снят с охраны','12-1','2019-10-31T09:15:17.111')
,(N'Срабатывание датчика','13-1','2019-10-31T09:21:22.222')
,(N'Снят с охраны','13-1','2019-10-31T09:22:34.531')
,(N'Срабатывание датчика','14-5','2019-10-31T09:22:54.213')
,(N'Срабатывание датчика','14-5','2019-10-31T09:23:37.513')
,(N'Снят с охраны','14-5','2019-10-31T09:23:39.444')
,(N'Снят с охраны','14-6','2019-10-31T09:25:18.351')
,(N'Срабатывание датчика','14-7','2019-10-31T09:25:31.415')
,(N'Снят с охраны','14-8','2019-10-31T09:26:34.512')
)T([Event],[Door],[DateTime])
)
SELECT *
,Period=CASE
WHEN [Event]=N'Снят с охраны' AND LAG([Event])OVER(ORDER BY[DateTime])=N'Срабатывание датчика'
THEN DATEDIFF(MILLISECOND,LAG([DateTime])OVER(ORDER BY[DateTime]),[DateTime])
END
FROM Data
ORDER BY [DateTime];
12 ноя 19, 11:39    [22014261]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы между значениями идущими подряд  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
court,

хотел сказать, что cte лишнее.
Потому что оконные функции вполне себе допустимы в списке SELECT
12 ноя 19, 11:42    [22014267]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы между значениями идущими подряд  [new]
relizx
Member

Откуда:
Сообщений: 2
Всем огромное спасибо! :)
P.s. Скрипт iap кстати отрабатывает быстрее всех. (Может я правда накосячил где-то, когда добавил DATEDIFF в скрипте Akina)
12 ноя 19, 14:07    [22014480]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы между значениями идущими подряд  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
iap
хотел сказать, что cte лишнее.
Потому что оконные функции вполне себе допустимы в списке SELECT
Исходная формулировка как бы предполагала, что не соответствующие условию "снятие после срабатывания" записи выводить не требуется - а вот тут без CTE или подзапроса никуда.
12 ноя 19, 14:25    [22014504]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить