Re: Looking for "gaps" (from a time perspective) in data
Date: Fri, 10 Oct 2008 05:56:32 -0700 (PDT)
On Oct 10, 12:19 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 10.10.2008 11:53, jeremy wrote:
> > This is on 10gR2 standard edition.
> > We have a table defined like this:
> > txn_id
> > date_start
> > date_end
> > url_called
> > which contains a record for every time our application processes a
> > request from a browser. We would like to be able to spot any "gaps" in
> > the data - which would indicate a period when the service was
> > unavailable for whatever reason.
> > To do this we need to do a comparison between the date_start of each
> > row and the one that preceded (or followed) it and then see if the
> > period of time is greater than a to-be-determined threshold.
> > Can a single SQL statement be constructed which could achieve this? if
> > so could you point me in the right direction? For those who haven't
> > followed what I was asking about, if you are in the majority then
> > please accept my apologies for not being more concise :)
> Yes, this should be possible with analytic SQL. You define a sliding
> window of one row preceding ordered by date_start and take the
> difference of date_start - min(date_start).
> An alternative might be to define the window based on a range of dates
> preceding (i.e. use you threshold) and emit all rows where the count is
> 1 (i.e. there is no other record within the threshold time).
Ended up with a query like this:
select txn_id, web_site_id, username, date_start,
lag(date_start) over (order by txn_id) prev_date_start, lead(date_start) over (order by txn_id) next_date_start, url
where date_start >= sysdate-5
and ip_address is not null
order by 1) tabview
where date_start - prev_date_start > 1/1440 and to_number(to_char(date_start,'hh24')) between 6 and 22;
Thanks for your comments.
-- jeremyReceived on Fri Oct 10 2008 - 07:56:32 CDT