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.
I did a couple of things a little odd . instead of doing a multi
dimensional array i did a one dimensional array where the 1st row is
Weight and the 2nd row is Value. This made the loop through the array
look stupid.
I tested it across 50,000 records with a group by it took 3.3 seconds to
run.
without the group by clause performance is terrible taking several
minutes just to do the sfunc part. 371,563ms
The Array seems to have performance hit any advice? It could be the
way i'm appending to the Array which has a performance hit as the array
gets bigger and bigger ?
btober@xxxxxxxxxxxxxxxx wrote:
Richard Broersma wrote:
On Tue, Mar 4, 2008 at 8:44 AM, Justin <justin@xxxxxxxxxxxxxxx> wrote:
I searched the archive of the mail list and did not find anything
Search the documentation. There are a couple great examples posted at
http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
create or replace function wcost_average_sf (numeric[], numeric, numeric)
returns numeric[] as
$Body$
declare
_state numeric[];
begin
_state := $1;
_state := array_append(_state, $2);
_state := array_append(_state, $3);
return _state;
end;
$Body$
LANGUAGE 'plpgsql' VOLATILE;
create or replace function wcost_average_ff (numeric[] )
returns numeric as
$Body$
declare
iState alias for $1 ;
_sumedWeight numeric ;
_sumedWxV numeric ;
_elmentCount integer ;
_icounter integer ;
begin
_elmentCount := array_upper(iState,1) ;
_sumedWeight := 0 ;
_sumedWxV := 0 ;
_icounter := 0 ;
loop
_sumedWeight := _sumedWeight + iState[_icounter + 1] ;
_icounter := _icounter + 2 ;
if ( _icounter = _elmentCount ) then
exit;
end if ;
end loop ;
_icounter := 0;
loop
_sumedWxV := _sumedWxV + ( (iState[_icounter + 1]/_sumedWeight) * iState[_icounter+2]) ;
_icounter := _icounter + 2 ;
if ( _icounter = _elmentCount ) then
exit;
end if ;
end loop ;
return _sumedWxV;
end;
$Body$
LANGUAGE 'plpgsql' VOLATILE;
create aggregate wcost_average (numeric, numeric)(
sfunc = wcost_average_sf,
stype = numeric[],
initcond = '{0,0}',
finalfunc = wcost_average_ff
);
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
