SQL进阶技巧：如何分析基于绝对中位差的异常值检测问题？

会飞的一十六 2024-09-19

398

—

场景描述

数据集中可能存在异常的值。值存在异常有多种原因：

（1）可能是数据收集方式有问题，比如记录值的仪表存在误差；

（2）可能是数据输入错误导致的；

（3）还可能是因为数据生成时环境出现异常，这意味着数据点是正确的，但应谨慎根据数据得出任何结论。

鉴于此，我们想检测出异常数据。一种检测异常数据的常用方法是，计算数据的标准偏差，并将超过3 倍标准偏差（或其他类似距离）的数据点视为异常数据。

在统计学中，我们把这种检测异常值的方式称为中位数绝对偏差（Median Absolute Deviation，MAD）的方法，可以用来识别异常值并将其排除在外。MAD是一种基于中位数的离散度量，它可以帮助识别数据集中的异常值。

公式如下：

其中Xi为数据样本集中的值， Xm为该该数据样本集的中位数

具体算法如下：

计算数据集的中位数。
计算每个数据点与中位数的绝对偏差。
计算这些绝对偏差的中位数，即MAD。

通过设置阈值，例如MAD的倍数，来判断哪些数据点被认为是异常值。

—

数据准备

有如下薪水值，找出薪水值异常值

create table  sal as
(
  select stack(
 
        5,
        10000,
        2960,
        2975,
        3000,
        3000
    )
    as (sal)
);

—

问题分析

步骤1：计算中位值

计算中位值，在hive中有percentile()函数，在oracle或pg数据库可以使用percenttile_cont()函数更为准确。

如果没有分位值函数，我们利用计算公式可求解，参考文章：

SQL高阶技巧：一种中位数的分析技巧及思路

具体公式如下：

abs(rn - (cnt+1)/2) < 1

SQL如下：

方法1：直接利用分位值函数

select sal,percentile(sal,0.5) over() med
from sal;

方法2：利用公式法

select sal,
       avg( case when abs(rn - (cnt + 1)  2) <= 1  2 then sal end) over () med
from (select sal,
             row_number() over (order by sal) rn,
             count(1) over ()                 cnt
      from sal) t

步骤2：计算每个数据点与中位数的绝对偏差

select sal, abs(sal - med) dev
from (select sal,
             avg(case when abs(rn - (cnt + 1)  2) <= 1  2 then sal end) over () med
      from (select sal,
                   row_number() over (order by sal) rn,
                   count(1) over ()                 cnt
            from sal) t) t

步骤3：计算这些绝对偏差的中位数，即MAD

select sal, med, dev, percentile(cast(dev as int), 0.5) over () mad
from (select sal, med, abs(sal - med) dev
      from (select sal,
                   avg(case when abs(rn - (cnt + 1)  2) <= 1  2 then sal end) over () med
            from (select sal,
                         row_number() over (order by sal) rn,
                         count(1) over ()                 cnt
                  from sal) t) t) t

步骤4：计算标准偏差

select sal, dev, mad, dev  mad dev_mad_rate
from (select sal, med, dev, percentile(cast(dev as int), 0.5) over () mad
      from (select sal, med, abs(sal - med) dev
            from (
            select sal,
                         avg(case when abs(rn - (cnt + 1)  2) <= 1  2 then sal end) over () med
                  from (select sal,
                               row_number() over (order by sal) rn,
                               count(1) over ()                 cnt
                        from sal) t) t) t) t

通过上述结果绝对偏差(dev)与绝对偏差的中位数（mad）比值可以看出，当sal为10000时大于比值的三倍，为异常值。

—

小结

本文分析了一种基于绝对中位差的异常值检测问题，首先，计算中值，然后计算这个中值与各个值的绝对偏差的中值，即绝对中位差。然后，使用查询来找出每个值相对于中值的偏差与绝对中位差的比值。接下来，就可以像使用标准偏差那样使用这些比值了。如果一个值相对于中值的偏差是绝对中位差的 3 倍以上，就可以认为它是异常值。

相对于标准偏差，这种方法的优点是，即便数据不呈正态分布，它依然有效。例如，即便数据分布不平衡，绝对中位差给出的答案依然合理。在我们的薪水数据中，有一个薪水值相对于中值的偏差超过了绝对中位差的 3 倍，它就是 CEO 的薪水。CEO 的薪水比大多数员工的薪水高这么多，这是否公平呢？考虑到此异常薪水值是 CEO 的，这与我们对薪水数据的理解相符。在其他情况下，如果不能对如此异常的异常值做出明确解释，那么就有理由怀疑这个值是否正确，或者将其与其他值一起处理是否合理。（如果这个值是正确的，那么我们可能认为需要将数据分成多组进行分析）

很多常见的统计数据（比如平均值和标准偏差）会假设数据分布形状为钟形曲线，即呈正态分布。这样的数据集很多，但不呈正态分布的数据集也不少。检测数据集是否呈正态分布的方法很多，它们会将数据可视化并执行计算。统计包中通常包含执行这些检测的函数，但 SQL 中没有，而且使用 SQL 也难以实现这样的函数。然而，通常还有其他的统计工具（非参数统计），它们不假设数据集呈特定分布，因此使用起来更安全。

精彩回顾

SQL进阶的技巧：如何实现某列的累计乘积？

SQL进阶技巧：如何删除字符串中多余的字符？| 详解TRANSLATE()函数

SQL进阶技巧：如何提取JSON中 key值？

SQL进阶技巧：如何获取数组中前N个元素？

SQL进阶技巧：近N指标如何精确计算并扩充？

SQL进阶技巧：如何对数据进行两两组合分析？| 广告策略投放转化问题