ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Выделение интервалов в последовательности
15 сообщений из 15, страница 1 из 1
Выделение интервалов в последовательности
    #40111355
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Коллеги, приветствую!

Помогите решить задачку, не знаю, с какой стороны подступиться.
Имеется некая таблица событий:

Код: 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.
Use tempdb
go

Create table dbo.[events] (N int not null identity(1,1) primary key, id int not null, dt datetime2(0) not null)

insert into dbo.[events] (id, dt)
Values
 (1, '20200101 10:00:01')
,(1, '20200101 10:00:05')
,(1, '20200101 10:00:06')
,(1, '20200101 10:01:00')
,(1, '20200101 10:40:01')
,(2, '20200101 10:00:01')
,(2, '20200101 10:01:01')
,(2, '20200101 10:02:03')
,(2, '20200101 10:15:01')
,(2, '20200101 10:15:02')
,(3, '20200101 10:00:01')
,(1, '20200101 10:01:01')
,(1, '20200101 10:02:01')
,(1, '20200101 10:10:01')
,(1, '20200101 10:30:00')

Select * from dbo.[events]

drop table dbo.[events]


Инцидентом называется последовательность событий, относящихся к одному id, и случившихся последовательно по времени, так, что временной интервал между предыдущим и последующим событиями не превышает 10 минут.
Задача - выделить инциденты, присвоив им уникальный номер, например - перенумеровав эти инциденты последовательно.
Например, в примере выше, инцидентом будет:
Niddt112020-01-01 10:00:01212020-01-01 10:00:05312020-01-01 10:00:06412020-01-01 10:01:001212020-01-01 10:01:011312020-01-01 10:02:011412020-01-01 10:10:01
Ему, например, можно присвоить номер NN = 1

Проблема осложняется тем, что в исходной таблице ~100 млн. записей, поэтому хотелось бы производительное решение, но, для начала - сгодиться любое.

SQL2016, но сгодится любой.
Помогите, затупил...
...
Рейтинг: 0 / 0
12.11.2021, 14:44
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111369
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Не наглядный пример привел, прошу прощения.
Длительность инцидента - не 10 минут! Он может и сутки, и месяц длиться.
Событие принадлежит инциденту, если найдется хоть одно событие, связанное с этим Id, расстояние по времени до которого - меньше 10 минут.
Если это изолированное событие, до которого больше 10 минут и вверх и вниз - то это само по себе инцидент, из одного события.

Т.е. это вроде бы gap and island, но без перекрытия/примыкания.

Не могу додумать.
...
Рейтинг: 0 / 0
12.11.2021, 15:01
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111371
Фотография Konst_One
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Konst_One
Участник
lead , lag никак не прикрутить?
...
Рейтинг: 0 / 0
12.11.2021, 15:02
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111374
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
Тут где-то aleks222 давал простой алгоритм поиска групп пересекающихся событий:

начало группы - дата начала отдельного события, которая не попадает ни в один из диапазонов
конец группы - ближайшая дата окончания отдельного события, которая не попадает ни в один из диапазонов

все, что внутри этого диапазона, одна группа событий

вам нужно просто добавить сюда свой десятиминутный лаг и разделение по id
...
Рейтинг: 0 / 0
12.11.2021, 15:09
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111375
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Konst_One
lead , lag никак не прикрутить?

Так вопрос то как раз в том, как именно прикрутить :-)
...
Рейтинг: 0 / 0
12.11.2021, 15:10
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111380
Gerasimenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gerasimenko
Участник
uaggster
Konst_One
lead , lag никак не прикрутить?

Так вопрос то как раз в том, как именно прикрутить :-)


Код: sql
1.
2.
3.
4.
DECLARE @StartDate DATETIME ='20211112 10:10:10 '
DECLARE @EndDate DATETIME = GETDATE()

SELECT DATEDIFF(MINUTE, @StartDate, @EndDate)
...
Рейтинг: 0 / 0
12.11.2021, 15:16
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111381
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
msLex
Тут где-то aleks222 давал простой алгоритм поиска групп пересекающихся событий:

начало группы - дата начала отдельного события, которая не попадает ни в один из диапазонов
конец группы - ближайшая дата окончания отдельного события, которая не попадает ни в один из диапазонов

все, что внутри этого диапазона, одна группа событий

вам нужно просто добавить сюда свой десятиминутный лаг и разделение по id


Вот жеж.

У вас не интервалы, а атомарные события, и всякие "поглощения" тут не нужны



тогда все просто


если предыдущее событие дальше чем на 10 минут - это начало инцидента
если последующее событие дальше чем на 10 минут - это окончание инцидента

нумеруете все "начала" и цепляете к ним все ближайшие (с учетом ID) окончания

