Re: obtain the difference between successive rows

On 2012-10-20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Raymond O'Donnell" <rod(at)iol(dot)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