Justin wrote:
> The help is not real clear nor can i find examples how to write an
> aggregate function.
Examples:
http://www.postgresql.org/docs/8.2/static/xaggr.html
> I searched the archive of the mail list and did not find anything
The online documentation is excellent for these sorts of things.
> I need to write Weighted Average function so the need to keep track of
> multiple variables between function calls is must?
You don't necessarily need an aggregate function to do this.
A weighted average takes several independent variables, weights them
based on some constant (usually the difference between a static time and
the time at which the data were recorded), and returns a value [0].
Maintaining state between calls is probably going to be more trouble
than it's worth, especially if you're recomputing the weights all the
time... which, in most cases, is what happens.
I perform exponential moving average analysis of stock market and
trading data, for which I have a table that contains columns like the
following (these data are not intended to resemble the performance of
any particular security).
id | time | price
-----------------
1 | 09:30 | 89.54
2 | 09:31 | 89.58
3 | 09:32 | 89.53
4 | 09:33 | 89.5
5 | 09:34 | 89.51
6 | 09:35 | 89.5
7 | 09:36 | 89.42
8 | 09:37 | 89.44
When I compute the exponential average of these data, I'm always looking
at the "most recent" X prices, as I loop over all the rows in which I'm
interested. Which means I need to recompute the weighted values for
every minute of data (in the case of this sample table, anyway).
Maintaining state for that sort of calculation wouldn't be worth the
overhead.
I suggest writing a function (in PL/pgSQL or whatever your favorite
flavor is) that performs a query to retrieve all the rows you need and
outputs a SETOF data that contains the weighted averages. If you only
need one average at a time, just return a single value instead of a
SETOF values.
I hope this helps, but in case it doesn't, you should probably give us a
little more detail about what you're actually trying to do.
Colin
[0] Using a formula like this:
<http://en.wikipedia.org/wiki/Moving_average#Weighted_moving_average>
