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

On Oct 10, 5:52 am, jeremy <jeremy0..._at_gmail.com> wrote:
> On Oct 10, 12:04 pm, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
> > On 10 okt, 11:53, jeremy <jeremy0..._at_gmail.com> 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 :)
>
> > > regards,
>
> > > --
> > > jeremy
>
> > Please refer to the LAG function in the Analytical Functions chapter
> > in the Datawarehousing documentation for the version you don't care to
> > mention.
>
> You mean where I stated 10gR2 up there ^^^^?
>
> LAG and LEAD were exactly the functions I was looking for.

Also put those in the search box at asktom.oracle.com for many more examples.

The book mentioned here sounds useful (I haven't checked it out yet, I'm afraid I might have to recode a huge old project :-) ): http://oraclesponge.wordpress.com/2008/06/12/the-overlaps-predicate/

>
> > Please apologize for being *too* concise. At a minimum you should
> > always post your 4 digit version number.
>
> Blimey I didn't realise the rules now included that level of detail.

See http://www.dbaoracle.net/readme-cdos.htm#subj12

>
> > If you can't be bother to do it, please consider not posting at all.
> > Crystal balls have been worn out by similar lazy dudes like you.
>
> Heh - I know you've been flamed before for being rude, but I'll not
> rise to the bait.
>
> Thanks for your help, the pointer to the relevant functions was all I
> needed.
>

Thanks for posting the query you wound up with, too, bound to be a FAQ. jg

--
@home.com is bogus.
So, how do you run a trace on what exadata is doing?