答案：处理SQL聚合函数受异常值影响的核心是先识别后处理。通过IQR等方法识别异常值，再采用过滤、使用中位数或模拟截断均值等方式进行稳健聚合，并结合对比分析、业务验证和可视化评估结果可靠性。

SQL聚合函数在面对数据异常值时确实会变得“脆弱”，它们天生就容易被少数极端值拉偏，导致我们对数据整体趋势的判断出现偏差。核心思路是，我们得在聚合之前或聚合过程中，想办法识别并处理掉这些“捣乱分子”，或者选用那些对异常值不那么敏感的聚合方法。

解决方案

处理SQL聚合函数计算异常值的问题，我们通常需要一个两步走策略：首先是异常值的识别，然后才是基于识别结果进行处理或采用更稳健的聚合方式。

1. 异常值识别： 在SQL中，我们可以利用统计学方法来识别异常值。最常用的莫过于基于四分位数间距（IQR）的方法。通过计算数据的Q1（第一四分位数）、Q3（第三四分位数）和IQR，我们可以定义一个合理的“围栏”：任何超出

Q1 - 1.5 * IQR

或

Q3 + 1.5 * IQR

范围的数据点，都可以被认为是异常值。

2. 异常值处理与聚合： 一旦识别出异常值，处理方式就灵活了：

• 直接过滤： 这是最简单粗暴但也常常有效的方式。将识别出的异常值从数据集中排除，然后再进行

  AVG

  、

  SUM

  等聚合计算。

• 使用稳健的聚合函数： 如果数据库支持

  MEDIAN

  （中位数），这会是一个比

  AVG

  更抗异常值的选择。中位数只关心数据排序后的中间值，不受极端值影响。

• 模拟稳健聚合： 对于不支持

  MEDIAN

  或需要更精细控制的场景，我们可以通过SQL模拟实现“截断均值”（Trimmed Mean）或“温莎均值”（Winsorized Mean），即排除一定比例的极端值或将极端值替换为边界值再计算均值。

这些方法都能帮助我们获得更具代表性、更少受异常值干扰的聚合结果。

如何识别数据中的异常值？（SQL实践）

识别数据中的异常值，就像是在一堆看似正常的数据中找出那些“格格不入”的家伙。在SQL里，我们通常会借助一些统计学原理来完成这项任务。我个人最常用也觉得最直观的就是基于四分位数间距（IQR）的方法。这玩意儿说白了，就是给你的数据划定一个“正常范围”，超出去的就可能被视为异常。

具体怎么做呢？ 我们需要计算几个关键指标：

1. Q1 (第一四分位数): 25%的数据点小于或等于这个值。
2. Q3 (第三四分位数): 75%的数据点小于或等于这个值。
3. IQR (四分位数间距):

   Q3 - Q1

   。这代表了中间50%数据的散布范围。

4. 上下限 (Fences):
   • 下限 =

     Q1 - 1.5 * IQR
   • 上限 =

     Q3 + 1.5 * IQR

任何数值低于下限或高于上限的数据点，我们都可以初步判定为异常值。

SQL代码示例：

假设我们有一个

sales

表，其中包含

product_id

和

revenue

字段，我们想找出

revenue

中的异常值。

WITH Quartiles AS (     SELECT         PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY revenue) OVER () AS Q1,         PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue) OVER () AS Q3     FROM         sales ), IQR_Calc AS (     SELECT         Q1,         Q3,         Q3 - Q1 AS IQR     FROM         Quartiles     LIMIT 1 -- 确保只获取一行Q1, Q3, IQR ), OutlierBounds AS (     SELECT         Q1,         Q3,         IQR,         Q1 - 1.5 * IQR AS LowerBound,         Q3 + 1.5 * IQR AS UpperBound     FROM         IQR_Calc ) SELECT     s.product_id,     s.revenue,     CASE         WHEN s.revenue < ob.LowerBound OR s.revenue > ob.UpperBound THEN '是异常值'         ELSE '否'     END AS is_outlier FROM     sales s, OutlierBounds ob WHERE     s.revenue < ob.LowerBound OR s.revenue > ob.UpperBound; -- 只显示异常值

