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

Откуда:
Сообщений: 54
Доброго времени суток!
Есть насущная проблема, которую никак не могу решить.
Вот табличка с событиями разного приоритета:
DECLARE @TAB TABLE ([Priority] [nvarchar](50),
[EventName] [nvarchar](50),
[STime] [datetime],
[ETime] [datetime])
insert into @TAB values
('1 приоритет', 'Событие 1', '2013-12-01 06:00', '2013-12-01 10:00'),
('2 приоритет', 'Событие 2', '2013-12-01 08:00', '2013-12-01 12:00'),
('3 приоритет', 'Событие 3', '2013-12-01 09:00', '2013-12-01 15:00'),
('1 приоритет', 'Событие 4', '2013-12-01 13:00', '2013-12-01 14:00')

select * from @TAB;

В готовом виде выглядит так
1 приоритет Событие 1 2013-12-01 06:00:00.000 2013-12-01 10:00:00.000
2 приоритет Событие 2 2013-12-01 08:00:00.000 2013-12-01 12:00:00.000
3 приоритет Событие 3 2013-12-01 09:00:00.000 2013-12-01 15:00:00.000
1 приоритет Событие 4 2013-12-01 13:00:00.000 2013-12-01 14:00:00.000

Суть в том, что нужно получить все события, которые не перекрываются более высоким приоритетом:
1 приоритет Событие 1 2013-12-01 06:00:00.000 2013-12-01 10:00:00.000 (не обрезается, так как высший приоритет)
2 приоритет Событие 2 2013-12-01 10:00:00.000 2013-12-01 12:00:00.000 (обрезается событием 1 до 10:00)
3 приоритет Событие 3 2013-12-01 12:00:00.000 2013-12-01 13:00:00.000 (обрезается событием 2 до 12:00 и событием 4 до 13:00)
3 приоритет Событие 3 2013-12-01 14:00:00.000 2013-12-01 15:00:00.000 (обрезается событием 4 до 14:00)
1 приоритет Событие 4 2013-12-01 13:00:00.000 2013-12-01 14:00:00.000 (не обрезается, так как высший приоритет)

Можно ли это сделать с помощью запроса или применять обработку в хранимой процедуре.
Опасаюсь, что при реализации в хранимой процедуре и наличия записей в таблице около 10000 будет неприемлемое время обработки для отчета.
Буду благодарен за любую помощь. Заранее спасибо.
5 дек 13, 11:58    [15244985]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
select * 
from @TAB a
where not exists(select * from @TAB b where b.Stime<a.ETime and a.Stime<b.ETime and b.Priority<a.Priority)

А почему Priority вдруг nvarchar(50), а не int?
5 дек 13, 22:29    [15249484]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Virtuoz
Member

Откуда: Украина
Сообщений: 307
2 Serg_77m:

Ваш запрос выдаёт некорректный результат!!
6 дек 13, 13:17    [15252499]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31910
Virtuoz
Ваш запрос выдаёт некорректный результат!!
Корректный.
Вы его хоть выполняли?
6 дек 13, 13:29    [15252629]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Serg_77m
select * 
from @TAB a
where not exists(select * from @TAB b where b.Stime<a.ETime and a.Stime<b.ETime and b.Priority<a.Priority)

А почему Priority вдруг nvarchar(50), а не int?


Да чисто для примера... конечно нужно сделать int.
6 дек 13, 13:31    [15252644]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Gizmor
Member

Откуда:
Сообщений: 54
В общем на примере двух уровней получается следующее
DECLARE @TAB TABLE ([Priority] [nvarchar](50),
[Equipment] [nvarchar] (50),
[EventName] [nvarchar](50),
[STime] [datetime],
[ETime] [datetime])
insert into @TAB values
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 06:00', '2013-12-01 10:00'),
('2 приоритет', 'Оборудование 1', 'Событие 2', '2013-12-01 04:00', '2013-12-01 12:00'),
('2 приоритет', 'Оборудование 1', 'Событие 3', '2013-12-01 13:00', '2013-12-01 16:00'),
('1 приоритет', 'Оборудование 1', 'Событие 4', '2013-12-01 14:00', '2013-12-01 15:00'),
('2 приоритет', 'Оборудование 2', 'Событие 5', '2013-12-01 07:00', '2013-12-01 11:00'),
('1 приоритет', 'Оборудование 2', 'Событие 6', '2013-12-01 08:00', '2013-12-01 12:00'),
('1 приоритет', 'Оборудование 2', 'Событие 7', '2013-12-01 13:00', '2013-12-01 17:00'),
('2 приоритет', 'Оборудование 2', 'Событие 8', '2013-12-01 14:00', '2013-12-01 16:00')

