Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
Задача следующая
Есть таблица log_table в ней поля:
Время события - rtime(в запросе задаётся через between),
Есть код события code: 26 - connect и 27 - disconnect,
Id устройства - deviceid
Необходимо на выходе получить Id устройства и кол-во disconnect более 10 минут
Не сообразить как составить запрос...
10 май 06, 16:25    [2648658]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
UK0IAI
Member

Откуда: питер
Сообщений: 13529
аналитические функци., конструкция betwwen.
range between nnnn precending nnnn following

Том Кайт, т.2 страница 55
10 май 06, 16:32    [2648724]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116282
А у вас по deviceid может быть два disconnecta подряд или они всегда чередуются ?
10 май 06, 16:35    [2648745]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
Proteus
Member

Откуда:
Сообщений: 1348
а уточнить можно?
надо получить deviceid для тех устройств у которых между событием 26 и событием 27 порошло > 10 минут?
правильно?
10 май 06, 16:37    [2648767]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116282
ОК, решаю как понял : в пределах deviceid disconnect и connect чередуются.
Если disconnect последний - непонятно, возьму до sysdate ...

Тестовые данные

with log_table
as
(select 1 deviceid, to_date('10-MAI-2006 09:23','DD-MON-YYYY HH24:MI') rtime, 'connect' code
 from dual
 union all
select 1 deviceid, to_date('10-MAI-2006 09:32','DD-MON-YYYY HH24:MI') rtime, 'disconnect' code
 from dual
 union all
select 1 deviceid, to_date('10-MAI-2006 09:41','DD-MON-YYYY HH24:MI') rtime, 'connect' code
 from dual
 union all
select 1 deviceid, to_date('10-MAI-2006 11:41','DD-MON-YYYY HH24:MI') rtime, 'disconnect' code
 from dual
 union all
select 1 deviceid, to_date('10-MAI-2006 11:54','DD-MON-YYYY HH24:MI') rtime, 'connect' code
 from dual
 )

Запрос

select deviceid, count(*)
from
(select l.* , lead(rtime,1,sysdate) over (partition by deviceid order by rtime) lead_rtime from log_table l order by deviceid, rtime)
where (lead_rtime - rtime) * 24 * 60 > 10
and code = 'disconnect'
group by deviceid

1 1
10 май 06, 16:57    [2648918]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
dmidek
А у вас по deviceid может быть два disconnecta подряд или они всегда чередуются ?

Отвлёкся на другие проблемы. Извиняюсь.
1. Если у устройства на discсonnect, то connect для него может наступить и через неделю (но такое редко). Может и сразу. В базе сделал выборку. Там у меня и подряд было, но будем считать что нет... Connect безусловно идёт за Disconnect, но могут за это время наступить аналогичные события для других устойств....
10 май 06, 17:04    [2648980]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116282
mod
dmidek
А у вас по deviceid может быть два disconnecta подряд или они всегда чередуются ?

Отвлёкся на другие проблемы. Извиняюсь.
1. Если у устройства на discсonnect, то connect для него может наступить и через неделю (но такое редко). Может и сразу. В базе сделал выборку. Там у меня и подряд было, но будем считать что нет... Connect безусловно идёт за Disconnect, но могут за это время наступить аналогичные события для других устойств....

Тогда вроде все нормально. Проверьте
10 май 06, 17:06    [2648994]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
neodiX
Member

Откуда: Frankfurt
Сообщений: 105
если время между дисконектами:

 select deviceid,count(*) from
 (
 select deviceid,rtime,
   lead(rtime,1) OVER (partition by deviceid order by rtime) a                     
 from log_table where 
 code=27  
 order by rtime                    
 ) internal
 where a-rtime > 1/(24*60*60)
 group by deviceid
10 май 06, 17:07    [2649004]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
neodiX
Member

Откуда: Frankfurt
Сообщений: 105
хех... шустрые какие все..:)
10 май 06, 17:10    [2649023]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
В запросе
select deviceid, count(*)
from
(select l.* , lead(rtime,1,sysdate) over (partition by deviceid order by rtime) lead_rtime from log_table l order by deviceid, rtime)
where (lead_rtime - rtime) * 24 * 60 > 10
and code = 'disconnect'
group by deviceid
немного не уловил где connect
10 май 06, 17:24    [2649132]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
neodiX
Member

