Mysql 简明教程
MySQL - Standard Deviation
MySQL 标准差函数是用于计算数据集中的值之间的变化或分散的数学函数。
MySQL Standard Deviation Functions are mathematical functions that are used to calculate the variation or dispertion between values in a dataset.
MySQL 中有两种类型的标准差，它们是总体标准差和样本标准差。
There are two types of standard deviations in MySQL, they are population standard deviation and sample standard deviation.
Popular standard deviation
“总体标准差”是数据集方差的平方根。它计算总体内部的变化量或离散度。用符号表示，它用 σ（希腊字母西格玛）表示。
The "population standard deviation" is the square root of the variance of a set of data. It calculates the amount of variation or dispersion within a population. Symbolically it is represented by σ (the Greek letter sigma).
要计算总体标准差，我们可以使用以下函数：
To calculate population standard deviation, we can use the following functions:
-
STD(expression): It calculates and returns the population standard deviation the fields in a particular column. If the specified row(s) doesn’t exist this function returns NULL.
-
STDDEV(expression): It is same as STD() function, but it also works with oracle database.
-
STDDEV_POP(expression): It is equivalent to STD() function.
以下是计算“总体标准差”的数学公式：
Following is the mathematical formula to calculate the "population standard deviation":
// Mathematical Formula
$\sigma = \sqrt{\frac{\sum_{i=1}^n{(x-\bar x)^2}}{N-1}}$其中，
Where,
-
σ = population standard deviation
-
N = size of the population
-
Xi = each value from the population
-
meu = the population mean
Example
首先，让我们使用 CREATE 语句创建一个名为 CUSTOMERS 的表，如下所示 −
First, let us create a table with the name CUSTOMERS using the CREATE statement as shown below −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(15) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR(25),
SALARY DECIMAL(10, 2),
PRIMARY KEY(ID)
);现在，让我们使用 INSERT 语句向 CUSTOMERS 表中插入值 -
Now, let us insert values into the CUSTOMERS table using the INSERT statement −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2000),
(4, 'Chaitali', '26', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'Hyderabad', 9000),
(7, 'Muffy', '24', 'Indore', 5500);表创建如下 −
The table is created as −
The STD() Function
以下查询计算了表演赛中所有选手的得分总体标准差 −
The following query calculates the population standard deviation of scores of all players in exhibition match −
SELECT STD(AGE) from CUSTOMERS;The STDDEV() Function
STDDEV() 函数与 STD() 函数相同，但它也适用于 Oracle 数据库。
The STDDEV() function is the same as STD() function, but it will also work with oracle database.
在以下查询中，我们针对“Score_In_Exhibition_Match”列计算总体标准差 −
In the following query, we are calculating the population standard deviation on "Score_In_Exhibition_Match" column −
SELECT STDDEV(AGE) FROM CUSTOMERS;The STDDEV_POP() Function
在 MySQL 中，STDDEV_POP() 函数等同于 STD() 函数。在此处，我们对 CUSTOMERS 表的 AGE 列执行总体标准差。
In MySQL, the STDDEV_POP() function is equivalent to the STD() function. Here, we are performing the population standard deviation on AGE column of CUSTOMERS table.
SELECT STDDEV_POP(AGE) FROM CUSTOMERS;Sample Standard Deviation
MySQL 标准差是方差的平方根，用于计算数据的离散程度或分散程度。
The MySQL standard deviation is the square root of the variance, which calculates how dispersed or spread out the data is.
STDDEV_SAMP() 函数用于计算列中一组值的样本标准差。
The STDDEV_SAMP() function is used to calculate the sample standard deviation of a set of values in a column.
以下是计算“样本标准差”的公式：
Following is the formula to calculate the "Sample Standard Deviation":
// Mathematical formula
s = sqrt(sum((x - mean)^2) / (n - 1))