这里使用了

PERCENTILE_CONT

窗口函数，它在大多数现代SQL数据库（如PostgreSQL, SQL Server, Oracle）中都可用。如果你的数据库不支持，可能需要通过子查询和

ROW_NUMBER()

或

NTILE()

来模拟四分位数的计算，但这会复杂一些。

除了IQR，你也可以考虑基于Z-score的方法，它通过计算每个数据点与均值的标准差距离来判断。不过，Z-score本身对均值和标准差的计算就受异常值影响，所以在使用前通常需要对数据进行一些预处理，或者结合其他方法。我个人觉得IQR在很多业务场景下更直观，也更少需要对数据分布做假设。

在聚合计算中，有哪些策略可以减轻异常值的影响？

一旦我们识别出了异常值，接下来的任务就是在聚合时“驯服”它们，让它们不再干扰我们对数据整体的理解。这不像写代码那么直接，有时候更像是一门艺术，需要根据具体场景和业务目标来选择。

Imagen – Google Research

google Brain team推出的图像生成模型。

19

查看详情

1. 简单粗暴但有效的“过滤法”： 这是最直接也最常用的策略。如果异常值确实是数据录入错误、传感器故障或者完全不符合业务逻辑的极端情况，那么直接将它们从数据集中剔除，再进行聚合计算，是最干净利落的做法。

-- 假设我们已经通过某种方式识别出了异常值的ID或特征 WITH CleanedSales AS (     SELECT         product_id,         revenue     FROM         sales     WHERE         revenue BETWEEN (SELECT LowerBound FROM OutlierBounds) AND (SELECT UpperBound FROM OutlierBounds)         -- 或者通过ID过滤，例如：WHERE product_id NOT IN (SELECT outlier_product_id FROM identified_outliers) ) SELECT     AVG(revenue) AS average_revenue_cleaned,     SUM(revenue) AS total_revenue_cleaned FROM     CleanedSales;

这种方法的好处是结果清晰，容易解释。但缺点也很明显：你可能会丢失一些“真实”的极端情况，如果这些极端值本身蕴含了重要的业务信息（比如某个突然爆卖的单品），那直接过滤掉就可能错失洞察。

2. 选用“稳健”的聚合函数： 有些聚合函数天生就对异常值不那么敏感，比如中位数（

MEDIAN

）。

• 中位数（MEDIAN）： 它只关心数据排序后的中间值，无论数据两端有多大的极端值，都不会影响中位数。

  SELECT     PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) OVER () AS median_revenue FROM     sales;

  （注意：

  MEDIAN()

  函数在某些数据库中是直接支持的，例如Oracle。在PostgreSQL等中，可以通过

  PERCENTILE_CONT(0.5)

  来实现。）

• 截断均值（Trimmed Mean）： 这是一个非常实用的概念。它指的是在计算均值之前，先去除掉数据集中最高和最低的一定比例（例如1%或5%）的数据点。这样既能保留大部分数据的信息，又能有效抵御少数极端值的影响。

  -- 模拟计算10%截断均值 (即去除最高5%和最低5%) WITH RankedSales AS (     SELECT         revenue,         NTILE(20) OVER (ORDER BY revenue) as quartile_group -- 将数据分为20份，每份5%     FROM         sales ) SELECT     AVG(revenue) AS trimmed_mean_revenue FROM     RankedSales WHERE     quartile_group > 1 AND quartile_group < 20; -- 排除最低5%和最高5%

