ems数据修复脚本

select DATE_FORMAT(collecttime,'%Y-%m-%d %H:%i:00') sumas15minutes,SUM(usevalue) from t_data_report_basic GROUP by sumas15minutes;
select DATE_FORMAT(collecttime,'%Y-%m-%d %H:00:00') sumasH24hours,SUM(usevalue) from t_data_report_basic GROUP by sumasH24hours;
select DATE_FORMAT(collecttime,'%Y-%m-%d 00:00:00') sumasdays,SUM(usevalue) from t_data_report_basic GROUP by sumasdays;
select DATE_FORMAT(collecttime,'%Y-%m-01 00:00:00') sumasdays,SUM(usevalue) from t_data_report_basic GROUP by sumasdays;


-- 计算比例
(SELECT ee.e/ff.f FROM
(select aa.a-bb.b e from(select nowValue a from t_data_report_repair where collectTime = '2017-09-10 00:00:00' )aa,
(select nowValue b from t_data_report_repair where collectTime = '2017-09-01 00:00:00')bb) ee,
(select cc.c-dd.d f from (select nowValue c from t_data_report_basic where collectTime = '2017-08-10 00:00:00') cc,
(select nowValue d from t_data_report_basic where collectTime = '2017-08-01 00:00:00')dd) ff);

-- 获取计算结果：
0.754509681；

-- 基于比例赋值
insert into t_data_report_repair 
select buildingid,gatewayid,meterid,pointid,collecttime,nowvalue,usevalue * 0.754509681  from t_data_report_basic


/* 清理过渡数据 */
delete from t_data_report_repair where collectTime <> ('2017-08-01 00:00:00' and '2017-08-10 00:00:00');

/* 生成统计数据 */
insert into t_data_report_repair 
select buildingid,gatewayid,meterid,pointid,DATE_ADD(collecttime,INTERVAL 31 DAY) collecttime,nowvalue,usevalue * 
(SELECT ee.e/ff.f FROM
(select aa.a-bb.b e from(select nowValue a from t_data_report_repair where collectTime = '2017-09-10 00:00:00' )aa,
(select nowValue b from t_data_report_repair where collectTime = '2017-09-01 00:00:00')bb) ee,