代码之家 › 专栏 › 技术社区 › Craig Schwarze

SQL:显示标准偏差内的平均值和最小/最大值

sql-server-2005 sql

Craig Schwarze · 技术社区 · 15 年前

我有下面的sql表-

Date       StoreNo       Sales
23/4            34     4323.00
23/4            23      564.00
24/4            34     2345.00
etc

我正在运行一个查询,返回一定时期内的平均销售额、最大销售额和最小销售额-

select avg(Sales), max(sales), min(sales)
from tbl_sales
where date between etc

但是在最小值和最大值中有一些值是非常极端的——也许是因为数据输入不好,也许是因为在那个日期和存储区发生了一些异常。

我想要的是一个返回平均值、最大值和最小值的查询,但不知何故排除了极值。我对如何做到这一点持开放态度,但也许它会以某种方式使用标准差(例如,仅使用x std dev中的真实平均值数据)。

多谢

3 回复 | 直到 14 年前

Aaronaught 15 年前

为了计算标准差,需要遍历所有元素,因此在一个查询中不可能做到这一点。懒惰的方法就是两次传球:

DECLARE
    @Avg int,
    @StDev int

SELECT @Avg = AVG(Sales), @StDev = STDEV(Sales)
FROM tbl_sales
WHERE ...

SELECT AVG(Sales) AS AvgSales, MAX(Sales) AS MaxSales, MIN(Sales) AS MinSales
FROM tbl_sales
WHERE ...
AND Sales >= @Avg - @StDev * 3
AND Sales <= @Avg + @StDev * 3

另一个简单的选择可以工作(在科学数据分析中相当常见)就是降低最小值和最大值 X 值,如果有大量数据要处理,则可以使用。你可以使用 ROW_NUMBER 要在一个语句中执行此操作:

WITH OrderedValues AS
(
    SELECT
        Sales,
        ROW_NUMBER() OVER (ORDER BY Sales) AS RowNumAsc,
        ROW_NUMBER() OVER (ORDER BY Sales DESC) AS RowNumDesc
)
SELECT ...
FROM tbl_sales
WHERE ...
AND Sales >
(
    SELECT MAX(Sales)
    FROM OrderedValues
    WHERE RowNumAsc <= @ElementsToDiscard
)
AND Sales <
(
    SELECT MIN(Sales)
    FROM OrderedValues
    WHERE RowNumDesc <= @ElementsToDiscard
)

替换 小精灵 具有 RANK 或 DENSE_RANK 如果你想丢弃一定数量的 独特的 价值观。

除了这些简单的技巧,你开始进入一些相当重的统计。我必须处理类似的验证,这是太多的材料,一个这样的职位。有一百种不同的算法,你可以用十几种不同的方法来调整。如果可能的话,我会尽量保持简单!

Thomas 14 年前

在Duffymo的帖子上展开你可以做些

With SalesStats As
    (
    Select Sales, NTILE( 100 ) OVER ( Order By Sales ) As NtileNum
    From tbl_Sales
    )
Select Avg( Sales ), Max( Sales ), Min( Sales )
From SalesStats
Where NtileNum Between 5 And 95

这将排除最低的5%和最高的95%。如果你的数据变化很大,你可能会发现平均值不是一个质量汇总统计,应该考虑使用中位数。你可以这样做:

With SalesStats As
    (
    Select NTILE( 100 ) OVER ( Order By Sales ) As NtileNum
        , ROW_NUMBER() OVER ( Order By Id ) As RowNum
    From tbl_Sales
    )
    , TotalSalesRows
        (
        Select COUNT(*) As Total
        From tbl_Sales
        )
    , Median As
        (
        Select Sales 
        From SalesStats
            Cross Join TotalSalesRows
        Where RowNum In ( (TotalRows.Total + 1) / 2, (TotalRows.Total + 2) / 2 )
        )
Select Avg( Sales ), Max( Sales ), Min( Sales ), Median.Sales
From SalesStats
    Cross Join Median
Where NtileNum Between 5 And 95

duffymo 15 年前

也许你要找的是 percentiles .

标准差往往对异常值敏感,因为它是用一个值和平均值之差的平方来计算的。

也许一个更稳健、更不敏感的度量,比如一个值和平均值之间的绝对值,更适合你的情况。