sql中插值法完成缺失数据的填充
来源:互联网 发布:股票套利软件 编辑:程序博客网 时间:2024/06/06 03:05
从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中写一个存储过程,自动完成上述过程。
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。
---------
SQL 语句
方法通过排序的方式求得的@NUM1和@NUM2,但缺失数据多的时候,不再适用了啊。
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
BEGIN
DECLARE @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 DESC
SELECT 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
BEGIN
DECLARE @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 DESC
SELECT 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
*/
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中写一个存储过程,自动完成上述过程。
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。
---------
SQL 语句
方法通过排序的方式求得的@NUM1和@NUM2,但缺失数据多的时候,不再适用了啊。
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
BEGIN
DECLARE @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 DESC
SELECT 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
BEGIN
DECLARE @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 DESC
SELECT 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
*/
0 0
- sql中插值法完成缺失数据的填充
- pandas处理,填充缺失数据
- python实现数据预处理之填充缺失值
- 缺失version model的情况下能否完成Core Data的数据迁移?
- pandas:填充缺失值
- SQL 更新数据填充表
- 数据缺失的处理方法
- 数据缺失的中文字符串
- 【数据建模 缺失值处理】缺失值的处理
- 数据ETL面临的问题----数据缺失
- wps安装完成后提示系统缺失字体的解决方法
- 用R和BioConductor进行基因芯片数据分析(二):缺失值填充
- 【分析函数】使用分析函数LAST_VALUE或11g LAG实现缺失数据填充及其区别
- 三维软件开发笔记---数据填充及色标完成
- SQL用新数据填充表
- SQL中用新数据填充表
- 关于数据缺失问题的总结[转]
- java 查找数组中缺失的数据
- Material Design的前世今生(介绍篇)
- 遗传算法MATLAB工具包简介
- Hearbeat高可用
- 文章标题
- 怎么清除电脑的运行记录
- sql中插值法完成缺失数据的填充
- JavaWeb——02_CSS
- solr 下载地址
- 贝叶斯推理(Bayes Reasoning)、独立与因式分解
- ubuntu14.04+eigen3安裝
- 函数模板机制探究
- Bitmap与String的相互转化,已知图片URL展示图片
- 看到糗友发的,小时候她喜欢吃的妈妈都不喜欢吃,
- MySQL入门之事务概念