aggregate and order by

From: "Matthew Dennis" <mdennis(at)merfer(dot)net>
To: PGSQL <pgsql-general(at)postgresql(dot)org>
Subject: aggregate and order by
Date: 2007-12-06 20:12:48
Message-ID: e94d85500712061212g2f3ccaf3pca791a755db1e260@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I want to create an aggregate that will give the average velocity (sum of
distance traveled / sum of elapsed time) from position and timestamps.

example:

create table data(position integer, pos_time timestamp, trip_id integer);

insert into data values(1, "time x", 1);
insert into data values(2, "time x plus 1 second", 1);
insert into data values(4, "time x plus 2 second", 1);
insert into data values(1, "time y", 2);
insert into data values(4, "time y plus 1 second", 2);
insert into data values(16, "time y plus 3 second", 2);
etc, etc, etc...

select trip_id, avg_vel(position, pos_time) from data group by trip_id;

Row one to row two has an elapsed time of ("time x plus 1 second" - "time
x") and a difference in position of (2 - 1) and from row two to row three
there is a difference in position of (4 -2) and a elapsed time of ("time x
plus 1 second" - "time x plus 2 seconds") so for trip_id we get ((2-1) +
(4-2)) / (1 + 1).

Row 4 to row 5 has a difference in position of (4-1) and a elapsed time of
("time y plus 1 second" - "time y") and from row 5 to row 6 there is a
position difference of (16-4) and time difference of ("time y plus 3
seconds" - "time y plus 1 second") so for trip_id 2 we get ((4-1) + (16-4))
/ (1 + 2).

Keep in mind that I can't just take the difference between the start and end
of the trip because I might move from 1 to 10 to 1. If I just took the end
points (1-1) the velocity would be zero because it looks like I didn't move.

So I could write an aggregate that remembers the last row and on each new
row, does the diff and keeps the running sums and then when it's done, the
final function does the division and returns the average velocity. However,
this only works if the rows come into the aggregate function in the correct
order (otherwise I might count the total distance and/or elapsed time wrong
because both are calculated from the difference of the previous row). So,
my question is if I can have PostgreSQL honor order by clauses such as:

select trip_id, avg_vel(position, pos_time) from (select position, pos_time,
trip_id from data order by pos_time) sorted_data

Would this in fact guarantee that the rows are passed into the aggregate in
the order specified?

Other suggestions/discussions/questions/etc are welcome.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John Wells 2007-12-06 20:14:34 Re: Recovering data via raw table and field separators
Previous Message Martijn van Oosterhout 2007-12-06 20:02:31 Re: Recovering data via raw table and field separators