--SELECT * FROM @TAB

--выбираем события 2 приоритета для их сопоставления с событиями 1 приоритета
SELECT
[SecondPriorityTable].[Priority],
[SecondPriorityTable].[Equipment],
[SecondPriorityTable].[EventName],
[FirstPriorityTable].[ETime] AS [STime],
[SecondPriorityTable].[ETime] AS [ETime]
FROM
@TAB AS [SecondPriorityTable]
--добавляем события 1 приоритета, которые частично перекрывают события 2 приоритета слева
INNER JOIN
(SELECT
Priority,
Equipment,
EventName,
STime,
ETime
FROM
@TAB
WHERE
Priority = '1 приоритет') AS [FirstPriorityTable]
ON [SecondPriorityTable].[Equipment] = [FirstPriorityTable].[Equipment] AND
([FirstPriorityTable].[ETime] < [SecondPriorityTable].[ETime] AND [FirstPriorityTable].[ETime] > [SecondPriorityTable].[STime])
WHERE
[SecondPriorityTable].[Priority] = '2 приоритет'
UNION
--выбираем события 2 приоритета для их сопоставления с событиями 1 приоритета
SELECT
[SecondPriorityTable].[Priority],
[SecondPriorityTable].[Equipment],
[SecondPriorityTable].[EventName],
[SecondPriorityTable].[STime] AS [STime],
[FirstPriorityTable].[STime] AS [ETime]
FROM
@TAB AS [SecondPriorityTable]
--добавляем события 1 приоритета, которые частично перекрывают события 2 приоритета справа
INNER JOIN
(SELECT
Priority,
Equipment,
EventName,
STime,
ETime
FROM
@TAB
WHERE
Priority = '1 приоритет') AS [FirstPriorityTable]
ON [SecondPriorityTable].[Equipment] = [FirstPriorityTable].[Equipment] AND
([FirstPriorityTable].[STime] > [SecondPriorityTable].[STime] AND [FirstPriorityTable].[STime] < [SecondPriorityTable].[ETime])
WHERE
[SecondPriorityTable].[Priority] = '2 приоритет'

Третий уровень хочу добавить по аналогии к составной таблице.
Помогите упростить запрос. Результат уже корректный.
6 дек 13, 13:34    [15252675]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Virtuoz
Member

Откуда: Украина
Сообщений: 307
автор
Корректный.
Вы его хоть выполняли?


Да, выполнял.

Если считать условие задачи таким:
автор
Суть в том, что нужно получить все события, которые не перекрываются более высоким приоритетом


то - да, корректный.
Но, как я понимаю, Гизмору нужны были события, с указанием перодов, в течении которых его не перекрывает более высокий приоритет.
6 дек 13, 13:37    [15252710]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Добрый Э - Эх
Guest
Репрезентативный набор тестовых данных и желаемый результат на них приведи. Количество уровней приоритета ограничено или может быть произвольным? Ну и версию сервера за комплект
6 дек 13, 13:38    [15252721]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
baracs
Member

