如何计算平均值并更新它代替零?使用 SSIS [英] How to calculate the average value and update it in place of zeros? using 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！