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

小编典典

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

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中的数据）。

非常感谢

阅读 265

2021-05-30

共1个答案

小编典典

为了计算标准偏差，您需要遍历所有元素，因此不可能在一个查询中执行此操作。懒惰的方法是分两次通过：

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为RANK或。DENSE_RANK __

除了这些简单的技巧，您还可以开始获得一些非常重要的统计数据。我必须处理类似的验证，因此对于SO帖子而言，材料太多了。您可以用十二种不同的方式来调整一百种不同的算法。如果可能，我会尽量保持简单！

2021-05-30