How the VARIANCE() function works in Mariadb?

In MariaDB, the VARIANCE() function is used to calculate this measure for a set of numbers.

Posted on

Variance is a statistical measure that represents the dispersion of a dataset relative to its mean. In MariaDB, the VARIANCE() function is used to calculate this measure for a set of numbers.

Syntax

The syntax for the MariaDB VARIANCE() function is as follows:

VARIANCE(expr)

Where expr is an expression, typically a column of numbers for which you want to calculate the variance. The function returns a double value that represents the variance of the input set of numbers.

Examples

Example 1: Calculating Variance for a Set of Values

This example calculates the variance for a set of integers in a table.

DROP TABLE IF EXISTS student_scores;
CREATE TABLE student_scores (score INT);
INSERT INTO student_scores (score) VALUES (70), (85), (90), (45), (80);

SELECT VARIANCE(score) FROM student_scores;

The output will be the variance of the scores.

+-----------------+
| VARIANCE(score) |
+-----------------+
|        254.0000 |
+-----------------+

Example 2: Variance with NULL Values

This example shows how the VARIANCE() function handles NULL values in the dataset.

DROP TABLE IF EXISTS measurements;
CREATE TABLE measurements (length DECIMAL(5,2));
INSERT INTO measurements (length) VALUES (10.5), (NULL), (14.3), (NULL), (9.6);

SELECT VARIANCE(length) FROM measurements;

The output will exclude NULL values from the calculation.

+------------------+
| VARIANCE(length) |
+------------------+
|         4.148889 |
+------------------+

Example 3: Variance of a Single Value

This example demonstrates the behavior of the VARIANCE() function when there is only one value in the dataset.

DROP TABLE IF EXISTS single_value;
CREATE TABLE single_value (number DECIMAL(5,2));
INSERT INTO single_value (number) VALUES (10.5);

SELECT VARIANCE(number) FROM single_value;

The output will be 0 because variance requires at least two values to calculate dispersion.

+------------------+
| VARIANCE(number) |
+------------------+
|         0.000000 |
+------------------+

Example 4: Variance of Empty Set

This example shows what happens when you try to calculate the variance of an empty set.

DROP TABLE IF EXISTS empty_set;
CREATE TABLE empty_set (value DECIMAL(5,2));

SELECT VARIANCE(value) FROM empty_set;

The output will be NULL since there are no values to calculate the variance.

+-----------------+
| VARIANCE(value) |
+-----------------+
|            NULL |
+-----------------+

Example 5: Grouped Variance Calculation

This example calculates the variance for different groups of data.

DROP TABLE IF EXISTS class_scores;
CREATE TABLE class_scores (class_id INT, score INT);
INSERT INTO class_scores (class_id, score) VALUES (1, 70), (1, 85), (1, 90), (2, 45), (2, 80);

SELECT class_id, VARIANCE(score) FROM class_scores GROUP BY class_id;

The output will show the variance calculated for each class.

+----------+-----------------+
| class_id | VARIANCE(score) |
+----------+-----------------+
|        1 |         72.2222 |
|        2 |        306.2500 |
+----------+-----------------+

Here are a few functions related to the MariaDB VARIANCE() function:

  • MariaDB STDDEV_POP() function calculates the population standard deviation of a set of numbers.
  • MariaDB STDDEV_SAMP() function calculates the sample standard deviation of a set of numbers.
  • MariaDB AVG() function calculates the average value of a set of numbers.

Conclusion

Understanding the VARIANCE() function in MariaDB is essential for performing statistical analysis on datasets. By following the syntax and examples provided, users can effectively utilize this function to gain insights into the variability of their data.