Re: Analytics windowing wuth lag/lead?

That give some lights.

Thank you

On 9/13/07, Shamsudeen, Riyaj <RS2273_at_att.com> wrote:
>
> Make sure logic is right, by testing with many conditions though.
> Last_value is to get last value in an ordered set and lag is to get previous
> row in an ordered set.
>
>
>
> select * from (
>
> select cod_ejer, id_comp, max_val as val, versioning, max_val -
> lag( max_val ) over ( order by cod_ejer ) diff
>
> from (
>
> SELECT distinct COD_EJER, ID_COMP,
>
> last_value( versioning )
>
> over (partition by cod_ejer order by versioning rows between unbounded
> preceding and unbounded following) versioning,
>
> last_value( val )
>
> over (partition by cod_ejer order by versioning rows between unbounded
> preceding and unbounded following) max_val
>
> FROM COMP_CL
>
> WHERE COD_EJER IN (200202, 200203)
>
> AND ID_COMP = 1173
>
> )
>
> ) where diff is not null
>
> /
>
>
>
> COD_EJER ID_COMP VAL VERSIONING DIFF
>
> ---------- ---------- ---------- ---------- ----------
>
> 200203 1173 30000 3 7000
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *amonte
> *Sent:* Thursday, September 13, 2007 4:34 AM
> *To:* oracle-l
> *Subject:* Analytics windowing wuth lag/lead?
>
>
>
> Hi all
>
> I have a query which returns following results
>
> SELECT
> COD_EJER,
> ID_COMP,
> VAL,
> VERSIONING
> FROM COMP_CL
> WHERE COD_EJER IN (200202, 200203)
> AND ID_COMP = 1173
> AND KEY = 56
>
> COD_EJER ID_COMP VAL VERSIONING
> ------------ ---------- ---------- -----------
> 200202 1173 22000 1
> 200202 1173 23000 2
> 200203 1173 30000 1
> 200203 1173 30000 3
>
> And teh requirement is:
>
> VAL OF MAX(VERSIONING) of 200203 - VAL OF MAX(VERSIONING) of 200202
>
> So it would return folloging
>
> COD_EJER ID_COMP VAL VERSIONING DIFF
> ------------ ---------- ---------- ----------- -----
> 200203 1173 30000 3 7000
>
> I know I can do first a MAX(VERSIONING) then filter the max version rows
> and use lag to subtract but I wonder if there are any better ways such as
> using windowing?
>
> I am basically treating quarters (200201, 200202, 200203 200204)
>
> Cheers
>
> Alex
>
>
>

--
http://www.freelists.org/webpage/oracle-l