On 2012-10-20, Tom Lane <t...@sss.pgh.pa.us> wrote:
> "Raymond O'Donnell" <r...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
