Neoview SQL Reference Manual (R2.3)

VARIANCE Function
• “Considerations for VARIANCE”
• “Examples of VARIANCE”
VARIANCE is an aggregate function that returns the statistical variance of a set of numbers.
VARIANCE is a Neoview SQL extension.
VARIANCE ([ALL | DISTINCT] expression [,weight])
ALL | DISTINCT
specifies whether duplicate values are included in the computation of the VARIANCE of the
expression. The default option is ALL, which causes duplicate values to be included. If
you specify DISTINCT, duplicate values are eliminated before the VARIANCE function is
applied. If DISTINCT is specified, you cannot specify weight.
expression
specifies a numeric value expression that determines the values for which to compute the
variance. expression cannot contain an aggregate function or a subquery. The DISTINCT
clause specifies that the VARIANCE function operates on distinct values from the one-column
table derived from the evaluation of expression.
weight
specifies a numeric value expression that determines the weights of the values for which to
compute the variance. weight cannot contain an aggregate function or a subquery. weight
is defined on the same table as expression. The one-column table derived from the
evaluation of expression and the one-column table derived from the evaluation of weight
must have the same cardinality.
Considerations for VARIANCE
Definition of VARIANCE
Suppose that vi are the values in the one-column table derived from the evaluation of
expression. N is the cardinality of this one-column table that is the result of applying the
expression to each row of the source table and eliminating rows that are null.
If weight is specified, wi are the values derived from the evaluation of weight. N is the
cardinality of the two-column table that is the result of applying the expression and weight
to each row of the source table and eliminating rows that have nulls in either column.
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 vi 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), Neoview SQL 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 vi is the i-th value of expression, wi 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.
Weighted Average
The weighted average vw of vi and wi is defined as:
VARIANCE Function 441