Re: self join for history analyzis

W dniu 01/26/2013 02:49 PM, Alban Hertroys pisze:
> On Jan 26, 2013, at 13:32, Rafał Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote:
>
>> I have a usage recording table: CREATE TABLE readings(tm timestamp, bytesin int, bytesout int);
>>
>> The readouts are made "occasionally" - the timespan between the readouts are not very precise, but there is a lot of those readouts.
>>
>> when presenting data, for every readout I need to compute the difference between it and the one immediately preceding it, and divide that by respective measurement interval.
> You don't need a self-join, you need a window function.
> See: http://www.postgresql.org/docs/9.1/static/tutorial-window.html
>
> For example:
> SELECT timestamp, lag(timestamp) OVER (ORDER BY timestamp) FROM readings;
>

Yes. That's what I needed (another example, of how fragmented knowledge
of a subject makes one (myself) use quite inapropriate keywords when
searching).

Thenx,

-R