如何使用标准差去除异常值

Question

我有一组查询结果，看起来有点像：

customer|sales_growth_percentage
--------|-----------------------
A       |0.00
B       |0.00
C       |0.00
D       |15.05
E       |20.00
F       |25.10
G       |30.00
H       |500.00
I       |600.20

我想做的是计算以下内容，同时排除销售增长中的任何统计异常值(例如客户H&I(：

count(customer),
sum(sales_growth_percentage) 

我知道我可以使用STDDEV函数来计算标准偏差，对于上面的例子，它给了我224.84。有人知道如何使用这个来排除异常值吗？销售增长超过224.84的异常值真的有什么意义吗？

我想我可以将标准偏差保存为一个变量，将查询结果保存到一个临时表中，然后从其中sales_growth_percentage<224.84？这是最好的方法吗，还是有更有效的方法？

任何帮助都将不胜感激！

Answer 1

您可以预先计算CTE中的平均值和标准偏差，然后可以在主查询中使用它。例如：

with
stats (av, st) as (
select avg(sales_growth_percentage), stddev(sales_growth_percentage) from t
)
select *
from t
cross join stats s
where t.sales_growth_percentage between s.av - s.st and s.av + s.st

结果：

CUSTOMER  SALES_GROWTH_PERCENTAGE  AV                           ST              
--------- ------------------------ ---------------------------- --------------- 
A         0.00                     132.26111111111111111111111  224.83562857462 
B         0.00                     132.26111111111111111111111  224.83562857462 
C         0.00                     132.26111111111111111111111  224.83562857462 
D         15.05                    132.26111111111111111111111  224.83562857462 
E         20.00                    132.26111111111111111111111  224.83562857462 
F         25.10                    132.26111111111111111111111  224.83562857462 
G         30.00                    132.26111111111111111111111  224.83562857462 

请参阅db<gt；不停摆弄

Answer 2

您可以使用相应的OLAP函数：

/*
WITH MYTAB (customer, sales_growth_percentage) AS
(
VALUES
('A',   0.00)
, ('B',   0.00)
, ('C',   0.00)
, ('D',  15.05)
, ('E',  20.00)
, ('F',  25.10)
, ('G',  30.00)
, ('H', 500.00)
, ('I', 600.20)
)
*/
SELECT *
FROM
(
SELECT 
T.*
, STDDEV (sales_growth_percentage) OVER () AS ST 
, AVG (sales_growth_percentage) OVER ()    AS AV
FROM MYTAB T
)
WHERE sales_growth_percentage BETWEEN AV - ST AND AV + ST;

结果是：

ST>224.83562857461717>>

客户	SALES_GROWTH_PERCENTAGE	1AV
A	0.00	224.83562857461717	132.2611111111111111111111111
B	0.00	132.2611111111111111111111111
C	0.00	224.83562857461717	132.2611111111111111111111111
D	15.05	224.83562857461717	132.2611111111111111111111
E	20.00	224.83562857461717	132.2611111111111111111111111
F	25.10	224.83562857461717	132.2611111111111111111111
G	30.00	224.83562857461717	132.2611111111111111111111