Откуда: Москва
Сообщений: 7215
alexeyvg
Virtuoz
Ваш запрос выдаёт некорректный результат!!
Корректный.
Вы его хоть выполняли?
Мне тоже показалось, что ТС хочет получить не список событий, которые наверху, а как бы проекцию перекрывающихся временных интервалов.
Т.е. в промежутках между интервалами высокого приоритета должны "проглядывать" интервалы с низким приоритетом...
6 дек 13, 13:43    [15252785]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Да, все правильно. Нужна проекция наверх.
Количество уровней 3. Версия сервера 2008.
Ситуация усугубляется, взял вот такую таблицу
DECLARE @TAB TABLE ([Priority] [nvarchar](50),
[Equipment] [nvarchar] (50),
[EventName] [nvarchar](50),
[STime] [datetime],
[ETime] [datetime])
insert into @TAB values
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 06:00', '2013-12-01 10:00'),
('1 приоритет', 'Оборудование 1', 'Событие 2', '2013-12-01 13:00', '2013-12-01 15:00'),
('2 приоритет', 'Оборудование 1', 'Событие 3', '2013-12-01 05:00', '2013-12-01 16:00'),
('3 приоритет', 'Оборудование 1', 'Событие 4', '2013-12-01 12:00', '2013-12-01 19:00'),
('1 приоритет', 'Оборудование 1', 'Событие 5', '2013-12-01 11:00', '2013-12-01 12:00'),
('1 приоритет', 'Оборудование 1', 'Событие 6', '2013-12-01 17:00', '2013-12-01 20:00')

Получается событие 3 должно быть разбито на 4 события, так как его закрывают 1, 2 и 5 событие. =((
Мое предыдущее решение этого не обеспечивает.
Чувствую пахнет рекурсивным запросом......
6 дек 13, 14:42    [15253329]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Virtuoz
Member

Откуда: Украина
Сообщений: 307
автор
Чувствую пахнет рекурсивным запросом..


Ну меня тут более опытные коллеги за рекурсивные запросы ругают...
Если у Вас количество приоритетов всё же ограничено 4-мя и дальше их количество расти не будет, то напишите процедуру.
6 дек 13, 14:58    [15253502]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
o-o
Guest
Virtuoz
автор
Чувствую пахнет рекурсивным запросом..


Ну меня тут более опытные коллеги за рекурсивные запросы ругают...


если это тонкий намек на меня, то меня вчера неправильно поняли
когда МОЖНО решить без рекурсии, то и не надо ее прикручивать, ибо тормознее не бывает.
а когда без нее не обойтись, то...для того ее и реализовали, чтоб использовать!
а когда 10 строк, то и вообще ФИОЛЕТОВО, за 0 милисекунд оно или за 74.
6 дек 13, 15:17    [15253718]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Virtuoz,

тогда может быть, так?
ID у таблички есть?
Временная таблица тут для оптимизации, её можно заменить на CTE, но будет медленнее.

DECLARE @tmp TABLE ([FLAG] smallint,[ID] int,[STime] datetime,primary key (ID,STime))

insert into @tmp (ID,STime,FLAG)
select ID,STime,sum(FLAG) as FLAG
from (
  select 1 as [FLAG],[ID],[STime]
  from @tab
  union all
  select -1 as [FLAG],[ID],[ETime]
  from @TAB
  union all
  select -1 as FLAG,a.ID,
    case when a.STime>b.STime then a.STime else b.STime end
  from @TAB a
    inner join @TAB b on a.STime<b.ETime and b.STime<a.ETime
	  and b.Priority<a.Priority 
  union all
  select 1 as FLAG,a.ID,
	case when a.ETime<b.ETime then a.ETime else b.ETime end
  from @TAB a
    inner join @TAB b on a.STime<b.ETime and b.STime<a.ETime
	  and b.Priority<a.Priority 
) a
group by ID,STime
having sum(FLAG)<>0

select c.ID,c.Priority,c.EventName,a.STime,b.ETime
from @tmp a
  cross apply (
    select top 1 b.STime as ETime,b.FLAG
	from @tmp b
	where b.ID=a.ID and b.STime>a.STime
	order by b.STime
  ) b
  inner join @TAB c on c.ID=a.ID
where a.FLAG>0 and b.FLAG<0
6 дек 13, 16:35    [15254470]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Забыл добавить, что в таблицу @TAB надо добавить поле
[ID] int identity primary key
6 дек 13, 16:38    [15254491]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Virtuoz
Member

Откуда: Украина
Сообщений: 307
Но только результат работы мягко говоря "не читаемый"... :)

Я так и не понял как для события 3 получить из него нужные интервалы....

1 1 2013-12-01 06:00:00.000
-1 1 2013-12-01 10:00:00.000
1 2 2013-12-01 10:00:00.000
-1 2 2013-12-01 12:00:00.000
-1 3 2013-12-01 09:00:00.000
1 3 2013-12-01 10:00:00.000
1 3 2013-12-01 12:00:00.000
-1 3 2013-12-01 13:00:00.000
1 3 2013-12-01 14:00:00.000
-1 3 2013-12-01 15:00:00.000
1 4 2013-12-01 13:00:00.000
-1 4 2013-12-01 14:00:00.000
6 дек 13, 18:06    [15255166]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Virtuoz,

это откуда выборка? Если из таблицы @tmp, то там ниже есть select, который выводит в читаемом виде.
6 дек 13, 18:09    [15255181]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Virtuoz
Member

Откуда: Украина
Сообщений: 307
Слона то я и не заметил :):)
6 дек 13, 19:54    [15255623]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Прошу прощения, но последний вариант наботает некорректно на следующих данных:
DECLARE @TAB TABLE ([Priority] [nvarchar](50),
[Equipment] [nvarchar] (50),
[EventName] [nvarchar](50),
[STime] [datetime],
[ETime] [datetime])
insert into @TAB values
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 10:00', '2013-12-01 13:00'),
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 10:00', '2013-12-01 13:00'),
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 14:00', '2013-12-01 18:00'),
('2 приоритет', 'Оборудование 1', 'Событие 3', '2013-12-01 11:00', '2013-12-01 16:00'),
('3 приоритет', 'Оборудование 1', 'Событие 4', '2013-12-01 12:00', '2013-12-01 19:00')
6 дек 13, 21:31    [15255950]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Попытка №3.
DECLARE @TAB TABLE ([Priority] [nvarchar](50),
[Equipment] [nvarchar] (50),
[EventName] [nvarchar](50),
[STime] [datetime],
[ETime] [datetime])
insert into @TAB values
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 10:00', '2013-12-01 13:00'),
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 10:00', '2013-12-01 13:00'),
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 14:00', '2013-12-01 18:00'),
('2 приоритет', 'Оборудование 1', 'Событие 3', '2013-12-01 11:00', '2013-12-01 16:00'),
('3 приоритет', 'Оборудование 1', 'Событие 4', '2013-12-01 12:00', '2013-12-01 19:00')


