Re: Looking for "gaps" (from a time perspective) in data

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).

HTH Kind regards

        robert Received on Fri Oct 10 2008 - 06:19:16 CDT