The Variance function variance (x),
The specific methods are:
The variance s,a is the average of X1,X2...XN,
then S = [(x1-a) ^2+ (x2-a) ^2+ (x3-a) ^2+ ... (xn-a) ^2]/n
Standard deviation function StdDev (x),
The specific algorithm is:
The variance is radical.
Set the standard check for &, then &^2 = S
But I found out from the following experiments that Oracle calculates variance () in this way,
s = [(x1-a) ^2+ (x2-a) ^2+ (x3-a) ^2+ ... (xn-a) ^2]/n-1, note not dividing by N, but n-1
-----------------------------------------------------------------------------------
Experiment:
CREATE TABLE Test_stddev_variance (col1 number (9,2), col2 number (9,2));
INSERT into test_stddev_variance values (+);
INSERT into test_stddev_variance values (2,3);
INSERT into test_stddev_variance values (3,9);
INSERT into test_stddev_variance values (4,15);
SELECT * from Test_stddev_variance;
COL1 COL2
----- -----
1 1
2 3
3 9
4 15
SELECT VARIANCE (COL1), VARIANCE (COL2) from Test_stddev_variance;
VARIANCE (COL1) VARIANCE (COL2)
---------------------------------------- --------
1.66666666666666666666666666666666666667 40
select STDDEV (COL1), STDDEV (COL2) from Test_stddev_variance;
STDDEV (COL1) STDDEV (COL2)
-------------------------------------------------- --------
1.29099444873580562839308846659413320361 6.32455532033675866399778708886543706744
Note variance (COL1) = (1.5^2 + 0.5^2 + 0.5^2 + 1.5^2)/3 = 5/3 =1.66666666666666666666666666666666666667
And if the variance formula should be calculated as variance (COL1) = (1.5^2 + 0.5^2 + 0.5^2 + 1.5^2)/4 =5/4=1.25
Why is the computational formula for the grouping function of Oracle not [(X1-A) ^2+ (x2-a) ^2+ (x3-a) ^2+ ... (xn-a) ^2]/n, but
s = [(x1-a) ^2+ (x2-a) ^2+ (x3-a) ^2+ ... (xn-a) ^2]/n-1, really a bit of doubt, have to know the master please pointing.
Oracle grouping functions StdDev and variance, I have a question.