由于数据不正确，创建一个查询来填补表中的空白
我有一个具有以下模式的表格：
DateTime [Creation Date] PK
int [Data]
列数据具有来自传感器的值，如下所示：
123
225
354
578
0
2151
2331
0
2555
2678
正如你所看到的，值总是增加。
由于传感器中存在问题，我们不时得到有效值之间的0。 当我们尝试使用数据时，这给我们带来了一些问题，所以我们想用这些东西填补这些空白。 理想情况下，我们希望在前一个值和下一个值之间取平均值，如果不可能，我们希望重复以前的值。
这只是一个查询可行吗？
提前致谢。
也许不是最有效的，但应该工作：
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来获取按Creation Date排序的连续数字。 然后这个数字用于根据其上一个值和下一个值获取新数据。
这是一个类似模式的演示（我用int代替了datetime ）：
更新
不错的一个，但它不处理与多个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;
演示 （在5,6上连续的零）
我有另一个变化：
SELECT
BadDate,
T1.Data AS PrevData,
T2.Data AS NextData,
(T1.Data + T2.Data) / 2 AS AvgValue
FROM
(
SELECT
T1.CreationDate As BadDate,
Max(T2.CreationDate) As PrevDate,
Min(T3.CreationDate) As NextDate
FROM
TestData T1,
TestData T2,
TestData T3
WHERE
T1.Data = 0
AND T2.Data <> 0
AND T2.CreationDate < T1.CreationDate
AND T3.Data <> 0
AND T3.CreationDate > T1.CreationDate
GROUP BY T1.CreationDate
) DateData
INNER JOIN TestData T1
ON DateData.PrevDate = T1.CreationDate
INNER JOIN TestData T2
ON DateData.NextDate = T2.CreationDate
如果您不担心获得平均值，则此方法可以为先前的值添加一个数字。
另外请注意，我不确定这种方法是否存在任何问题（除了更新所有记录），但仅表现为一种不同且简单的方法...
declare @new int = 1
update mytable
set @new = val = case when val = 0 then @new + 1 else val end
小提琴演示
| D | VAL |
---------------------
| 2013-01-01 | 123 |
| 2013-01-02 | 225 |
| 2013-01-03 | 354 |
| 2013-01-04 | 578 |
| 2013-01-05 | 579 |--Updated
| 2013-01-06 | 2151 |
| 2013-01-07 | 2331 |
| 2013-01-08 | 2332 |--Updated
| 2013-01-09 | 2555 |
| 2013-01-10 | 2678 |
上一篇: Create a query to fill the gaps in a table due to bad data