sql - 创建查询来填补表中由于数据错误而造成的空白

Question

我有一个具有以下架构的表:

DateTime [Creation Date] PK
int [Data]

列数据具有来自传感器的值，如下所示:

123
225
354
578
0
2151
2331
0
2555
2678

如您所见，该值始终递增。

由于传感器存在问题，我们有时会在有效值之间出现 0。当我们尝试使用数据时，这会给我们带来一些问题，因此我们想用一些东西来填补这些 0 空白。理想情况下，我们希望在前一个值和后一个值之间取平均值，如果不可能，我们希望重复前一个值。

仅通过查询就可以做到这一点吗？

提前致谢。

Answer 1

也许不是最有效的，但应该有效:

WITH cte 
     AS (SELECT [Creation Date], 
                Data, 
                rn=Row_number() OVER(ORDER BY [Creation Date]) 
         FROM   dbo.Table) 
UPDATE cte 
SET    Data = ( ( (SELECT c2.Data
                    FROM   cte c2 
                    WHERE  c2.rn = cte.rn - 1) 
                   + (SELECT c2.Data
                      FROM   cte c2 
                      WHERE  c2.rn = cte.rn + 1) ) / 2 ) 
WHERE  Data = 0;

我在 CTE 中使用 Row_Number 来获取按创建日期排序的连续数字。然后用这个数字根据它的上一个和下一个值来获取新的数据。

Here's具有类似架构的演示(我使用了 int 而不是 datetime):

更新

Nice one but it does not handle gaps with multiple 0

很好，这是考虑到这一点的修改后的 SQL:

WITH cte 
     AS (SELECT [Creation Date], 
                Data, 
                rn=Row_number() OVER(ORDER BY [Creation Date]) 
         FROM   dbo.Table) 
UPDATE cte 
SET    Data = ( ( (SELECT c2.Data
                    FROM   cte c2 
                    WHERE  c2.rn = (SELECT MAX(RN)FROM CTE c3 WHERE c3.RN<cte.RN AND c3.Data<>0)) 
                   + (SELECT c2.Data
                      FROM   cte c2 
                      WHERE  c2.rn = (SELECT MIN(RN)FROM CTE c3 WHERE c3.RN>cte.RN AND c3.Data<>0))) / 2 ) 
WHERE  Data = 0;

Demo (5,6 上有连续的零)