STDDEV_POP

stddev_pop([distinct] expr)

Description

The STDDEV_POP function is used to calculate the population standard deviation of a set of numerical data. The standard deviation is a statistical measure of the dispersion of data distribution, used to indicate the degree of deviation of values in a dataset from the mean.

Parameter Description

• expr: The numerical data for which the standard deviation needs to be calculated. It can be of type tinyint, smallint, int, bigint, float, double, or decimal.
• distinct: Optional parameter indicating whether to calculate the standard deviation of the distinct set. If distinct is set, the function calculates the standard deviation of the non-duplicate data.

Return Result

• Returns a value of type double, representing the calculation result.
• If all input values are null, it returns null.

Usage Example

Example 1: Calculate the population standard deviation of a set of numerical data

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

Example 2: Calculate the overall standard deviation of deduplicated numerical data

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

By the above example, you can flexibly use the STDDEV_POP function to calculate the population standard deviation of a dataset according to your actual needs.