数据库根据时间后一条数据减去前一条数据的一种简单处理方式

因为数据计算错误，导致数据库中连续时间保存的数据没有正确的计算后一个时间的数据前去前一个时间的数据，因此需要重新处理，但是由于数据量比较大，因此直接使用sql语句处理速度慢且sql语句更麻烦，因此，采用一种先用简单sql语句计算后一时间减去前一时间并保存到中间表，然后利用存储过程和游标将中间表计算好的数据循环update到原来的表中。

 

结构如上图，要将后一时间减去前一时间存入RATIO_TOTAL_VALUE列中，在有多变量多时间的情况下，需要2个游标才能处理完成，存储过程的sql语句比较麻烦，所以利用首先计算差值存入中间表，然后存储过程只用一个游标循环根据ID处理每一行即可。

1、利用排序后的rownum计算差值存入中间表：

create table ems_collect2 as 
select t2.id,t2.mn,t2.paramno,t2.datatime,to_char(t2.datatime) datatime2 ,(case when t1.mn=t2.mn and t1.paramno=t2.paramno then to_char(t2.ratiovalue-t1.ratiovalue,'fm999999999999990.0999999999999') else '0' end)as RATIO_TOTAL_VALUE  from
 ( select a.*,rownum as a_rownum from (select a.* from ems_collectdata a,ems_item b where a.mn=b.mn and a.paramno=b.item_id and b.numerictypes='2' and a.datatime>'20161231205000' and a.datatime<'20170321000000' order by a.paramno,a.datatime ) a) t1,
 ( select a.*,rownum as a_rownum from (select a.* from ems_collectdata a,ems_item b where a.mn=b.mn and a.paramno=b.item_id and b.numerictypes='2' and a.datatime>'20161231205000' and a.datatime<'20170321000000' order by a.paramno,a.datatime ) a) t2
 where t1.a_rownum=t2.a_rownum-1 order by t1.a_rownum;

2、利用存储过程循环处理中间表每一行，根据ID重新update到原表中：

 

CREATE OR REPLACE PROCEDURE sp_update_collect IS
  CURSOR C_EMP IS --声明显式游标
    SELECT * FROM ems_collect2  order by DATATIME ;
  C_ROW       C_EMP%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录
  curDate VARCHAR2(14);
BEGIN
  --Fetch 循环
  select '20160101000000' into curDate from dual;
  OPEN C_EMP; --必须要明确的打开和关闭游标
  LOOP
    FETCH C_EMP INTO C_ROW;
    EXIT WHEN C_EMP%NOTFOUND;
    if curDate<>C_ROW.DATATIME2 then 
      insert into EMS_HANDLETIME values(C_ROW.DATATIME2);
      commit;
      curDate := C_ROW.DATATIME2;
    end if;    
    /*curDate := C_ROW.DATATIME;*/
    update ems_collectdata  set ratio_total_value = C_ROW.RATIO_TOTAL_VALUE
    where id=C_ROW.ID;
  END LOOP;
  CLOSE C_EMP;
  commit;
END sp_update_collect;

3、执行存储过程

begin
  -- Call the procedure
  sp_update_collect;
end;