Откуда: Frankfurt
Сообщений: 105
mod
В запросе
select deviceid, count(*)
from
(select l.* , lead(rtime,1,sysdate) over (partition by deviceid order by rtime) lead_rtime from log_table l order by deviceid, rtime)
where (lead_rtime - rtime) * 24 * 60 > 10
and code = 'disconnect'
group by deviceid
немного не уловил где connect

тут люди полагают, что нужно узнать время между дисконектами.. зачем тогда конекты селектить
10 май 06, 17:27    [2649148]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116282
2 mod : Если connect и disconnect чередуются, то connect учитывается автоматически

2 neodiX
Я считаю время дисконнекта, то есть до коннекта.
Вы считаете время дисконнекта + время последующего коннекта.
Кто прав, автору виднее ...
Мне кажется, что по логике нужно мерять только дисконнект...
10 май 06, 17:32    [2649179]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
Не, я же написал, что нужно узнать время прибывания в сотоянии disconnect, которое заканчивается, когда наступает connect... время между двумя disconnect здесь до лампочки.... На выходе и время не важно... Важно только количество отключек устройств более чем на 10 минут...
10 май 06, 17:35    [2649202]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
Событие disconnect - это аварийная ситуация (т.е. пропала связь) и считается что более 10 минут это критично...
10 май 06, 17:37    [2649214]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
neodiX
Member

Откуда: Frankfurt
Сообщений: 105
dmidek
2 mod : Если connect и disconnect чередуются, то connect учитывается автоматически

2 neodiX
Я считаю время дисконнекта, то есть до коннекта.
Вы считаете время дисконнекта + время последующего коннекта.
Кто прав, автору виднее ...
Мне кажется, что по логике нужно мерять только дисконнект...


2dmidek
мне кажется вы правы.
10 май 06, 17:37    [2649216]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
Таблица, же по-сути это log как видно из названия...
10 май 06, 17:38    [2649219]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
Proteus
а уточнить можно?
надо получить deviceid для тех устройств у которых между событием 26 и событием 27 порошло > 10 минут?
правильно?

Правильно вообщем... между событием connect и disconnect более 10 минут...
10 май 06, 17:41    [2649229]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
[quot dmidek]2 mod : Если connect и disconnect чередуются, то connect учитывается автоматически
Не улавливаю почему автоматически?
10 май 06, 17:43    [2649250]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116282
mod
[quot dmidek]2 mod : Если connect и disconnect чередуются, то connect учитывается автоматически
Не улавливаю почему автоматически?

У меня объяснения получаются не очень хорошо.
Я попробую. lead_rtime - это время, следующего по времени события
после дисконнекта для заданного deviceid , а это именно connect.
Вычитая эти два времени между собой, мы и получаем общее время дисконнекта и сравниваем его с 10 минутами. Подобное работает только в предположении, что коннект и дисконнект чередуются, но вроде Вы с этим согласны...
10 май 06, 17:51    [2649292]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
Elic
Member

Откуда:
Сообщений: 29990
Периоды отключки независимо от (не)чередования состояний вкл/выкл:
with log_table as
( select 1 deviceid, to_date('10-05-2006 09:23','DD-MM-YYYY HH24:MI') rtime, 'connect'    code from dual union all
  select 1 deviceid, to_date('10-05-2006 09:32','DD-MM-YYYY HH24:MI') rtime, 'disconnect' code from dual union all
  select 1 deviceid, to_date('10-05-2006 09:34','DD-MM-YYYY HH24:MI') rtime, 'disconnect' code from dual union all
  select 1 deviceid, to_date('10-05-2006 09:41','DD-MM-YYYY HH24:MI') rtime, 'connect'    code from dual union all
  select 1 deviceid, to_date('10-05-2006 09:59','DD-MM-YYYY HH24:MI') rtime, 'connect'    code from dual union all
  select 1 deviceid, to_date('10-05-2006 11:41','DD-MM-YYYY HH24:MI') rtime, 'disconnect' code from dual union all
  select 1 deviceid, to_date('10-05-2006 11:54','DD-MM-YYYY HH24:MI') rtime, 'connect'    code from dual
)
select deviceid, min(decode(code, 'disconnect', rtime)) as disconnected,
                 min(decode(code,    'connect', rtime)) as connected
  from
  ( select deviceid, rtime, code, sum(start_of_group) over (partition by deviceid order by rtime) as group_no
      from
      ( select deviceid, rtime, code,
            case when code = 'disconnect'
                  and lag(code, 1, 'connect') over (partition by deviceid order by rtime) = 'connect'
                 then 1
            end as start_of_group
          from log_table
      )
  )
  group by deviceid, group_no
