MySQL - 标准差

MySQL: 计算标准差

标准差（Standard Deviation）是一种统计量，用于衡量一组数据值的分散程度或离散程度。低标准差表示这些值趋向于接近数据集的平均值（或期望值），而高标准差则表示这些值分布在一个更宽的范围内。

MySQL 提供了几个聚合函数，可以直接在查询中计算标准差。

总体标准差与样本标准差

在使用这些函数之前，理解总体（Population）和样本（Sample）之间的区别至关重要：

• 总体： 指您正在研究的群体中的每个成员。例如，如果您有一个包含公司所有员工的表格，那么这就是总体。
• 样本： 是一个更大总体的更小、可管理子集。例如，如果您调查 100 名随机员工来估计整个公司的满意度，那么这 100 人的群体就是一个样本。

它们的计算公式略有不同，MySQL 为每种情况都提供了单独的函数。

MySQL 标准差函数

• 总体标准差： - STDDEV_POP(expression): 这是标准的、明确的函数，推荐使用以保持清晰性。 - STD(expression): STDDEV_POP() 的同义词。 - STDDEV(expression): STDDEV_POP() 的另一个同义词，为与其他数据库系统兼容而提供。
• 样本标准差： - STDDEV_SAMP(expression): 这是用于计算样本标准差的函数。

如果查询没有返回行，或者表达式对所有行都求值为 NULL，这些函数将返回 NULL。

实际示例

让我们使用 employees 表，假设这些数据代表一家小公司的全体员工总体。

-- 如果您需要创建表：
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

INSERT INTO employees (department, salary) VALUES
('Engineering', 95000.00), ('Engineering', 110000.00),
('Sales', 72000.00), ('Sales', 68000.00),
('HR', 65000.00), ('Engineering', 125000.00),
('Sales', 88000.00);

1. 计算所有薪资的总体标准差

为了找出公司薪资的整体离散度，我们使用 STDDEV_POP()。

SELECT STDDEV_POP(salary) AS salary_std_dev FROM employees;

salary_std_dev
20387.56417387

2. 计算样本标准差

如果我们将数据视为一个更大组织的样本，我们将使用 STDDEV_SAMP()。请注意，结果略大。

SELECT STDDEV_SAMP(salary) AS salary_sample_std_dev FROM employees;

salary_sample_std_dev
21984.87103738

3. 将标准差与 GROUP BY 结合使用

一个更强大的用法是比较每个部门内部的薪资变动。

SELECT
    department,
    AVG(salary) AS average_salary,
    STDDEV_POP(salary) AS salary_std_dev
FROM
    employees
GROUP BY
    department;

这个查询揭示了工程部门的平均薪资更高，但薪资变动也比销售部门大得多。

department	average_salary	salary_std_dev
Engineering	110000.00	12472.19128925
Sales	76000.00	8944.27191
HR	65000.00	0.0

现代方法：使用窗口函数

自 MySQL 8.0 起，您可以将标准差函数用作窗口函数。这允许您在不使用 GROUP BY 折叠行的情况下，将偏差显示在每行数据旁边。

例如，让我们显示每个员工的薪资以及他们所在整个部门的标准差。

SELECT
    id,
    department,
    salary,
    STDDEV_POP(salary) OVER (PARTITION BY department) AS department_salary_std_dev
FROM
    employees;

OVER (PARTITION BY department) 子句告诉 MySQL 为具有相同 department 值的每组行计算标准差。

id	department	salary	department_salary_std_dev
1	Engineering	95000.00	12472.19128925
2	Engineering	110000.00	12472.19128925
6	Engineering	125000.00	12472.19128925
5	HR	65000.00	0.0
3	Sales	72000.00	8944.27191
4	Sales	68000.00	8944.27191
7	Sales	88000.00	8944.27191

这种方法对于直接从数据库创建详细分析报告非常强大。