MySQL高级函数之：`STDDEV_POP()` 和 `STDDEV_SAMP()`：其在统计学标准差计算中的应用。

MySQL 高级函数：STDDEV_POP() 和 STDDEV_SAMP() – 统计学标准差计算中的应用

各位朋友，大家好！今天我们来深入探讨 MySQL 中两个非常有用的统计函数：STDDEV_POP() 和 STDDEV_SAMP()。 这两个函数都用于计算标准差，但它们在统计学上的意义和应用场景略有不同。 理解它们的差异，将有助于我们更准确地分析数据，做出更明智的决策。

1. 什么是标准差？

在深入研究 MySQL 函数之前，我们先回顾一下标准差的概念。标准差是统计学中衡量数据离散程度的一种度量。它反映了数据集中各个数值相对于平均值的偏离程度。标准差越大，表示数据越分散；标准差越小，表示数据越集中。

更具体地说，标准差是方差的平方根。方差是每个数据点与平均值之差的平方的平均数。

2. STDDEV_POP()：总体标准差

STDDEV_POP() 函数计算的是总体标准差，也称为Population Standard Deviation。 总体标准差考虑了数据集中的所有数据点，并将它们视为整个总体。 公式如下：

σ = √[ Σ(xi – μ)² / N ]

其中：

• σ (sigma) 代表总体标准差
• xi 代表数据集中的每个数据点
• μ (mu) 代表数据集的总体平均值
• N 代表数据集中数据点的总数

在 MySQL 中，STDDEV_POP() 函数接受一个数值类型的列作为参数，并返回该列中所有值的总体标准差。

示例：

假设我们有一个名为 sales 的表，其中包含每天的销售额数据：

CREATE TABLE sales (
  sale_date DATE,
  amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 120.00),
('2023-01-03', 110.00),
('2023-01-04', 130.00),
('2023-01-05', 140.00);

现在，我们可以使用 STDDEV_POP() 函数计算所有销售额的总体标准差：

SELECT STDDEV_POP(amount) AS population_stddev FROM sales;

执行结果将返回一个数值，表示 sales 表中 amount 列的总体标准差。

3. STDDEV_SAMP()：样本标准差

STDDEV_SAMP() 函数计算的是样本标准差，也称为Sample Standard Deviation。 样本标准差用于估计从总体中随机抽取的样本的标准差。它通常用于当无法获得整个总体的数据时，通过样本来推断总体的离散程度。 公式如下：

s = √[ Σ(xi – x̄)² / (n – 1) ]

其中：

• s 代表样本标准差
• xi 代表样本中的每个数据点
• x̄ (x bar) 代表样本的平均值
• n 代表样本中数据点的总数

注意公式中分母是 (n - 1)，而不是 n。 这是因为使用 (n - 1) 可以提供对总体标准差的无偏估计。 简单来说，如果不进行修正，样本标准差往往会低估总体标准差。

在 MySQL 中，STDDEV_SAMP() 函数接受一个数值类型的列作为参数，并返回该列中值的样本标准差。

示例：

使用上面的 sales 表，我们可以使用 STDDEV_SAMP() 函数计算销售额的样本标准差：

SELECT STDDEV_SAMP(amount) AS sample_stddev FROM sales;

执行结果将返回一个数值，表示 sales 表中 amount 列的样本标准差。

4. STDDEV() 函数：与哪个函数等价？

在 MySQL 中，STDDEV() 函数等价于 STDDEV_SAMP() 函数。 也就是说，STDDEV() 函数默认计算的是样本标准差。

SELECT STDDEV(amount) AS sample_stddev FROM sales; -- 与 STDDEV_SAMP(amount) 结果相同

5. 何时使用 STDDEV_POP() 和 STDDEV_SAMP()？

选择使用 STDDEV_POP() 还是 STDDEV_SAMP() 取决于你的数据是否代表整个总体，或者只是总体的一个样本。

• 如果你的数据代表整个总体，并且你想计算整个总体的标准差，那么应该使用 STDDEV_POP()。 例如，如果你收集了公司所有员工的年龄数据，并想计算员工年龄的总体标准差，那么应该使用 STDDEV_POP()。

• 如果你的数据只是总体的一个样本，并且你想使用样本来估计总体的标准差，那么应该使用 STDDEV_SAMP()。 例如，如果你随机抽取了公司 100 名员工的年龄数据，并想使用这些数据来估计所有员工年龄的标准差，那么应该使用 STDDEV_SAMP()。

