Re: obtain the difference between successive rows

On 20/10/2012 17:23, Tom Lane wrote:
> Chris Angelico <rosuav(at)gmail(dot)com> writes:
>> To be quite honest, I would simply read the table directly and then do
>> the processing in an application language :) But two window functions
>> should do the trick. Whether or not it's actually more efficient that
>> way is another question.
>
> 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?

> Having said that, they are pretty expensive. I tend to agree that doing
> the processing on the application side might be faster --- but only if
> you've got a place to put such code there. If you've just got generic

True, assuming that you're working with a language that handles dates
well... I do a lot of PHP, and have found that it's generally safer to
handle date arithmetic in Postges.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie