On Wed, 21 Jan 2015, Andrew Dunstan wrote:
On 01/21/2015 09:27 AM, Arne Scheffer wrote:
Sorry, corrected second try because of copy&paste mistakes:
VlG-Arne
Comments appreciated.
Definition var_samp = Sum of squared differences /n-1
Definition stddev_samp = sqrt(var_samp)
Example N=4
1.) Sum of squared differences
1_4Sum(Xi-XM4)²
=
2.) adding nothing
1_4Sum(Xi-XM4)²
+0
+0
+0
=
3.) nothing changed
1_4Sum(Xi-XM4)²
+(-1_3Sum(Xi-XM3)²+1_3Sum(Xi-XM3)²)
+(-1_2Sum(Xi-XM2)²+1_2Sum(Xi-XM2)²)
+(-1_1Sum(Xi-XM1)²+1_1Sum(Xi-XM1)²)
=
4.) parts reordered
(1_4Sum(Xi-XM4)²-1_3Sum(Xi-XM3)²)
+(1_3Sum(Xi-XM3)²-1_2Sum(Xi-XM2)²)
+(1_2Sum(Xi-XM2)²-1_1Sum(Xi-XM1)²)
+1_1Sum(X1-XM1)²
=
5.)
(X4-XM4)(X4-XM3)
+ (X3-XM3)(X3-XM2)
+ (X2-XM2)(X2-XM1)
+ (X1-XM1)²
=
6.) XM1=X1 => There it is - The iteration part of Welfords Algorithm
(in
reverse order)
(X4-XM4)(X4-XM3)
+ (X3-XM3)(X3-XM2)
+ (X2-XM2)(X2-X1)
+ 0
The missing piece is 4.) to 5.)
it's algebra, look at e.g.:
http://jonisalonen.com/2013/deriving-welfords-method-for-computing-variance/
I have no idea what you are saying here.
I'm sorry for that statistics stuff,
my attempt was only to visualize in detail
the mathematical reason for
the iterating part of Welfords algorithm
being computing the current sum of squared differences in every step
- therefore it's in my opinion better to call the variable sum_of_squared_diffs
(every statistician will be confused bei "sum_of_variances",
because: sample variance = sum_of_squared_diffs / n-1,
have a look at Mr. Cooks explanation)
- therefore deviding by n-1 is the unbiased estimator by definition.
(have a look at Mr. Cooks explanation)
- therefore I suggested (as a minor nomenclature issue) to call the
column/description
stdev_samp (PostgreSQL-nomenclature) / sample_.... to indicate that
information.
(have a look at the PostgreSQL aggregate functions, it's doing that the same
way)
Here are comments in email to me from the author of
<http://www.johndcook.com/blog/standard_deviation> regarding the divisor
used:
My code is using the unbiased form of the sample variance, dividing
by n-1.
I am relieved, now we are at least two persons saying that. :-)
Insert into the commonly known definition
Definition stddev_samp = sqrt(var_samp)
from above, and it's exactly my point.
Maybe I should add that in the code comments. Otherwise, I don't think we
need a change.
Huh?
Why is it a bad thing to call the column "stddev_samp" analog to the
aggregate function or make a note in the documentation,
that the sample stddev is used to compute the solution?
I really think it not a good strategy having the user to make a test or dive
into the source code to determine the divisor used.
E.g. David expected stdev_pop, so there is a need for documentation for cases
with a small sample.
VlG-Arne
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