总结：

函数名称	计算类型	应用场景
STDDEV_POP()	总体标准差	数据代表整个总体，需要计算整个总体的标准差。
STDDEV_SAMP()	样本标准差	数据只是总体的一个样本，需要使用样本来估计总体的标准差。
STDDEV()	样本标准差	等价于 STDDEV_SAMP()，默认计算样本标准差。

6. 实际应用场景

6.1 金融分析

在金融领域，标准差被广泛用于衡量投资组合的风险。 股票价格的波动性越高，标准差越大，风险也就越高。

例如，我们可以使用 STDDEV_POP() 或 STDDEV_SAMP() 函数计算某只股票在一段时间内的每日收益率的标准差，以此来评估该股票的风险。

假设我们有一个名为 stock_prices 的表，其中包含股票的每日收盘价：

CREATE TABLE stock_prices (
  stock_date DATE,
  closing_price DECIMAL(10, 2)
);

INSERT INTO stock_prices (stock_date, closing_price) VALUES
('2023-01-01', 10.00),
('2023-01-02', 10.50),
('2023-01-03', 11.00),
('2023-01-04', 10.80),
('2023-01-05', 11.20);

首先，我们需要计算每日收益率。 这里为了简化，我们直接使用今天的收盘价减去昨天的收盘价的差值作为收益率（更精确的计算应使用百分比变化）。

WITH daily_returns AS (
  SELECT
    stock_date,
    closing_price,
    closing_price - LAG(closing_price, 1, closing_price) OVER (ORDER BY stock_date) AS daily_return
  FROM
    stock_prices
)
SELECT
  STDDEV_SAMP(daily_return) AS risk
FROM
  daily_returns;

在这个查询中，我们使用 LAG() 函数获取前一天的收盘价，然后计算每日收益率。 最后，我们使用 STDDEV_SAMP() 函数计算每日收益率的样本标准差，作为股票的风险指标。 由于我们通常只拥有股票历史价格的一个样本，因此使用 STDDEV_SAMP() 更合适。

6.2 质量控制

在制造业中，标准差被用于衡量产品质量的稳定性。 如果产品尺寸的标准差过大，则表示产品质量不稳定，需要进行调整。

例如，我们可以使用 STDDEV_POP() 或 STDDEV_SAMP() 函数计算一批产品的尺寸的标准差，以此来评估产品质量的稳定性.

假设我们有一个名为 product_dimensions 的表，其中包含一批产品的尺寸数据：

CREATE TABLE product_dimensions (
  product_id INT,
  dimension DECIMAL(10, 2)
);

INSERT INTO product_dimensions (product_id, dimension) VALUES
(1, 10.00),
(2, 10.10),
(3, 9.90),
(4, 10.20),
(5, 9.80);

如果这张表包含了所有被生产的产品的尺寸数据，那么我们可以使用 STDDEV_POP() 函数计算产品尺寸的总体标准差：

SELECT STDDEV_POP(dimension) AS quality_stability FROM product_dimensions;

相反，如果这张表只是代表抽样检查的产品，那么使用 STDDEV_SAMP() 函数：

SELECT STDDEV_SAMP(dimension) AS quality_stability FROM product_dimensions;

6.3 网站性能分析

在网站性能分析中，标准差可以用于衡量网站响应时间的稳定性。 响应时间波动越大，用户体验就越差。

例如，我们可以使用 STDDEV_POP() 或 STDDEV_SAMP() 函数计算网站在一段时间内的响应时间的标准差，以此来评估网站性能的稳定性。

假设我们有一个名为 website_response_times 的表，其中包含网站的每日响应时间数据：

CREATE TABLE website_response_times (
  log_date DATE,
  response_time DECIMAL(10, 2)
);

INSERT INTO website_response_times (log_date, response_time) VALUES
('2023-01-01', 0.50),
('2023-01-02', 0.60),
('2023-01-03', 0.55),
('2023-01-04', 0.70),
('2023-01-05', 0.65);

和股票例子类似，我们通常只拥有网站响应时间的一个样本，因此使用 STDDEV_SAMP() 更合适。

SELECT STDDEV_SAMP(response_time) AS performance_stability FROM website_response_times;

6.4 销售数据分析

