Re: obtain the difference between successive rows

On 2012-10-20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Raymond O'Donnell" <rod@iol.ie> writes:
>> On 20/10/2012 17:23, Tom Lane wrote:
>>> FWIW, Postgres is reasonably smart about the case of multiple window
>>> functions with identical window definitions --- once you've got one
>>> lag() in the query, adding more isn't going to cost much.
>
>> Out of curiosity, would there be much difference between having multiple
>> lag()s in the SELECT and a single one in a CTE?
>
> Not sure what you're proposing?  I don't see how you'd solve this
> problem with a CTE, at least not without a join, which seems unlikely
> to be a win.


select generate_series(1,20)              as a ,
      ( generate_series(1,20)*9+random()*7)::int as b
  into temp table foo;
delete from foo where random()>0.5;

with
 j as ( select f,lag(f) over (order by f.a)as g from foo as f )
 select (f).*,(g).a as "lag a",(g).b as "lag b",
 (f).a-(g).a as "diff(a)" ,(f).b-(g).b as "diff(b)" from j;

--
⚂⚃ 100% natural