sql中插值法完成缺失数据的填充

Question

从Excel中导入了一批数据到Sqlserver，但因为原始数据不全，中间有些数据漏掉了。比如下面这种情况。ID为2的so数据为0。ID为3，4的co1数据缺失了，暂时用0代替。
ID  so    co1
1   0.1   0.1
2   0     0.2
3   0.2   0
4   0.25  0
5   0.2   0.4使用差值法将这些缺失的数据补齐。插值计算方法如下：（也可以不使用这两个步骤，只要最后的结果一致就行）
步骤一：计算缺失值上下的已知值间的斜率：
k = (b2 - b1)/(n + 1)   n 为缺失数据的个数
步骤二：计算对应的缺失值
a(i) = b1 + k * i
经过处理后，得到的数据是这样的：
ID  so    co1
1   0.1   0.1
2   0.15  0.2
3   0.2   0.27
4   0.25  0.33
5   0.2   0.4现在希望在sqlserver中写一个存储过程，自动完成上述过程。

Answer 1

b1 b2 k i 都代表什么？

Answer 2

so，co1为原始表的字段，这样的字段一共有七八个。所以一次可以只考虑一个字段的缺失值填充。
b2 b1是缺失数据前后的正常数据。比如
ID  co1 
1   0.1 
2   0.2 
3    0 
4    0 
5   0.4
这里b2为ID=5，b1为ID=2的数据。b2和b1需要在sql过程中去判断。
k是插值的斜率
i为第几个缺失数据。比如这里在填充ID为3，co1的数据时，i=1。填充ID为4，co1的数据时，i=2。
---------
我想办法贴个图上来。

Answer 3

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
IF OBJECT_ID('FUN_SO') IS NOT NULL DROP FUNCTION FUN_SO
IF OBJECT_ID('FUN_CO1') IS NOT NULL DROP FUNCTION FUN_CO1
GO
CREATE TABLE TB(
ID INT,
SO NUMERIC(19,6),
CO1 NUMERIC(19,6)
)
INSERT INTO TB
SELECT 1,  0.1,  0.1 UNION ALL
SELECT 2,  0,    0.2 UNION ALL
SELECT 3,  0.2,  0 UNION ALL
SELECT 4,  0.25,  0 UNION ALL
SELECT 5,  0.2,  0.4 
GO
CREATE FUNCTION FUN_SO(@ID INT)
RETURNS NUMERIC(19,6)
AS
BEGINDECLARE @NUM1 NUMERIC(19,6),@ID1 INT,@NUM2 NUMERIC(19,6),@ID2 INT
SELECT TOP 1 @ID1=ID , @NUM1=SO  FROM TB WHERE ID<=@ID AND SO<>0 ORDER BY ID DESCSELECT TOP 1 @ID2=ID , @NUM2=SO  FROM TB WHERE ID>=@ID AND SO<>0 ORDER BY ID ASC
IF @ID2<>@ID1
RETURN @NUM1+(((@NUM2-@NUM1)/(@ID2-@ID1))*(@ID-@ID1))RETURN @NUM1
END
GO
CREATE FUNCTION FUN_CO1(@ID INT)
RETURNS NUMERIC(19,6)
AS
BEGINDECLARE @NUM1 NUMERIC(19,6),@ID1 INT,@NUM2 NUMERIC(19,6),@ID2 INT
SELECT TOP 1 @ID1=ID , @NUM1=CO1  FROM TB WHERE ID<=@ID AND CO1<>0 ORDER BY ID DESCSELECT TOP 1 @ID2=ID , @NUM2=CO1  FROM TB WHERE ID>=@ID AND CO1<>0 ORDER BY ID ASC
IF @ID2<>@ID1
RETURN @NUM1+(((@NUM2-@NUM1)/(@ID2-@ID1))*(@ID-@ID1))RETURN @NUM1
END
GO
SELECT ID,DBO.FUN_SO(ID),DBO.FUN_CO1(ID) FROM TB/*
1 0.100000 0.100000
2 0.150000 0.200000
3 0.200000 0.266667
4 0.250000 0.333333
5 0.200000 0.400000
*/

Answer 4