DECLARE @tmp TABLE (
  FLAG smallint,Equipment nvarchar(50),EventName nvarchar(50),Priority nvarchar(50),
  STime datetime,
  primary key (Equipment,EventName,Priority,STime)
)

insert into @tmp (Equipment,EventName,Priority,STime,FLAG)
select Equipment,EventName,Priority,STime,sum(FLAG) as FLAG
from (
  select 1 as FLAG,Equipment,EventName,Priority,STime
  from @tab
  union all
  select -1 as FLAG,Equipment,EventName,Priority,ETime
  from @TAB
  union all
  select -1 as FLAG,a.Equipment,a.EventName,a.Priority,
    case when a.STime>b.STime then a.STime else b.STime end
  from @TAB a
    inner join @TAB b on a.Equipment=b.Equipment
      and a.STime<b.ETime and b.STime<a.ETime
	  and b.Priority<a.Priority 
  union all
  select 1 as FLAG,a.Equipment,a.EventName,a.Priority,
	case when a.ETime<b.ETime then a.ETime else b.ETime end
  from @TAB a
    inner join @TAB b on a.Equipment=b.Equipment
      and a.STime<b.ETime and b.STime<a.ETime
	  and b.Priority<a.Priority 
) a
group by Equipment,EventName,Priority,STime
having sum(FLAG)<>0

select a.Equipment,a.EventName,a.Priority,
  b.STime,a.STime as ETime
from @tmp a
  cross apply (
    select max(b.STime) as STime,sum(b.FLAG) as FLAG
	from @tmp b
	where b.Equipment=a.Equipment and b.EventName=a.EventName
	  and b.Priority=a.Priority
	  and b.STime<a.STime
  ) b
where a.FLAG<0 and b.FLAG>0

ID уже не нужен. Уникальность не требуется.
6 дек 13, 21:33    [15255957]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
sdet
Member

