Search Postgresql Archives

Re: how do you write aggregate function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux