RE: Analytics windowing wuth lag/lead?

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