Re: Analytics windowing wuth lag/lead?

  • From: amonte <ax.mount@xxxxxxxxx>
  • To: "Shamsudeen, Riyaj" <RS2273@xxxxxxx>
  • Date: Fri, 14 Sep 2007 15:08:44 +0200

That give some lights.

Thank you




On 9/13/07, Shamsudeen, Riyaj <RS2273@xxxxxxx> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *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
>
>
>

Other related posts: