Re: Looking for "gaps" (from a time perspective) in data
Date: Fri, 10 Oct 2008 15:16:15 -0700 (PDT)
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.
> > 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
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?Received on Fri Oct 10 2008 - 17:16:15 CDT