10 май 06, 18:23    [2649439]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
dmidek
mod
[quot dmidek]2 mod : Если connect и disconnect чередуются, то connect учитывается автоматически
Не улавливаю почему автоматически?

У меня объяснения получаются не очень хорошо.
Я попробую. lead_rtime - это время, следующего по времени события
после дисконнекта для заданного deviceid , а это именно connect.
Вычитая эти два времени между собой, мы и получаем общее время дисконнекта и сравниваем его с 10 минутами. Подобное работает только в предположении, что коннект и дисконнект чередуются, но вроде Вы с этим согласны...

Там у поля не два значения. Могут быть и другие события...
10 май 06, 18:30    [2649466]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116282
mod
dmidek
mod
[quot dmidek]2 mod : Если connect и disconnect чередуются, то connect учитывается автоматически
Не улавливаю почему автоматически?

У меня объяснения получаются не очень хорошо.
Я попробую. lead_rtime - это время, следующего по времени события
после дисконнекта для заданного deviceid , а это именно connect.
Вычитая эти два времени между собой, мы и получаем общее время дисконнекта и сравниваем его с 10 минутами. Подобное работает только в предположении, что коннект и дисконнект чередуются, но вроде Вы с этим согласны...

Там у поля не два значения. Могут быть и другие события...

Ну Вы даете. Нет слов ...
10 май 06, 18:34    [2649478]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
Elic
Периоды отключки независимо от (не)чередования состояний вкл/выкл:
with log_table as
( select 1 deviceid, to_date('10-05-2006 09:23','DD-MM-YYYY HH24:MI') rtime, 'connect'    code from dual union all
  select 1 deviceid, to_date('10-05-2006 09:32','DD-MM-YYYY HH24:MI') rtime, 'disconnect' code from dual union all
  select 1 deviceid, to_date('10-05-2006 09:34','DD-MM-YYYY HH24:MI') rtime, 'disconnect' code from dual union all
  select 1 deviceid, to_date('10-05-2006 09:41','DD-MM-YYYY HH24:MI') rtime, 'connect'    code from dual union all
  select 1 deviceid, to_date('10-05-2006 09:59','DD-MM-YYYY HH24:MI') rtime, 'connect'    code from dual union all
  select 1 deviceid, to_date('10-05-2006 11:41','DD-MM-YYYY HH24:MI') rtime, 'disconnect' code from dual union all
  select 1 deviceid, to_date('10-05-2006 11:54','DD-MM-YYYY HH24:MI') rtime, 'connect'    code from dual
)
select deviceid, min(decode(code, 'disconnect', rtime)) as disconnected,
                 min(decode(code,    'connect', rtime)) as connected
  from
  ( select deviceid, rtime, code, sum(start_of_group) over (partition by deviceid order by rtime) as group_no
      from
      ( select deviceid, rtime, code,
            case when code = 'disconnect'
                  and lag(code, 1, 'connect') over (partition by deviceid order by rtime) = 'connect'
                 then 1
            end as start_of_group
          from log_table
      )
  )
  group by deviceid, group_no


Оно канечно Transact-SQL рулит, но не в Oracle...
11 май 06, 10:21    [2650975]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
Elic
Member

Откуда:
Сообщений: 29990
mod
Оно канечно Transact-SQL рулит, но не в Oracle...
Это... Ты, пацан, о чём?!!
11 май 06, 10:28    [2651032]     Ответить | Цитировать Сообщить модератору
 Re: Как в запросе выявить время между событиями 10 мин.?  [new]
mod
Member

Откуда:
Сообщений: 2273
Elic
mod
Оно канечно Transact-SQL рулит, но не в Oracle...
Это... Ты, пацан, о чём?!!

Да так, о своём... :-(. Не обращай внимание... Просто некоторые особенности диалекта в Oracle непонятны...
11 май 06, 10:42    [2651103]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить