mysql - 如何从 MySQL 表中删除大数据峰值

Question

我有一个被输入到图表中的数据库。出于某种原因，我的 PHP 脚本时常会在我的数据库中插入非常大的峰值...

例如:

如您所见，图表中存在随机尖峰，其中数据有时从 300 上升到 3000，然后又直接下降到 300。

我需要一种方法来整理这些表格并删除比前一行和下一行大得多的数据。

我在谷歌上做了一些研究，但一无所获!

提前致谢。

Answer 1

您可以采取一些措施来删除这些“离群值”数据点:

您可以删除与平均值相差超过 N 倍标准差的点。例如，如果数据是正态分布的，这将大致去除前 2.5%:

delete from datapoints where value > (select avg(value)+2*stddev(value) 
                                      from datapoints);

或者，您可以直接删除前 1% 的数据，保留第 99 个百分位的数据。有效地找到百分位点是一个更难的问题，但像这样的事情可能会奏效:

set @rownum = 0;
@percentile = select value from (select value, @rownum:=@rownum+1 as rownum from datapoints) D
              where rownum > (select 0.99*count(value) from datapoints) limit 1;
delete from datapoints where value > @percentile;

这些方法会删除所有通常异常大的数据点，而不考虑数据中的一般趋势或周期。这意味着山谷中的尖峰可能不会被发现。需要更高级的算法来处理这些情况。例如，您可以修改第一种方法以根据特定环境中的数据点删除异常值:

delete from datapoints d2 where value > 
    (select avg(value)+2*stddev(value) 
     from datapoints d1 
     where d1.dt between d2.dt - interval 2 hour 
                     and d2.dt + interval 2 hour);