VAR_POP

VAR_POP([DISTINCT] expr)

Description

The VAR_POP function is used to calculate the population variance of a set of numerical data. Variance is a statistical measure of the dispersion of data distribution, used to reflect the degree of volatility in the data. By calculating the population variance, we can understand the data's volatility, allowing for a deeper analysis of the data.

Parameter Description

• expr: Numerical type, can be tinyint, smallint, int, bigint, float, double, or decimal type.

  • DISTINCT: Optional parameter, indicating the calculation of the variance of the set after deduplication. If this parameter is not set, the variance of the set including duplicate values is calculated.

Return Result

• The return type is double.
• If all input values are null, null is returned.

Usage Example

1. Calculate the variance of the set including duplicate values:

   SELECT VAR_POP(col) FROM VALUES (1), (2), (3), (3), (null) AS tab(col);
   +----------------+
   | `VAR_POP`(col) |
   +----------------+
   | 0.6875         |
   +----------------+
   

2. Calculate the variance of the deduplicated set:

   SELECT VAR_POP(DISTINCT col) FROM VALUES (1), (2), (3), (3), (null) AS tab(col);
   +-------------------------+
   | `VAR_POP`(DISTINCT col) |
   +-------------------------+
   | 0.6666666666666666      |
   +-------------------------+