本文介绍了如何计算平均值并更新它代替零?使用 SSIS的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
先谢谢你.我有一个 Mytable1
:
Thank you in advance.I have a Mytable1
:
Site_name | date& Time |PowerOutput
xyz001 |2013-07-21 01:00:00.000 |192
xzu001 |2013-07-21 02:00:00.000 |189
abf003 |2013-07-21 03:00:00.000 |0
ACT0001 |2013-07-21 04:00:00.000 |178
我想在零的情况下计算上一行和下一行的平均值.
I want to calculate average value of the previous row and the next row in case of ZERO.
我的输出表应该是:
Sitename |date&time |Power_output
xyz001 |2013-07-21 01:00:00.000 |192
xzu001 |2013-07-21 02:00:00.000 |189
abf003 |2013-07-21 03:00:00.000 |189
ACT0001 |2013-07-21 04:00:00.000 |178
逻辑是:
((上一个值-下一个值)/上一个值)*100<5,如果这是真的,那么它应该插入以前的值
((Previous value-next value)/previous value) *100 <5, If this is true then it should insert the previous value
((上一个值-下一个值)/上一个值)*100 >=5,如果这是真的,那么它应该保持为零.
((Previous value-next value)/previous value)*100 >=5,If this is true then it should remain as zero.
推荐答案
试试这个
BEGIN TRAN
DECLARE @Id INT ,@PreValue INT,@NextValue INT
CREATE TABLE #table(_Id INT IDENTITY(1,1) , Site_name VARCHAR(100),_dateTime DATETIME,PowerOutput INT,_upFlg TINYINT DEFAULT(0))
INSERT INTO #table( Site_name ,_dateTime ,PowerOutput)
SELECT 'xyz001' ,'2013-07-21 01:00:00.000' ,192 UNION ALL
SELECT 'xzu001' ,'2013-07-21 02:00:00.000' ,189 UNION ALL
SELECT 'abf003' ,'2013-07-21 03:00:00.000' ,0UNION ALL
SELECT 'ACT0001' ,'2013-07-21 04:00:00.000' ,178
WHILE EXISTS(SELECT 1 FROM #table WHERE PowerOutput = 0 AND _upFlg = 0)
BEGIN
SELECT Top 1 @Id = _Id FROM #table WHERE PowerOutput = 0 AND _upFlg = 0
SELECT @PreValue = PowerOutput FROM #table WHERE _Id = @Id - 1
SELECT @NextValue = PowerOutput FROM #table WHERE _Id = @Id + 1
IF ( (@PreValue-@NextValue)/@PreValue ) < 5
UPDATE #table SET PowerOutput = @PreValue WHERE _id = @id
UPDATE #table SET _upFlg = 1 WHERE _Id = @Id
END
SELECT * FROM #table
ROLLBACK TRAN
这篇关于如何计算平均值并更新它代替零?使用 SSIS的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!