SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-167
Considerations for VARIANCE
Definition When Weight Is Not Specified
If weight is not specified, the statistical variance of the values in the one-column
result table is defined as:
where v
i
is the i-th value of expression, v is the average value expressed in the
common data type, and N is the cardinality of the result table.
Because the definition of variance has N-1 in the denominator of the expression (when
weight is not specified), NonStop SQL/MX returns a default value of zero (and no error)
if the number of rows in the table, or a group of the table, is equal to 1.
Definition When Weight Is Specified
If weight is specified, the statistical variance of the values in the two-column result
table is defined as:
where v
i
is the i-th value of expression, w
i
is the i-th value of weight, vw is the
weighted average value expressed in the common data type, and N is the cardinality of
the result table.
1
)(
1
2
−
−
∑
=
N
vv
N
i
i
∑
∑
=
=
−
⋅−
N
i
N
i
i
i
i
w
wvwv
1
1
2
1
)(