• 温莎均值（Winsorized Mean）： 与截断均值不同，温莎均值不是直接剔除极端值，而是将极端值“拉回”到某个边界值。例如，将所有高于上限的数值都替换为上限值，所有低于下限的数值都替换为下限值，然后再计算均值。这种方法在保留数据点数量的同时，减小了异常值的影响。

  -- 假设LowerBound和UpperBound已经计算好 WITH WinsorizedSales AS (     SELECT         CASE             WHEN revenue < (SELECT LowerBound FROM OutlierBounds) THEN (SELECT LowerBound FROM OutlierBounds)             WHEN revenue > (SELECT UpperBound FROM OutlierBounds) THEN (SELECT UpperBound FROM OutlierBounds)             ELSE revenue         END AS winsorized_revenue     FROM         sales ) SELECT     AVG(winsorized_revenue) AS winsorized_mean_revenue FROM     WinsorizedSales;

选择哪种策略，真的取决于你对数据的理解和业务需求。有时候，异常值本身就是重要的信息，比如欺诈交易或系统故障，这时候直接过滤可能就不合适了。

异常值处理后，如何评估聚合结果的可靠性？

处理完异常值，我们得到了新的聚合结果。但这些结果真的“可靠”吗？它们是不是更真实地反映了数据背后的趋势？这是一个需要我们反思和验证的环节，毕竟数据分析不是一次性的任务，而是一个迭代优化的过程。

1. 对比分析：处理前后的差异 最直接的方法就是把处理前和处理后的聚合结果放在一起比较。

• AVG(revenue)

  从 1000 变成了 800？这个 200 的差距，是合理的吗？

• SUM(revenue)

  减少了多少？这些减少的量是否主要来自于我们识别出的异常值？ 如果差异巨大，那么说明异常值对原始聚合结果的影响非常显著，我们采取的处理措施很可能是有价值的。但如果差异微乎其微，那可能意味着要么异常值本身就不多，要么我们的处理方法不够到位，需要重新审视。

2. 业务逻辑与常识的验证 数据分析的结果，最终还是要回到业务场景中去验证。

• 处理后的平均销售额，是否符合你对该产品或该时间段的业务预期？
• 如果聚合的是用户行为数据，处理后的平均活跃时长是否与用户画像相符？ 如果结果与业务常识严重偏离，那可能需要重新审视异常值的定义、处理方法，甚至是不是我们对数据的理解一开始就有偏差。有时候，那些被我们视为“异常”的数据，在某个特定的业务背景下，可能恰恰是“正常”的。

3. 可视化辅助判断 “一图胜千言”在这里尤其适用。

• 箱线图（Box Plot）： 处理前后的箱线图对比，能直观地看到数据分布的变化，特别是异常值被“驯服”后的效果。处理后的箱线图应该更紧凑，没有那么多孤立的点。
• 直方图（Histogram）： 看看数据分布是否变得更“正常”了，比如是否更接近正态分布（如果业务上期望如此）。极端值被移除或调整后，直方图的尾部应该会变得更平滑。 通过可视化，我们能更直观地感受数据变化，判断处理效果。

4. 敏感性分析：阈值的影响 如果你的异常值识别或处理方法依赖于某个阈值（比如IQR的1.5倍系数，或者截断均值的百分比），那么进行敏感性分析会很有帮助。

• 尝试改变这个阈值（例如，从1.5改为2.0，或者截断比例从5%改为10%）。
• 观察聚合结果如何随之变化。 如果结果对阈值非常敏感，那么你需要更谨慎地选择阈值，并解释其背后的理由。如果结果相对稳定，那说明你的处理方法是比较稳健的。

5. 记录与透明度 最后但同样重要的是，要详细记录你如何识别和处理异常值，以及这些处理对最终聚合结果产生了什么影响。这不仅能帮助你回顾和优化，也能确保你的分析结果是可追溯、可信赖的。在团队协作中，这种透明度尤其重要，避免其他人对你的数据报告产生疑问。

总而言之，评估可靠性不是一蹴而就的，它是一个需要结合统计学、业务知识和经验的综合过程。我们的目标是让聚合结果不仅在数学上“正确”，更要在业务上“有意义”。