не забудьте только, что начало и окончание могут совпасть
...
Рейтинг: 0 / 0
12.11.2021, 15:17
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111396
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
msLex, так перенумерую то я их легко, и предыдущий N - легко прикручу.
Вопрос в том, как эти последовательности раскрутить то потом, не рекурсивно же!
...
Рейтинг: 0 / 0
12.11.2021, 15:56
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111414
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
вот поиск интервалов

если нужно наложить на исходную таблицу, заджойните

Код: 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.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
Create table #events (N int not null identity(1,1) primary key, id int not null, dt datetime2(0) not null)

insert into #events (id, dt)
Values
 (1, '20200101 10:00:01')
,(1, '20200101 10:00:05')
,(1, '20200101 10:00:06')
,(1, '20200101 10:01:00')
,(1, '20200101 10:40:01')
,(2, '20200101 10:00:01')
,(2, '20200101 10:01:01')
,(2, '20200101 10:02:03')
,(2, '20200101 10:15:01')
,(2, '20200101 10:15:02')
,(3, '20200101 10:00:01')
,(1, '20200101 10:01:01')
,(1, '20200101 10:02:01')
,(1, '20200101 10:10:01')
,(1, '20200101 10:30:00')


; with t1 as (
	select 
		prev_dt = lag(e.dt, 1) over(partition by id order by dt)
		, next_dt = lead(e.dt, 1) over(partition by id order by dt)
		, *
	from #events e
)
, t2 as (
	select 
		is_start = case when dateadd(mi, 10, prev_dt) < dt or prev_dt is null then 1 else 0 end
		, is_end = case when dateadd(mi, -10, next_dt) > dt or next_dt is null then 1 else 0 end
		, *
	from t1 
	where
		dateadd(mi, 10, prev_dt) < dt or prev_dt is null
		or dateadd(mi, -10, next_dt) > dt or next_dt is null
)
, t3 as  (
	select 
		start_dt = dt
		, end_dt = case when is_end = 1 then dt else lead(dt, 1) over(partition by id order by dt) end
		, rn = ROW_NUMBER() over(order by dt)
		, n
		, id
		, is_start
	from t2
)
select * from t3 where is_start = 1 order by rn
drop table #events
...
Рейтинг: 0 / 0
12.11.2021, 16:40
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111474
PizzaPizza
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PizzaPizza
Участник
uaggster,

Пронумеровать инциденты в таблице с 100 миллионов записей это одно. А что с этим дальше делать можно я не представляю. Положить пронумерованное в другую таблицу? Статистику посмотреть какую то? Искать то по номеру инцидента слишком тяжело.
...
Рейтинг: 0 / 0
12.11.2021, 18:28
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111584
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select
 a.*,
 cast(a.id as binary(4)) + cast(sum(case when b.dt is null then 1 else 0 end) over (order by a.id, a.dt) as binary(4))
from
 dbo.[events] a outer apply
 (select top (1) dt from dbo.[events] where id = a.id and dt < a.dt and datediff(mi, dt, a.dt) <= 10 order by dt desc) b
order by
 a.id, a.dt;

Плюс соответствующие индексы
...
Рейтинг: 0 / 0
12.11.2021, 22:44
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111594
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Так лучше
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with a as
(
 select
  N, id, dt,
  lag(dt, 1, dt) over (partition by id order by dt) as dt__prev
 from
  dbo.[events]
)
select
 N, id, dt,
 cast(id as binary(4)) + cast(sum(case when datediff(mi, dt__prev, dt) > 10 then 1 else 0 end) over (partition by id order by dt) as binary(4))
from
 a
order by
 id, dt;

При правильном индексе будет один просмотр
...
Рейтинг: 0 / 0
12.11.2021, 23:04
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111814
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
invm, спасибо, попытаюсь вкурить.

Итого, как я понимаю, сначала в СТЕ строится последовательность, запись + предыдущее значение даты/времени инцидента, а потом поверх последовательности считается сумма: если расстояние до предыдущего - меньше 10 минут, то 0, в противном случае - +1.
Таким образом все члены последовательности получат + N, где N - количество десятиминутных разрывов от начала последовательности.
ИМХО, гениально, я б не недопёр.
Я всё время вертелся вокруг rows between unbounded preceding and current row, но там феерическая дичь получалась, в т.ч. и по производительности.
Спасибо, проверю, отпишусь.
...
Рейтинг: 0 / 0
14.11.2021, 09:24
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111836
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
uaggster,

сколько возникает проблем, если неверно организовано хранение данных...
...
Рейтинг: 0 / 0
14.11.2021, 12:34
| Ответить | Цитировать | Написать  
Выделение интервалов в последовательности
    #40111929
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Владислав Колосов, тут не то, что мопед не мой, а и гараж тоже :-)
...
Рейтинг: 0 / 0
14.11.2021, 18:19
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Выделение интервалов в последовательности
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=9&tid=1684107]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 34ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 55ms
get tp. blocked users:
 2ms
others: 14ms
total:  152ms
созд. / загр.: 152ms
x
x
Закрыть


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