Re: subtracting values of sequential rows

On Sun, 19 Aug 2007 07:34:45 GMT, mh_at_pixar.com wrote:

>Once a minute I collect a particular statistic, the total count of
>jobs completed. I can report on the last 5 minutes' progress
>with the query below.
>
>I would like to add on the the report the count of the jobs
>completed in that minute, which is of course the current minute's
>value of NCOMPLETED minus the previous minute's value of NCOMPLETED.
>
>Can I do this with a single SQL query? i.e., can I reference
>the value of NCOMPLETED for "rownum - 1"?
>
>select * from (
> select snaptime,ncompleted from op_thumbcounts
> order by snaptime desc
>) where rownum <= 5
>
>SNAPTIME NCOMPLETED (What I would like)
>------------------------- ----------------------
>19-AUG-07 00:17:00 2841878 140
>19-AUG-07 00:16:00 2841738 169
>19-AUG-07 00:15:00 2841569 159
>19-AUG-07 00:14:00 2841410
>19-AUG-07 00:13:00 2841220
>
>Many TIA!
>Mark

You need analytical functions, in particular the LAG function. The LAG function is documented in the datawarehousing manual. Other than that you can wait for Michel Cadot to do your work for you.

-- 
Sybrand Bakker
Senior Oracle DBA