使用标准偏差使用mysql删除异常值

小编典典

使用标准偏差使用mysql删除异常值

sql

我使用下面的查询删除异常值（标准偏差的1.5倍）。

DELETE FROM sv_condition_sw 
WHERE snow_mountain > (
    SELECT AVG(snow_mountain)+1.5*STDDEV(snow_mountain) 
    FROM sv_condition_sw 
    WHERE lud='2012-11-28' AND res_id=769)
AND lud='2012-11-28' 
AND res_id=769

但是它给出了这个错误：

询问： delete FROM sv_condition_sw WHERE snow_mountain > (SELECT AVG(snow_mountain)+1.5*STDDEV(snow_mountain) FROM sv_condition_sw WHER...

Error Code: 1093
You can't specify target table 'sv_condition_sw' for update in FROM clause

我不知道这是什么意思。

阅读 196

2021-03-23

共1个答案

小编典典

您可以诱使MySQL通过另一个子选择来做到这一点

DELETE FROM sv_condition_sw 
WHERE snow_mountain > (select * from (SELECT AVG(snow_mountain)+1.5*STDDEV(snow_mountain) 
                                      FROM sv_condition_sw 
                                      WHERE lud='2012-11-28' 
                                      AND res_id=769) x)
 AND lud='2012-11-28' AND res_id=769

2021-03-23