VARIANCE Function

variance([DISTINCT] expr)

Description

The VARIANCE function is an alias of VAR_SAMP and computes the sample variance of a set of numeric data. Variance is a statistical measure of data dispersion, reflecting the degree of variability in the data.

Parameters

• expr: A numeric type, which can be TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, or DECIMAL.
• DISTINCT (optional): specifies that the variance should be computed on the deduplicated set. If this parameter is not set, the variance is computed on the set including duplicates.

Return Type

• Returns a DOUBLE value representing the computed sample variance.

Notes

• If the input contains NULL values, they are excluded from computation.
• The VARIANCE function is fully identical to VAR_SAMP and the two can be used interchangeably.

Examples

1. Compute the sample variance including duplicates

SELECT variance(col) FROM VALUES (1), (2), (3), (3), (NULL) AS tab(col); +--------------------+ | `variance`(col) | +--------------------+ | 0.9166666666666666 | +--------------------+

2. Compute the sample variance on deduplicated values

SELECT variance(DISTINCT col) FROM VALUES (1), (2), (3), (3), (NULL) AS tab(col); +--------------------------+ | `variance`(DISTINCT col) | +--------------------------+ | 1.0 | +--------------------------+

3. Use FILTER clause to conditionally compute variance

SELECT variance(col) FILTER (WHERE col > 1) FROM VALUES (1), (2), (3), (4) AS tab(col); +--------------------------------------+ | variance(col) FILTER (WHERE col > 1) | +--------------------------------------+ | 1.0 | +--------------------------------------+

4. Compute variance by group

SELECT c, variance(col) FROM VALUES ('a', 1), ('a', 2), ('a', 3), ('b', 10), ('b', 20) AS tab(c, col) GROUP BY c; +---+--------------------+ | c | variance(col) | +---+--------------------+ | a | 1.0 | | b | 50.0 | +---+--------------------+