Re: Working with huge tables of chronological data

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 7 Apr 2007 10:10:13 +0100
Message-ID: <xoWdnTbZfObx_YrbnZ2dneKdnZydnZ2d@bt.com>

"Mladen Gogala" <mgogala.SPAM-ME.NOT_at_verizon.net> wrote in message news:pan.2007.04.06.23.32.32_at_verizon.net...

> On Fri, 06 Apr 2007 22:45:44 +0100, Jonathan Lewis wrote:
>
>> Correct, the option using the analytic lag() would only give you the
>> adjacent pairs.
>
> Actually, it wouldn't. In version 10g, lag has an "offset" argument
> which would tell you by how much is it trailing.
>
> -- 
> http://www.mladen-gogala.com

Tsk, Tsk,

Mladen,

After your complaints about lazy participants in Oracle-l, you didn't read the whole of this fairly short posting before responding.

The first few lines were as follows:

    > Jonathan Lewis wrote:

>> Option b)
>> Use the analytic lag(,1) function

    >>


    >> select

    >>     flag, event, prior_event, timestamp, prior_timestamp

>> from (
>> select
>> flag, lag(flag,1) over (order by timestamp) prior_flag,

....

followed by

>> Correct, the option using the analytic lag() would only give you the >> adjacent pairs.

So option (b), as stated, would only give the adjacent pairs. Moreover it looks like I already knew that the lag() function allowed for an offset - because I used it (making the default value explicit).

In fact, if you read the old manuals, you will find lag() has had an offset since it was first introduced in Oracle 8.1.6.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Sat Apr 07 2007 - 04:10:13 CDT