一千萬個為什麽

搜索

SQL:在標準偏差內顯示平均值和最小值/最大值

我有以下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 devs內的數據)。

非常感謝

最佳答案

為了計算標準偏差,您需要遍歷所有元素,因此在一個查詢中無法執行此操作。懶惰的方式是兩次通過:

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
)

如果要丟棄一定數量的唯一值,請將 ROW_NUMBER 替換為 RANKDENSE_RANK

除了這些簡單的技巧,你開始進入一些相當沈重的統計數據。我必須處理類似的驗證,這對SO帖子來說太多了。您可以通過十幾種不同的方式調整一百種不同的算法。如果可能的話,我會盡量保持簡單!

轉載註明原文: SQL:在標準偏差內顯示平均值和最小值/最大值