T-SQL用于规范化平均值

Question

我正在寻找一种计算给定数值集合的有用平均值的方法，这些数值可能包含巨大的峰值。(例如21、54、34、14、20、300、23或1、1、1、1、200、1、100)，使用标准平均值计算时，这些峰值会影响结果。

我考虑过使用中位数，但这并不能真正得到所需的结果。

我想在T-SQL中实现这个功能。

有什么想法吗？

- MakkyNZ

---

如果您不想要尖峰值，那么您需要考虑使用标准差。 - John Hartsock

Answer 1

使用中值滤波器：

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

- Quassnoi

Answer 2

这样你就可以在计算结果之前去掉最高和最低的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

- t-clausen.dk

Answer 3

1. 按对数规则创建 GROUP BY（例如数字之间的差异不超过10倍或任何其他对数的基数）
2. 通过非代表性组创建过滤器（使用 HAVING）（例如少于3个）

- Dewfy

Answer 4

这样做的危险在于，您无法确定所有这些峰值都是不重要的并且值得丢弃的。一个人的噪音可能是另一个人的黑天鹅。

如果您担心大量值会不必要地扭曲您对数据的看法，那么最好使用像中位数这样对异常值不太敏感的度量。它比平均值更难计算，但它将为您提供一个中心度量，不会被峰值影响太多。

- duffymo

Answer 5

您可以考虑使用OVER / PARTITION BY这样的窗口函数。这将允许您在特定行组（如按名称、日期或小时）中对排除进行微调。在此示例中，我从示例t-clausen.dk中借用行并添加了一个名称，以便我们可以演示窗口功能。

- 设置边界，例如上述示例中使用的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。但是，您可以根据自己的需求使用此技术来缓解峰值。

- Rayman