self join for history analyzis
Hello the list,
For some time now, I'm struggling with a problem of self join of a table:
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.
I need to make a self join of that table to analyze the bandwidth usage
e.g.: 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.
Initially I've put an additional column with a serial into the readouts
table, and did a join on (p.serial = n.serial+1); but that had an
occasional glitch, when serial actually skipped a value. So I'm trying
to work out a more resiliant/general solution. So far to no avail.
Is there an "sql-idiom" (receipt?) to do such join? the better if
without the spurious seiral column.
Thenx
-R
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Jan 26, 2013, at 13:32, Rafał Pietrak <rafal@zorro.isa-geek.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;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
W dniu 01/26/2013 02:49 PM, Alban Hertroys pisze:
On Jan 26, 2013, at 13:32, Rafał Pietrak <rafal@zorro.isa-geek.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.htmlFor 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general