Subject | Re: Aggregate UDF? |
---|---|

Author | Svein Erling |

Post date | 2010-06-25T13:50:12Z |

--- In firebird-support@yahoogroups.com, Milan Babuskov wrote:

with CTE(MySum) as

(select avg(MyField*MyField)-(avg(MyField)*avg(MyField))

from TEST_TABLE)

select sqrt(MySum)

from CTE

So no need for any stored procedure,

Set

> PenWin wrote:Nothing is like trying, and to me, PenWins suggestions seems OK - except that he forgot the square root. At least, the following produced a sensible result for my first (and only) test:

> > Dne 25.6.2010 14:29, Michael Ludwig napsal(a):

> >> Svein Erling schrieb am 25.06.2010 um 11:16 (-0000):

> >>> [...] 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)?

> >>

> >> Yes, exactly. Conceptually, it cannot be done in one pass.

> >

> > Actually, it can.

> >

> > STDDEV(x) = AVG(x^2) - (AVG(x))^2

> >

> > AVG can be calculated in one pass.

>

> Isn't it rather:

>

> STDDEV(x) = AVG( x^2 - (AVG(x))^2 )

>

> ?

with CTE(MySum) as

(select avg(MyField*MyField)-(avg(MyField)*avg(MyField))

from TEST_TABLE)

select sqrt(MySum)

from CTE

So no need for any stored procedure,

Set