如何在SQL Server查询中隔离包含与该列中的其他值不同的值的行？

Question

我正在尝试在SSMS 2016中编写一个查询，该查询将隔离与列中其他值不同的组的值。我可以用一个例子更好地解释：

我们车队中的每一台设备都有一个小时的仪表读数，从手持设备上记录下来。有时，该领域的人员会输入打字错误的读数，这会影响我们每小时的读数。

因此，一个单位的仪表历史可能如下所示：

10/1/2019: 2000    
10/2/2019: 2208    
10/4/2019: 2208    
10/7/2019: 2212    
10/8/2019: 2    
10/8/2019: 2225   
...etc.

很明显，"2“是一个不好的记录，因为一个小时表永远不会减少。编辑:有时可能会出现相反的极端，他们输入类似"22155“的读数，然后我需要查询来适应太高的值，并将这些值隔离开来。此数据存储在仪表历史表中，其中每个仪表读数都有一行。我的任务是创建某种类型的过程，它将自动隔离坏数据并从表中删除这些行。我如何才能编写一个能够理解仪表历史的上下文并知道2是错误的查询呢？

欢迎任何提示，提前谢谢。

Answer 1

你可以使用filter去掉“rid”：

select t.*
from (select t.*, lag(col2) over (order by col1) as prev_col2
      from t
     ) t
where prev_col2 < col2;

我不建议“自动删除”这样的记录。

Answer 2

自动删除数据是有风险的，所以我不确定我是否建议在没有认真考虑的情况下释放它，但这是我的想法，基于你的样本数据，它通常是一个相当一致的数字。

DECLARE @Median numeric(22,0);
;with CTE as
(
select t.*, row_number() over (order by t.value) as "rn" from t
)
select @Median = cte.value
where cte.rn = (select (SUM( MAX(RN) + MIN(RN)) / 2 from cte); -- floors if dividing an odd number
select * from dataReadings where reading_value < (0.8 * @median) OR reading_value > (1.2 * @median);

这样做的目的是给你一个中位数的+/- 20%的范围，这个范围不应该像平均值那样被错误所扭曲。同样，这假设您的值应该落在可接受的范围内。

如果这是一个不断增加的读数，你不应该遇到更低的值，戈登的答案是完美的。

Answer 3

我想看看每个读数与平均读数的变化。(我也从@戈登·林诺夫的回复中拿到了lag()支票。)例如：

create table #test (the_date date, reading int)
insert #test (the_date, reading) values ('10/1/2019', 2000)
                                      , ('10/2/2019', 2208)
                                      , ('10/4/2019', 2208)
                                      , ('10/7/2019', 2212)
                                      , ('10/8/2019', 2)
                                      , ('10/8/2019', 2225)
                                      , ('10/8/2019', 2224)
                                      , ('10/9/2019', 22155)

declare @avg int, @stdev float
select @avg = avg(reading)
     , @stdev = stdev(reading) * 0.5
  from #test

select t.*
     , case when reading < @avg - @stdev then 'SUSPICIOUS - too low'
            when reading > @avg + @stdev then 'SUSPICIOUS - too high'
            when reading < prev_reading then 'SUSPICIOUS - decrease'
       end Comment
from (select t.*, lag(reading) over (order by the_date) as prev_reading
      from #test t
     ) t

这会导致：

the_date    reading prev_reading    Comment
2019-10-01  2000    NULL    NULL    
2019-10-02  2208    2000    NULL    
2019-10-04  2208    2208    NULL    
2019-10-07  2212    2208    NULL    
2019-10-08  2   2212    SUSPICIOUS - too low    
2019-10-08  2225    2   NULL    
2019-10-08  2224    2225    SUSPICIOUS - decrease   
2019-10-09  22155   2224    SUSPICIOUS - too high