分析销售数据时，标准差可以用来衡量每日、每周或每月销售额的变化。 高标准差可能表明销售额波动较大，需要进一步调查原因。

假设我们有一个名为 daily_sales 的表：

CREATE TABLE daily_sales (
  sale_date DATE,
  sales_amount DECIMAL(10, 2)
);

INSERT INTO daily_sales (sale_date, sales_amount) VALUES
('2023-11-01', 1500.00),
('2023-11-02', 1600.00),
('2023-11-03', 1400.00),
('2023-11-04', 1700.00),
('2023-11-05', 1550.00);

计算销售额的标准差：

SELECT STDDEV_SAMP(sales_amount) AS sales_variation FROM daily_sales;

6.5 用户行为分析

分析用户行为数据时，标准差可以用来衡量用户在网站上花费的时间、点击次数等指标的变化。 这有助于识别异常用户行为，例如欺诈行为或机器人活动。

假设我们有一个名为 user_activity 的表：

CREATE TABLE user_activity (
  user_id INT,
  session_duration INT
);

INSERT INTO user_activity (user_id, session_duration) VALUES
(1, 120),
(2, 150),
(3, 90),
(4, 180),
(5, 130);

计算用户会话时长的标准差：

SELECT STDDEV_SAMP(session_duration) AS session_duration_variation FROM user_activity;

7. NULL 值处理

在计算标准差时，STDDEV_POP() 和 STDDEV_SAMP() 函数都会忽略 NULL 值。这意味着包含 NULL 值的行不会影响标准差的计算结果。

示例：

CREATE TABLE test_data (
  value INT
);

INSERT INTO test_data (value) VALUES
(10),
(20),
(NULL),
(30),
(40);

SELECT STDDEV_POP(value) AS population_stddev FROM test_data; -- 只计算 10, 20, 30, 40 的标准差
SELECT STDDEV_SAMP(value) AS sample_stddev FROM test_data; -- 只计算 10, 20, 30, 40 的标准差

8. 除数为零的错误

如果 STDDEV_SAMP() 函数应用于只有一个数据点的样本，由于公式中的分母是 (n - 1)，此时 n - 1 = 0，会导致除数为零的错误。 在这种情况下，STDDEV_SAMP() 函数会返回 NULL。 STDDEV_POP() 函数则不会出现这个问题，因为它使用 N 作为除数。

示例：

CREATE TABLE single_data (
  value INT
);

INSERT INTO single_data (value) VALUES
(10);

SELECT STDDEV_POP(value) AS population_stddev FROM single_data; -- 返回 0
SELECT STDDEV_SAMP(value) AS sample_stddev FROM single_data;   -- 返回 NULL

9. 高级用法：与 GROUP BY 子句结合使用

STDDEV_POP() 和 STDDEV_SAMP() 函数可以与 GROUP BY 子句结合使用，以计算每个分组的标准差。 这在分析分组数据时非常有用。

示例：

假设我们有一个名为 order_items 的表，其中包含订单的商品信息：

CREATE TABLE order_items (
  order_id INT,
  product_category VARCHAR(255),
  quantity INT,
  price DECIMAL(10, 2)
);

INSERT INTO order_items (order_id, product_category, quantity, price) VALUES
(1, 'Electronics', 2, 100.00),
(1, 'Clothing', 1, 50.00),
(2, 'Electronics', 1, 150.00),
(2, 'Furniture', 1, 200.00),
(3, 'Clothing', 2, 40.00),
(3, 'Furniture', 1, 100.00);

我们可以使用 GROUP BY 子句和 STDDEV_SAMP() 函数计算每个商品类别的平均价格和价格标准差：

SELECT
  product_category,
  AVG(price) AS average_price,
  STDDEV_SAMP(price) AS price_stddev
FROM
  order_items
GROUP BY
  product_category;

这个查询将返回每个商品类别的平均价格和价格标准差。

10.总结

通过今天的讲解，我们深入了解了 MySQL 中 STDDEV_POP() 和 STDDEV_SAMP() 函数的原理和应用。 掌握这两个函数，可以帮助我们更好地理解数据的离散程度，并在实际应用中做出更准确的判断。选择合适的函数取决于你的数据代表的是总体还是样本，理解这一点至关重要。 记住， STDDEV() 等价于 STDDEV_SAMP()，在使用时应注意区分。