【PostgreSQL数据分析实战：从数据清洗到可视化全流程】3.3 异常值识别（Z-score法/IQR法/业务规则法）

👉 点击关注不迷路
👉 点击关注不迷路
👉 点击关注不迷路

---

• 在数据分析中，异常值会干扰分析结果的准确性，识别异常值至关重要。
• 下面我将结合具体数据和表格，深入讲解 Z-score法、IQR法、业务规则法 三种异常值识别方法。

PostgreSQL数据分析实战：数据质量分析之异常值识别（Z-score法 / IQR法 / 业务规则法）

在数据分析领域，数据质量是决定分析结果可靠性和有效性的关键因素。

• 而异常值作为数据质量问题中的重要一环，会对统计分析、模型训练等后续工作产生严重干扰。
• 在使用PostgreSQL进行数据分析时，掌握高效的异常值识别方法至关重要。
• 本文将详细介绍Z-score法、IQR法以及业务规则法三种常用的异常值识别方法，并结合具体数据和表格进行深入剖析。

3.3 异常值识别

3.3.1 Z-score法

• Z-score法 基于统计学中的标准差概念，用于衡量数据点与均值之间的偏离程度。

• 其核心思想是通过计算每个数据点的Z-score值，判断该数据点是否属于异常值。Z-score的计算公式如下：
  
  一般来说，当Z-score的绝对值大于某个阈值（通常为2或3）时，该数据点被视为异常值。绝对值越大，说明数据点偏离均值越远。

• 下面我们通过一个具体的示例来演示如何在PostgreSQL中使用Z-score法识别异常值。

  • 假设有一个存储销售数据的表 sales，包含 sale_id（销售记录ID）、product_name（产品名称）、sale_amount（销售金额）等字段，我们要对 sale_amount 字段进行异常值识别。
  • 首先，计算 sale_amount 字段的均值和标准差：

  SELECT AVG(sale_amount) AS mean_amount,STDDEV(sale_amount) AS std_amount
  FROM sales;
  

  假设查询结果得到均值 mean_amount 为 100，标准差 std_amount 为 15。

  • 然后，计算每个数据点的Z-score值：

  SELECT sale_id,product_name,sale_amount,(sale_amount - 100) / 15 AS z_score
  FROM sales;
  

• 将上述查询结果整理成表格形式如下：

  sale_id	product_name	sale_amount	z_score
  1	产品A	85	-1
  2	产品B	130	2
  3	产品C	160	4
  4	产品D	95	-0.33
  5	产品E	110	0.67

从表格中可以看出，当设定阈值为3时，sale_id 为3的记录，其 z_score 值为4，超过了阈值，可判定为异常值。

• Z-score法的优点
  • 计算简单，适用于符合正态分布的数据。
  • 当数据分布不是正态分布时，该方法的有效性会受到影响，可能会误判或漏判异常值。

3.3.2 IQR法

IQR（Interquartile Range，四分位距）法是一种基于分位数的异常值识别方法，相比Z-score法，它对数据分布没有严格要求，在处理非正态分布数据时表现更优。

IQR的计算步骤如下：

• 1. 计算数据集的第一四分位数（$Q_1$）和第三四分位数（$Q_3$）。
• 2. 计算四分位距，$IQR=Q_3-Q_1$。
• 3. 确定异常值的范围：
  • 下限：$Q_1-1.5\times IQR$
  • 上限：$Q_3+1.5\times IQR$
    不在这个范围内的数据点被视为异常值。

同样以 sales 表的 sale_amount 字段为例，在PostgreSQL中实现IQR法识别异常值。

• 首先，计算 $Q_1$ 和 $Q_3$：

  SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sale_amount) AS Q1,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sale_amount) AS Q3
  FROM sales;
  

假设查询结果得到 $Q_1$ 为 80，$Q_3$ 为 120。

• 然后，计算 $IQR$、下限和上限：

  WITH IQR_CTE AS (SELECT (120 - 80) AS IQR
  )SELECT 80 - 1.5 * IQR AS lower_bound,120 + 1.5 * IQR AS upper_bound
  FROM IQR_CTE;
  

假设计算得到下限 lower_bound 为 20，上限 upper_bound 为 180。

• 最后，筛选出异常值：

  SELECT sale_id,product_name,sale_amount
  FROM sales
  WHERE sale_amount < 20 OR sale_amount > 180;
  

将查询结果整理成表格形式如下：

sale_id	product_name	sale_amount
3	产品C	160

从表格中可以看出，sale_id 为3的记录的 sale_amount 超出了计算得到的范围，被判定为异常值。

• IQR法的优势
  • 对数据分布的适应性强，能有效识别数据中的极端值。
  • 但也有局限性，比如对于数据量较小的数据集，分位数的计算可能不够准确，从而影响异常值的判断。

3.3.3 业务规则法

业务规则法是一种基于业务逻辑和领域知识来识别异常值的方法。

• 它与前面两种基于数学统计的方法不同，更依赖于对业务的理解。
• 例如，在电商业务中，商品的价格不可能为负数；
• 在考勤系统中，员工的工作时长一般不会超过24小时等。

假设我们有一个存储用户注册信息的表 user_registration，包含 user_id（用户ID）、registration_date（注册日期）、age（年龄）等字段。根据业务规则，用户年龄应该在18岁到100岁之间，我们可以通过以下SQL语句来识别异常值：

SELECT user_id,age
FROM user_registration
WHERE age < 18 OR age > 100;

将查询结果整理成表格形式如下：

user_id	age
5	15
8	105

从表格中可以看出，user_id 为5和8的记录的 age 值不符合业务规则，可判定为异常值。

• 业务规则法优点
  • 直观、针对性强，能够快速识别出不符合业务逻辑的异常数据。
  • 但缺点也很明显，需要深入了解业务，并且业务规则可能会随着业务的发展而变化，维护成本较高。

总结

Z-score法、IQR法和业务规则法各有优劣。

• 在实际的PostgreSQL数据分析中，我们可以根据数据的特点和业务需求，灵活选择合适的异常值识别方法；
• 也可以将多种方法结合使用，以更全面、准确地识别异常值，提高数据质量，为后续的数据分析和决策提供可靠的基础。
• 这些方法在实际应用中各有用武之地。若你在使用PostgreSQL识别异常值时遇到具体问题，或想了解更多相关技巧，欢迎和我分享。