查找指定时间范围内的错误遥信
select occur_time, milli_second, alarm_type, result, content from scada_event_yx where occur_time > '2019-07-12 00:00:00' and occur_time < '2019-07-19 00:00:00' and alarm_type = 1 and result = 1;
选择其中一些,根据occur_time,alarm_type,result编写SQL语句进行删除操作。
以下是已经废弃的旧版本
1. 创建待删除记录特征表
CREATE TABLE "SMARTSYS"."scada_event_yx_mark"
(
"OCCUR_TIME" TIMESTAMP(0) NOT NULL,
"OBJ_ID" BIGINT NOT NULL,
"STATUS" INTEGER NOT NULL,
"ALARM_TYPE" INTEGER NOT NULL,
"CONTENT" VARCHAR(512),
"ISDEL" INTEGER default 0) STORAGE(ON "SMART_HISTORY", CLUSTERBTR);
2. 将指定时间范围内的错误遥信找出来,将其特征记录到特征表中
declare
occur_time_var TIMESTAMP;
soe_time_var timestamp;
obj_id_var bigint;
status_var integer;
content_var VARCHAR;
cnt integer;
Pcur CURSOR;
begin
OPEN Pcur FOR select distinct e.occur_time,e.obj_id,e.status,e.content from scada_event_yx e
where rtu_type in (3,4,7,8) and alarm_type = 1 and occur_time between '2019-05-01' and '2019-05-02';
LOOP
FETCH Pcur INTO occur_time_var,obj_id_var,status_var,content_var;
EXIT WHEN Pcur%NOTFOUND;
execute immediate 'select count(*) from scada_event_yx where alarm_type = 3 and soe_time <=? and soe_time >= ? and obj_id = ? and status = ?;' into cnt using occur_time_var,occur_time_var-numtodsinterval(15,'second'),obj_id_var,status_var;
if (cnt < 1) then
--execute immediate 'delete from scada_event_yx where alarm_type = 1 and occur_time =? and obj_id = ? and status = ?;' into cnt using occur_time_var,obj_id_var,status_var;
execute immediate 'insert into scada_event_yx_mark(alarm_type,occur_time,obj_id,status,content) values(1,?,?,?,?);' using occur_time_var,obj_id_var,status_var,content_var;
end if;
END LOOP;
CLOSE Pcur;
end;
commit;
3. 从特征表中手动标记确认要删除的记录:isdel=1
4. 从原始表中彻底删除错误遥信记录
delete from scada_event_yx where exists (select * from scada_event_yx_mark where isdel = 1 and scada_event_yx.occur_time=scada_event_yx_mark.occur_time and scada_event_yx.alarm_type=scada_event_yx_mark.alarm_type and scada_event_yx.status=scada_event_yx_mark.status);
commit;
5. 删除特征表
drop table scada_event_yx_mark;