按LZ的要求换成2位精度
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
IF OBJECT_ID('FUN_SO') IS NOT NULL DROP FUNCTION FUN_SO
IF OBJECT_ID('FUN_CO1') IS NOT NULL DROP FUNCTION FUN_CO1
GO
CREATE TABLE TB(
ID INT,
SO NUMERIC(19,2),
CO1 NUMERIC(19,2)
)
INSERT INTO TB
SELECT 1,  0.1,  0.1 UNION ALL
SELECT 2,  0,    0.2 UNION ALL
SELECT 3,  0.2,  0 UNION ALL
SELECT 4,  0.25,  0 UNION ALL
SELECT 5,  0.2,  0.4 
GO
CREATE FUNCTION FUN_SO(@ID INT)
RETURNS NUMERIC(19,2)
AS
BEGINDECLARE @NUM1 NUMERIC(19,2),@ID1 INT,@NUM2 NUMERIC(19,2),@ID2 INT
SELECT TOP 1 @ID1=ID , @NUM1=SO  FROM TB WHERE ID<=@ID AND SO<>0 ORDER BY ID DESCSELECT TOP 1 @ID2=ID , @NUM2=SO  FROM TB WHERE ID>=@ID AND SO<>0 ORDER BY ID ASC
IF @ID2<>@ID1
RETURN @NUM1+(((@NUM2-@NUM1)/(@ID2-@ID1))*(@ID-@ID1))RETURN @NUM1
END
GO
CREATE FUNCTION FUN_CO1(@ID INT)
RETURNS NUMERIC(19,2)
AS
BEGINDECLARE @NUM1 NUMERIC(19,2),@ID1 INT,@NUM2 NUMERIC(19,2),@ID2 INT
SELECT TOP 1 @ID1=ID , @NUM1=CO1  FROM TB WHERE ID<=@ID AND CO1<>0 ORDER BY ID DESCSELECT TOP 1 @ID2=ID , @NUM2=CO1  FROM TB WHERE ID>=@ID AND CO1<>0 ORDER BY ID ASC
IF @ID2<>@ID1
RETURN @NUM1+(((@NUM2-@NUM1)/(@ID2-@ID1))*(@ID-@ID1))RETURN @NUM1
END
GO
SELECT ID,DBO.FUN_SO(ID),DBO.FUN_CO1(ID) FROM TB/*
1 0.10 0.10
2 0.15 0.20
3 0.20 0.27
4 0.25 0.33
5 0.20 0.40
*/

Answer 5

你的方法通过排序的方式求得的@NUM1和@NUM2，但缺失数据多的时候，不再适用了啊。比如：
ID  so    co1 
1  0.1  0.1 
2  0    0.2 
3  0.2  0 
4  0    0 
5  0    0.4 
6  0.1  0.5
------------
ID为2的so，ID为3、4的so数据。

Answer 6

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
IF OBJECT_ID('FUN_SO') IS NOT NULL DROP FUNCTION FUN_SO
IF OBJECT_ID('FUN_CO1') IS NOT NULL DROP FUNCTION FUN_CO1
GO
CREATE TABLE TB(
ID INT,
SO NUMERIC(19,2),
CO1 NUMERIC(19,2)
)
INSERT INTO TB
SELECT 1,  0.1,  0.1 union all
SELECT 2,  0,    0.2 union all
SELECT 3,  0.2,  0 union all
SELECT 4,  0,    0 union all
SELECT 5,  0,    0.4 union all
SELECT 6,  0.1,  0.5 
GO
CREATE FUNCTION FUN_SO(@ID INT)
RETURNS NUMERIC(19,2)
AS
BEGINDECLARE @NUM1 NUMERIC(19,2),@ID1 INT,@NUM2 NUMERIC(19,2),@ID2 INT
SELECT TOP 1 @ID1=ID , @NUM1=SO  FROM TB WHERE ID<=@ID AND SO<>0 ORDER BY ID DESCSELECT TOP 1 @ID2=ID , @NUM2=SO  FROM TB WHERE ID>=@ID AND SO<>0 ORDER BY ID ASC
IF @ID2<>@ID1
RETURN @NUM1+(((@NUM2-@NUM1)/(@ID2-@ID1))*(@ID-@ID1))RETURN @NUM1
END
GO
CREATE FUNCTION FUN_CO1(@ID INT)
RETURNS NUMERIC(19,2)
AS
BEGINDECLARE @NUM1 NUMERIC(19,2),@ID1 INT,@NUM2 NUMERIC(19,2),@ID2 INT
SELECT TOP 1 @ID1=ID , @NUM1=CO1  FROM TB WHERE ID<=@ID AND CO1<>0 ORDER BY ID DESCSELECT TOP 1 @ID2=ID , @NUM2=CO1  FROM TB WHERE ID>=@ID AND CO1<>0 ORDER BY ID ASC
IF @ID2<>@ID1
RETURN @NUM1+(((@NUM2-@NUM1)/(@ID2-@ID1))*(@ID-@ID1))RETURN @NUM1
END
GO
SELECT ID,DBO.FUN_SO(ID),DBO.FUN_CO1(ID) FROM TB/*
1 0.10 0.10
2 0.15 0.20
3 0.20 0.27
4 0.17 0.33
5 0.13 0.40
6 0.10 0.50
*/