我考虑过使用中位数,但这并不能真正得到所需的结果。
我想在T-SQL中实现这个功能。
有什么想法吗?
使用中值滤波器:
SELECT AVG(value)
FROM (
SELECT TOP 1 value AS median
FROM (
SELECT TOP 50 PERCENT value
FROM mytable
ORDER BY
value
) q
ORDER BY
value DESC
) q
JOIN mytable m
ON ABS(LOG10(value) - LOG10(median)) <= @filter_level
这样你就可以在计算结果之前去掉最高和最低的25%。
declare @t table (col1 int)
insert @t
select 21 union all
select 54 union all
select 34 union all
select 14 union all
select 20 union all
select 300 union all
select 23 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 200 union all
select 1 union all
select 100
select avg(col1) from (
select top 67 percent col1 from (
select top 75 percent col1 from @t order by col1
) a order by col1 desc) b
GROUP BY
(例如数字之间的差异不超过10倍或任何其他对数的基数)HAVING
)(例如少于3个)这样做的危险在于,您无法确定所有这些峰值都是不重要的并且值得丢弃的。一个人的噪音可能是另一个人的黑天鹅。
如果您担心大量值会不必要地扭曲您对数据的看法,那么最好使用像中位数这样对异常值不太敏感的度量。它比平均值更难计算,但它将为您提供一个中心度量,不会被峰值影响太多。
- 设置边界,例如上述示例中使用的TOP PERCENT
DECLARE @UBOUND FLOAT, @LBOUND FLOAT
SET @UBOUND = 0.8 --(80%) SET @LBOUND = 0.2 --(20%)
--建立CTE表 ;WITH tb_example AS ( select [Val]=21,[fname]='Bill' union all select 54,'Tom' union all select 34,'Tom' union all select 14,'Bill' union all select 20,'Bill' union all select 300,'Tom' union all select 23,'Bill' union all select 1,'Tom' union all select 1,'Tom' union all select 1,'Bill' union all select 1,'Tom' union all select 200,'Bill' union all select 1,'Tom' union all select 12,'Tom' union all select 8,'Tom' union all select 11,'Bill' union all select 100,'Bill' )
-- 外部查询应用您选择的条件来去除峰值 SELECT fname,AVG(Val) FROM ( -- 内部查询应用窗口聚合值以进行外部查询处理 SELECT * ,ROW_NUMBER() OVER (PARTITION BY fname order by Val) RowNum ,COUNT(*) OVER (PARTITION BY fname) RowCnt ,MAX(Val) OVER (PARTITION BY fname) MaxVal ,MIN(Val) OVER (PARTITION BY fname) MinVal FROM tb_example ) TB WHERE -- 您可以使用边界来消除前20%和后20% RowNum BETWEEN (RowCnt*@LBOUND) and (RowCnt*@UBOUND) -- 限制窗口 -- 或者您可以选择简单地消除最大值和最小值 OR (Val > MinVal AND Val < MaxVal) -- 移除最低和最高值 GROUP BY fname
在这种情况下,我使用了两个条件,并按fname平均val。但是,您可以根据自己的需求使用此技术来缓解峰值。