Re: subtracting values of sequential rows

<sybrandb_at_hccnet.nl> a écrit dans le message de news: nj2gc3tfq0mhku52llqaeq8odttur7e8jr_at_4ax.com...
| 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

I won't give a solution to a question that just requires to read the function definition. ;)

Regards
Michel Cadot Received on Sun Aug 19 2007 - 10:00:43 CDT