在SQL Server表中查找数据漂移

Question

我有一个包含以下字段的SQL Server表：

Id int
PName varchar(30)
PValue decimal(10,2)

一旦数据被加载到这个表中，我想对"PValue“运行一个查询，它是应该给出漂移值的decimal类型。请仔细阅读下面的示例数据

Id   PName  Pvalue 
1     A     15.2
2     A     15.8
3     A     17.9
4     A     14.2
5     A     21.0
6     A     53.2
7     A     14.4
8     A      1.6
9     A     16.8

现在，我希望查询返回的数据记录

6     A     53.2
8     A      1.6

与以前的记录和后来的记录相比，该值突然上升或下降。请帮助提供SQL查询以获取此类数据。

Answer 1

如果你必须在SQL中做到这一点，你可以这样做；

SELECT id, PName, PValue,
       CASE 
          WHEN ABS(PValue - AVG(PValue) OVER (PARTITION BY PName)) > 
               STDEV(PValue) OVER (PARTITION BY PName)
          THEN 1
          ELSE 0
       END AS deviates
FROM #TEST

其中#Test是您的示例数据；

CREATE TABLE #TEST (Id int,
                    PName varchar(30),
                    PValue decimal(10,2))

INSERT INTO #TEST (id,PName,PValue) 
VALUES (1,'A',15.2),
(2,'A',15.8),
(3,'A',17.9),
(4,'A',14.2),
(5,'A',21.0),
(6,'A',53.2),
(7,'A',14.4),
(8,'A', 1.6),
(9,'A',16.8)

它显示了偏离平均值超过1个标准差的地方。

如果您需要更具体地定义“.net”是什么，可以选择编写一个漂移CLR函数-这可能会给您带来更大的灵活性。

Answer 2

您需要做的是利用SQL Server中内置的R或Perl语言，并运行查询来确定大纲中的数据漂移。Here's an article on SQLTips展示了如何使用R发现数据中的异常值。

在您的示例中，您将同时查看min和max，因此您将希望将本文中的两个示例组合在一起。