Subject Re: Standard Deviation arnofab2000 2008-12-30T09:45:47Z
--- In firebird-support@yahoogroups.com, Richard Wesley <hawkfish@...>
wrote:
>
>
> On 29 Dec 2008, at 04:54, mielhostens wrote:
>
> > Anybody who knows if there is a UDF or stored procedure to calculate
> > the standard deviation of a variable column?
>
> You can't create plug-in aggregators. For the common statistical
> aggregators, you can use:
>
> STDEV
> (CASE WHEN COUNT(%1) > 1 THEN
> SQRT((COUNT(%1) * SUM(POWER(%1,2)) - POWER(SUM(%1), 2) ) /
> (COUNT(%1)*(COUNT(%1) - 1)))
> ELSE CAST(NULL AS DOUBLE PRECISION) END)
>
> STDEVP
> (CASE WHEN COUNT(%1) > 0 THEN
> SQRT((COUNT(%1) * SUM(POWER(%1,2)) - POWER(SUM(%1), 2) ) /
> (POWER(COUNT(%1),2)))
> ELSE CAST(NULL AS DOUBLE PRECISION) END)
>
> VAR
> (CASE WHEN COUNT(%1) > 1 THEN
> ((COUNT(%1) * SUM(POWER(%1,2)) - POWER(SUM(%1), 2) ) /
> (COUNT(%1)*(COUNT(%1) - 1)))
> ELSE CAST(NULL AS DOUBLE PRECISION) END)
>
> VARP
> (CASE WHEN COUNT(%1) > 0 THEN
> ((COUNT(%1) * SUM(POWER(%1,2)) - POWER(SUM(%1), 2) ) /
> (POWER(COUNT(%1),2)))
> ELSE CAST(NULL AS DOUBLE PRECISION) END)
>
> HTH,

Hi Richard, Very interesting code.

Please, can You explain a bit more?

VARP means?
What is %1?
CAn You provide an example code?

TIA

Arn