MySQL Standard Deviation

Summary: in this tutorial, you will learn about MySQL standard deviation functions to calculate populate standard deviation and sample standard deviation.

Introduction to standard deviation

Standard deviation is a measure of how spread-out the values in a data set are. The standard deviation shows how much variation exists from the average (mean). A low standard deviation shows that the values in the data set is close to the mean, while the high standard deviation indicates that the values of the data set are spread-out over a large range of values.

A standard deviation is a square root of the variance, which can be calculated by using the following steps:

• Step 1. Calculate the average of all values in the data set to get the average or mean e.g., suppose the data set consists of 1, 2 and 3, the mean is (1+2+3)/3 = 2.

• Step 2. Compute the difference of the value from the mean for each value, and square the result of each e.g., (1-2)²= (-1)² = 1, (2-2)² = (0)² = 0, (3-2)² = (1)² = 1.

• Step 3. Calculate the average values in the step 2, which produces the variance. And then take a square root of the variance to get the standard deviation of all values in the data set e.g., square root of ((1 + 0 + 1)/3) = 0.816497

Population standard deviation vs. sample standard deviation

If all values in the data set are taken into the calculation, this standard deviation is called population standard deviation. However, if a subset of values or a sample is taken into the calculation, this standard deviation is called sample standard deviation.

A sigma letter (σ) represents the standard deviation. The following equations illustrate how to calculate population standard deviation and sample standard deviation:

Population standard deviation:

Sample standard deviation:

The calculation of population standard deviation and sample standard deviation is slightly different. When calculating the variance of sample standard deviation, divide by N-1 instead of N, where N is the number of values in the data set.

MySQL standard deviation functions

MySQL makes it easy for you to calculate the population standard deviation and sample standard deviation.

To calculate population standard deviation, you use one of the following functions:

• STD(expression) - returns population standard deviation of the expression. The STD function returns NULL if there was no matching row.

• STDDEV(expression) - is equivalent to the STD function. It is provided to be compatible with Oracle Database only.

• STDEV_POP(expression) - is equivalent to the STD function.

To calculate the sample standard deviation, you use the STDDEV_SAMP (expression) function.

MySQL also provides some functions for population variance and sample variance calculation:

• VAR_POP(expression) - calculates the population standard variance of the expression.

• VARIANCE(expression) -  is equivalent to the VAR_POP function.

• VAR_SAMP(expression) - calculates the sample standard variance of the expression.

Examples of MySQL standard deviation functions

Let’s take a look at the orders table in the sample database.

Examples of population standard deviation functions

First, we create a new table named customerOrders and populate the customer numbers and orders’ counts from the orders table as follows:

CREATE TABLE customerOrders SELECT customerNumber, count(*) orderCount FROM orders GROUP BY customerNumber;

Second, to calculate the population standard deviation of the order’s counts of customers, you use the following statement:

SELECT FORMAT(STD(orderCount),2) FROM customerOrders;

Notice that the FORMAT function is used for formatting the result of the STD function.

Examples of sample standard deviation functions

Suppose you only want to evaluate only shipped orders in the orders table.

First, create a new table named customerShippedOrders and populate the customer numbers and shipped orders’ counts from the orders table.

CREATE TABLE customerShippedOrders SELECT customerNumber, count(*) orderCount FROM orders WHERE status = 'Shipped' GROUP BY customerNumber;

Second, calculate the sample standard deviation by using the STDDEV_SAMP as follows:

SELECT FORMAT(STDDEV_SAMP(orderCount),2) FROM customerShippedOrders;

In this tutorial, we have introduced you to standard deviation. Then, we showed you how to use the MySQL standard deviation functions to calculate population standard deviation and sample standard deviation of an expression.

原文链接：http://outofmemory.cn/mysql/function/mysql-standard-deviation