Re: how do you write aggregate function - Mailing list pgsql-general

>>A couple of email pointers:
>>
>>*  Please don't use  language like "the Array would suck this bad" in
>>lists.  I swear like a sailor in person, but it reads *very* badly in
>>email.  There are a lot of people on this list who might take offense,
>>since it is their hard work that means that arrays work at all for
>>you...

That comment was not meant to be an insult or disparaging in any way what so ever.  If it was taken as such then i'm sorry.

It seems the biggest performance hit is copying of the array content from one memory variable to another which is happening allot.

I'm not really against using a temp tables to hold  onto values.  I used to do that in Foxpro when i hit the hard limit on its array but other problems start popping up.  If we use a temp table keeping track what going with other users can make life fun.

I really want to figure this out how to speed it up.  I have to write allot more aggregate functions to analyze R&D data which will happen latter this year.   right now this function will be used in calculating manufacturing cost. 

Webb Sprague wrote:

A couple of email pointers:

*  Please don't use  language like "the Array would suck this bad" in
lists.  I swear like a sailor in person, but it reads *very* badly in
email.  There are a lot of people on this list who might take offense,
since it is their hard work that means that arrays work at all for
you...

* Please don't topquote.

On Mon, Mar 10, 2008 at 8:01 AM, Justin <justin@emproshunts.com> wrote: 

 i wish that could work  but need to keep track of the individual weights as
its a percentage of the total amount of the weight.   

The definition of "weighted mean" doesn't require that the weights be
normalized.  You probably need to calculate this separately, either
using a temp table or a column in the original table.
 

 I would never have thought the performance of the Array would suck this
bad.   

You should be using a temp table or a new column as above to store the
normalized weights, and then calling the original aggregate on those.
(a) calculate sum of weights (aggregate), (b) calculate normalized
weight for each row (weight / total), (c) find weighted mean with
aggregate that retains only the totals between function calls.  If you
let your arrays grow to the size of tables, performance will suffer
(as they are not meant to do that anyway).  I don't think you can
avoid two passes, whether inside a function or outside.
 

 Martijn van Oosterhout wrote:On Sun, Mar 09, 2008 at 11:40:47AM -0500, Justin wrote:

I got the aggregate function for weighted average done. I finely left
alone for more than 10 minutes to actual get it written. It takes 2
value input Weight and the Value. it will sum the weighted entries to
create the constant then does the normal formula, but does not
percentage number but averaged number. A simple change on the return
line it can do percentages.
If you're looking for performance, ISTM the best option would be to
simply accumulate the weights and value*weight as you go and do a
division at the end. That seems likely to beat any implementation
involving array_append.

Have a nice day,