Dear All,
I have written a STDEV aggregate function which seems to work; the only problem is that I am not sure that the method I
amusing was intended by the designers of aggregates.
I am using the first parameter of sfunc1 to store a pointer to a context record, and sfunc1 creates and returns that
pointerwhen it is first called (the DB definition specified initcond1 = '0'). In this way I can accumulate all the
detailsI need.
The problems is that the way I read the code, there is an expectation that the parameters of sfuncX and finalfunc match
thebasetype. This is clearly not the case, and I have declared them as int4, which will also presumable break on 64 bit
implementations...
Anyway, the code is below. Any suggestions as to how I should handle complex context within aggregates would be
appreciated.
Thanks,
Philip Warner.
-------------- STDEv Func:
typedef struct { float4 ssq; float4 s; float4 n;
} ctx_type;
int4
sdev_ag1(int4 ctxPtr, float4 *f2)
{ ctx_type *ctx; int4 *result;
if ((ctxPtr) == 0) { ctx = (ctx_type*)palloc(sizeof(ctx_type)); ctx->ssq = 0;
ctx->s = 0; ctx->n = 0; } else { ctx = (ctx_type*)(ctxPtr); };
ctx->ssq += (*f2) * (*f2); ctx->s += (*f2); ctx->n++;
return (int4)ctx;
}
float4*
sdev_fin(int4 ctxPtr, void* p)
{ ctx_type *ctx; float4 *result; float4 avg;
result = (float4*)palloc(sizeof(float4));
if ((ctxPtr) == 0) { (*result) = 0; } else { ctx = (ctx_type*)(ctxPtr);
avg = ctx->s / ctx->n; (*result) = (ctx->ssq - 2*avg*ctx->s)/ctx->n + avg*avg;
(*result)= sqrt((*result)); };
pfree(ctx);
return result;
}
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/