Откуда:
Сообщений: 463
Serg_77m,
Еще вариант
;with cteDat as 
(
 select dt,row_number() over (order by dt) rn 
 from   (
       select STime dt from   @TAB
       union
       select ETime dt from   @TAB
       ) t
),
cteRes
as 
(select r.Equipment,r.EventName,r.Priority,d.STime,d.ETime,row_number() over (partition by d.STime,d.ETime order by Priority) rn
from   ( select b.dt STime,e.dt ETime from cteDat b,cteDat e where  b.rn = e.rn - 1)d, @TAB r
where  r.STime <= d.STime and  r.ETime >= d.ETime and d.STime<> d.ETime
) 
select Equipment,EventName,Priority,STime,ETime from cteRes where rn=1
order by EventName,STime,ETime
7 дек 13, 22:42    [15259042]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Спасибо большое!
Обязательно погоняю на разных вариантах, но вариант с рекурсией применять опасаюсь так как событий около 3-4 тысяч и руководство повесит за одно место если будет долго ждать
8 дек 13, 11:26    [15260060]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Gizmor
Member

Откуда:
Сообщений: 54
Serg_77m
Попытка №3.
DECLARE @TAB TABLE ([Priority] [nvarchar](50),
[Equipment] [nvarchar] (50),
[EventName] [nvarchar](50),
[STime] [datetime],
[ETime] [datetime])
insert into @TAB values
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 10:00', '2013-12-01 13:00'),
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 10:00', '2013-12-01 13:00'),
('1 приоритет', 'Оборудование 1', 'Событие 1', '2013-12-01 14:00', '2013-12-01 18:00'),
('2 приоритет', 'Оборудование 1', 'Событие 3', '2013-12-01 11:00', '2013-12-01 16:00'),
('3 приоритет', 'Оборудование 1', 'Событие 4', '2013-12-01 12:00', '2013-12-01 19:00')


DECLARE @tmp TABLE (
  FLAG smallint,Equipment nvarchar(50),EventName nvarchar(50),Priority nvarchar(50),
  STime datetime,
  primary key (Equipment,EventName,Priority,STime)
)

insert into @tmp (Equipment,EventName,Priority,STime,FLAG)
select Equipment,EventName,Priority,STime,sum(FLAG) as FLAG
from (
  select 1 as FLAG,Equipment,EventName,Priority,STime
  from @tab
  union all
  select -1 as FLAG,Equipment,EventName,Priority,ETime
  from @TAB
  union all
  select -1 as FLAG,a.Equipment,a.EventName,a.Priority,
    case when a.STime>b.STime then a.STime else b.STime end
  from @TAB a
    inner join @TAB b on a.Equipment=b.Equipment
      and a.STime<b.ETime and b.STime<a.ETime
	  and b.Priority<a.Priority 
  union all
  select 1 as FLAG,a.Equipment,a.EventName,a.Priority,
	case when a.ETime<b.ETime then a.ETime else b.ETime end
  from @TAB a
    inner join @TAB b on a.Equipment=b.Equipment
      and a.STime<b.ETime and b.STime<a.ETime
	  and b.Priority<a.Priority 
) a
group by Equipment,EventName,Priority,STime
having sum(FLAG)<>0

select a.Equipment,a.EventName,a.Priority,
  b.STime,a.STime as ETime
from @tmp a
  cross apply (
    select max(b.STime) as STime,sum(b.FLAG) as FLAG
	from @tmp b
	where b.Equipment=a.Equipment and b.EventName=a.EventName
	  and b.Priority=a.Priority
	  and b.STime<a.STime
  ) b
where a.FLAG<0 and b.FLAG>0

ID уже не нужен. Уникальность не требуется.


Serg_77m!!! Гениально. Спасибо. Крайне благодарен за запрос. В жисть бы не догадался!!! Еще бы понять как это работает =))
9 дек 13, 16:04    [15265510]     Ответить | Цитировать Сообщить модератору
 Re: Обработка событий в таблице SQL  [new]
Добрый Э - Эх
Guest
Gizmor
Serg_77m!!! Гениально. Спасибо. Крайне благодарен за запрос. В жисть бы не догадался!!! Еще бы понять как это работает =))
В основе этого решения, как и во многих других решениях задач на пересечение, объединение и разделение интервалов, лежит метод "разворота" диапазонов в точки, с последующей проекцией точек на исходные интервалы и формированием новых интервалов по множеству полученных подинтервальчиков.
9 дек 13, 21:07    [15267241]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить