aggregate and order by
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.
"Matthew Dennis" <mdennis@merfer.net> writes:
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_dataWould this in fact guarantee that the rows are passed into the aggregate in
the order specified?
Yes. AFAIK this isn't covered by the spec but it works in Postgres and we know
there are people depending on it so we wouldn't break it without a big notice
and presumably some replacement.
Other suggestions/discussions/questions/etc are welcome.
Good luck, this looks pretty painful to get right. Keep in mind you have to
keep all your state in the state data. If you keep a temporary variable
outside that data then your function won't work if it's called twice in the
same query like "select avg_vel(position, pos_time), avg_vel(position2,
pos_time2) from ..."
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
Hi people,
I intend to set up two slave servers, one using WAL shipping and one
using Slony I.
Are there any good tools, or scripts that'll help us check that both
replication methods are working?
I know theres Nagios - but what does this actually allow us to
monitor?
Also if I want to make backups from the slaves to save master
downtime / load what are my options?
Thanks
__________________________________________________________
Sent from Yahoo! - the World's favourite mail http://uk.mail.yahoo.com
"Glyn Astill" <glynastill@yahoo.co.uk> writes:
Hi people,
I intend to set up two slave servers, one using WAL shipping and one
using Slony I.
This has nothing to do with "aggregate and ordering" the subject of the
message to which you're replying. You're more likely to see responses if you
post in a new thread.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
How did that happen? The subject is totally different, so is the
body.
This is shit.
--- Gregory Stark <stark@enterprisedb.com> wrote:"Glyn Astill" <glynastill@yahoo.co.uk> writes:
Hi people,
I intend to set up two slave servers, one using WAL shipping and
one
using Slony I.
This has nothing to do with "aggregate and ordering" the subject of
the
message to which you're replying. You're more likely to see
responses if you
post in a new thread.--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's
PostgreSQL training!---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?
Glyn Astill
__________________________________________________________
Sent from Yahoo! - the World's favourite mail http://uk.mail.yahoo.com
Glyn Astill wrote:
How did that happen? The subject is totally different, so is the
body.
It has an "In-Reply-To:" and possibly "References:" header which relates
it to the other thread.
The solution is simple. Don't reply to an existing message when you
want to post a new thread. Compose a new one instead.
--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"Aprender sin pensar es in�til; pensar sin aprender, peligroso" (Confucio)
Ah thanks, thats what I must have done. Never happened on other lists
so I assumed it'd be okay. My Bad.
--- Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:Glyn Astill wrote:
How did that happen? The subject is totally different, so is the
body.It has an "In-Reply-To:" and possibly "References:" header which
relates
it to the other thread.The solution is simple. Don't reply to an existing message when
you
want to post a new thread. Compose a new one instead.--
Alvaro Herrera Developer,
http://www.PostgreSQL.org/
"Aprender sin pensar es in�til; pensar sin aprender, peligroso"
(Confucio)
Glyn Astill
__________________________________________________________
Sent from Yahoo! - the World's favourite mail http://uk.mail.yahoo.com
On Thu, Dec 06, 2007 at 02:12:48PM -0600, Matthew Dennis wrote:
I want to create an aggregate that will give the average velocity (sum of
distance traveled / sum of elapsed time) from position and timestamps.
How do you want to handle noisy data? If you want to handle it in any
reasonable way you'd need to some sort of regression; i.e. you'd need
to consider all the relavant data and then try and minimise the total
error somehow. I've always relied on external tools to do this sort of
thing, but if you want to do it in the database you may be able to get
somewhere with pl/r.
Sam