Subject Re: Aggregate UDF? Svein Erling 2010-06-25T11:16:57Z
> >> Well, let us assume a table T had an int field N, the table has
> >> 4 rows:
> >> {10,12,14,16}
> >>
> >> I need to do this:
> >>
> >> SELECT std_udf(N) FROM T;
> >>
> >> And it will return one value:
> >>
> >> 2.236
> >>
> >> Thus I need to 'create a custom aggregate function' or simple a
> >> "Aggregate UDF"
> >>
> > not in one call. You need to process the calculation in a stored procedure
> > at this time, using the available UDFs and built-in functions
>
> Alan,
>
> So how does it work with Firebird, is there some feature list out
> there which I could add this feature to?
>
> Cartoper

To me, a UDF sounds wrong and a built in function like something difficult to implement - I may well be wrong (my statistical knowledge is limited), but isn't it an aggregate function that requires 'going through' records twice (first finding the average and then how much each value deviates from this value)?

You can quite easily implement something similar in your own database, however. E.g.

SET TERM ^^ ;
CREATE PROCEDURE P_STDDEV (
FIELD VarChar(32),
FROMCLAUSE VarChar(1900))
returns (
STDDEV Double Precision)
AS
DECLARE VARIABLE MYAVERAGE DOUBLE PRECISION;
DECLARE VARIABLE MYSUM DOUBLE PRECISION;
DECLARE VARIABLE MYVALUE DOUBLE PRECISION;
DECLARE VARIABLE NORECORDS INTEGER;
DECLARE VARIABLE S VARCHAR(2048);
begin
S = 'SELECT COUNT('||FIELD||') FROM '|| FROMCLAUSE;
EXECUTE STATEMENT S INTO :NORECORDS;
S = 'SELECT AVG('||FIELD||') FROM '|| FROMCLAUSE;
EXECUTE STATEMENT S INTO :MYAVERAGE;
MYSUM = 0;
S = 'SELECT '||FIELD||' FROM '|| FROMCLAUSE;
FOR EXECUTE STATEMENT S INTO :MYVALUE DO
BEGIN
MYSUM=MYSUM+((MYVALUE-MYAVERAGE)*(MYVALUE-MYAVERAGE));
END
stddev = SQRT(MYSUM/NORECORDS);
SUSPEND;
end ^^
SET TERM ; ^^

and then you can call it like:

SELECT *
FROM P_STDDEV('MYFIELD', 'TEST_TABLE')

or

SELECT *
FROM P_STDDEV('MYFIELD', 'TEST_TABLE WHERE MYFIELD < 16')

I at least got the right result when I tried it on tiny table containing your four records (10, 12, 14, 16).

HTH,
Set