ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вычисление разницы между значениями идущими подряд
7 сообщений из 7, страница 1 из 1
Вычисление разницы между значениями идущими подряд
    #39887916
relizx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
relizx
Гость
Добрый день!
Прошу помощи с решением задачи.
Есть таблица вида:
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", но так и не сообразил, как их здесь можно применить.
...
Рейтинг: 0 / 0
12.11.2019, 10:51
| Ответить | Цитировать | Написать  
Вычисление разницы между значениями идущими подряд
    #39887940
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Ну типа
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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 = 'Срабатывание датчика'

А дальше уже посчитаешь, что надо...
...
Рейтинг: 0 / 0
12.11.2019, 11:21
| Ответить | Цитировать | Написать  
Вычисление разницы между значениями идущими подряд
    #39887942
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
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_ms11-22019-10-31 08:40:18.4530000NULLNULL12-12019-10-31 08:41:12.4510000NULLNULL12-12019-10-31 08:43:54.21100002019-10-31 09:15:17.1110000188290012-12019-10-31 09:15:17.1110000NULLNULL13-12019-10-31 09:21:22.22200002019-10-31 09:22:34.53100007230913-12019-10-31 09:22:34.5310000NULLNULL14-52019-10-31 09:22:54.2130000NULLNULL14-52019-10-31 09:23:37.51300002019-10-31 09:23:39.4440000193114-52019-10-31 09:23:39.4440000NULLNULL14-62019-10-31 09:25:18.3510000NULLNULL14-72019-10-31 09:25:31.4150000NULLNULL14-82019-10-31 09:26:34.5120000NULLNULL
...
Рейтинг: 0 / 0
12.11.2019, 11:23
| Ответить | Цитировать | Написать  
Вычисление разницы между значениями идущими подряд
    #39887957
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
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];
...
Рейтинг: 0 / 0
12.11.2019, 11:39
| Ответить | Цитировать | Написать  
Вычисление разницы между значениями идущими подряд
    #39887961
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
court,

хотел сказать, что cte лишнее.
Потому что оконные функции вполне себе допустимы в списке SELECT
...
Рейтинг: 0 / 0
12.11.2019, 11:42
| Ответить | Цитировать | Написать  
Вычисление разницы между значениями идущими подряд
    #39888061
relizx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
relizx
Гость
Всем огромное спасибо! :)
P.s. Скрипт iap кстати отрабатывает быстрее всех. (Может я правда накосячил где-то, когда добавил DATEDIFF в скрипте Akina)
...
Рейтинг: 0 / 0
12.11.2019, 14:07
| Ответить | Цитировать | Написать  
Вычисление разницы между значениями идущими подряд
    #39888075
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
iap
хотел сказать, что cte лишнее.
Потому что оконные функции вполне себе допустимы в списке SELECT
Исходная формулировка как бы предполагала, что не соответствующие условию "снятие после срабатывания" записи выводить не требуется - а вот тут без CTE или подзапроса никуда.
...
Рейтинг: 0 / 0
12.11.2019, 14:25
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вычисление разницы между значениями идущими подряд
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]