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

Откуда: Minsk
Сообщений: 59
Добрый день, уважаемые форумчане
Помогите пожалуйста решить задачу.

Имеем данные об активности агента
- если агент выходит Online появляется запись IsOnline=1 и время события
- если aгент Offline могут подряд идти несколько записей IsOnline=0 и время проверки
- если aгент Online могут подряд идти несколько записей IsOnline=1 и время проверки
- если запись IsOnline=1 первая в сутках, то считаем что до этого агент был Offline

Пример:
CREATE TABLE AgentActivity (
    AgentId int
  , IsOnline bit
  , TriggerTime dateTime
);

INSERT INTO AgentActivity (AgentId, TriggerTime, IsOnline)
VALUES (1,'20140912 08:00:00', 0)
          , (1,'20140912 08:50:00',1)
	  , (1,'20140912 09:30:00',0)
          , (1,'20140912 10:00:00',1)
	  , (1,'20140912 10:30:00',0)
	  , (2,'20140912 09:10:00',1)
	  , (2,'20140912 09:50:00',0);


Нужно вернуть данные по часам (для каждого часа в сутках), сколько секунд каждый агент был Online в течение каждого конкретного часа. Таким образом выводится начало часа и сумма времени онлайн в интервале до следующего часа.
Для приведенного примера:

AgentId, HourOfDay, SecondOnline
...
1 20140912 00:00:00 0
1 20140912 01:00:00 0
...
1 20140912 08:00:00 600
1 20140912 09:00:00 2400
...
2 20140912 00:00:00 0
2 20140912 01:00:00 0
...
2 20140912 08:00:00 0
2 20140912 09:00:00 1800
...
12 сен 14, 15:27    [16569011]     Ответить | Цитировать Сообщить модератору
 Re: Запрос, для вычисления времени активности в течение каждого часа  [new]
a_voronin
Member

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

версия сервера какая?

Под 2012 делается легко с LAG, под 2005-2008 придется повыкручиватся
12 сен 14, 19:21    [16570013]     Ответить | Цитировать Сообщить модератору
 Re: Запрос, для вычисления времени активности в течение каждого часа  [new]
aleks2
Guest
Плохой балерине завсегда тапочки жмут
declare @AgentActivity TABLE (
    AgentId int
  , IsOnline bit
  , TriggerTime dateTime
);

INSERT INTO @AgentActivity (AgentId, TriggerTime, IsOnline)
VALUES (1,'20140912 08:00:00', 0)
          , (1,'20140912 08:50:00',1)
	  , (1,'20140912 09:30:00',0)
	  , (1,'20140912 09:30:00',0)
          , (1,'20140912 10:00:00',1)
	  , (1,'20140912 10:30:00',0)
	  , (2,'20140912 09:10:00',1)
	  , (2,'20140912 09:50:00',0);

;with
  hours as ( select [hour] from ( values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23) ) d([hour]))
, days as ( select distinct cast( CAST(TriggerTime as date) as datetime ) [day] from @AgentActivity )
, days_hours as ( select dateadd(hour, [hour], [day]) dh from days cross join hours )
, naa as ( select *, ROW_NUMBER() oVER(PARTITION BY AgentId ORDER BY TriggerTime ASC) n from @AgentActivity )
-- искореняем повторы
, unaa as ( select n1.*, ROW_NUMBER() oVER(PARTITION BY n1.AgentId ORDER BY n1.TriggerTime ASC) nn 
              from naa n1 left outer join naa n2 on n1.AgentId = n2.AgentId and n1.n+1 = n2.n and n1.IsOnline = n2.IsOnline
              where n2.n is null
          )
-- пoлучаем интервалы
, ints as ( select u1.AgentId, u1.IsOnline, u1.TriggerTime T1, u2.TriggerTime T2  from ( select * from unaa where nn%2 = 1 ) u1 inner join unaa u2 on u1.AgentId = u2.AgentId and u1.nn+1 = u2.nn )
-- пoлучаем пересечения интервалов с часовыми
, intersecs as ( select dh.*, i.AgentId, i.IsOnline
                     , case when i.IsOnline is not null then (select MAX(T) from ( values(dh.dh),(i.T1)) x(T)) end T1
                     , case when i.IsOnline is not null then (select MIN(T) from ( values(dateadd(hour, 1, dh)),(i.T2)) x(T)) end T2
                   from days_hours dh left outer join ints i on dh.dh <= i.T2 and i.T1 <= dateadd(hour, 1, dh) )
-- длины пересечения интервалов с часовыми
, hs as ( select dh, AgentId, case IsOnline when 1 then +1 when 0 then -1 end * datediff(SECOND, T1, T2) dT from intersecs )
-- группировка интервалов 
, ghs as ( select dh, AgentId, SUM(dT) dT from hs group by dh, AgentId )
-- результат
, ghs1 as ( select dh, AgentId, case when dT>=0 then dT when dT<0 then dT + 60*60 end dT  from ghs)
select * from ghs order by AgentId, dh

Идея, надеюсь, ясна. А отлаживать мне лень.
12 сен 14, 19:58    [16570178]     Ответить | Цитировать Сообщить модератору
 Re: Запрос, для вычисления времени активности в течение каждого часа  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
aleks2,

Спасибо. идея ясна. Особенно мне пригодился кусок кода для пересечения интервалов агента с часовыми интервалами:


, intersecs as ( select dh.*, i.AgentId, i.IsOnline
                     , case when i.IsOnline is not null then (select MAX(T) from ( values(dh.dh),(i.T1)) x(T)) end T1
                     , case when i.IsOnline is not null then (select MIN(T) from ( values(dateadd(hour, 1, dh)),(i.T2)) x(T)) end T2
                   from days_hours dh left outer join ints i on dh.dh <= i.T2 and i.T1 <= dateadd(hour, 1, dh) )



А остальной запрос я все же построил используя LAG/LEAD. Реализация на базе SELF JOIN работала недостаточно производительно.
16 сен 14, 16:37    [16581990]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить