【指标统计】删除失败遥控

1. 创建待删除记录特征表

CREATE TABLE "SMARTSYS"."scada_event_yk_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_yk e 
		where rtu_type in (3,4,7,8) and alarm_type = 4 and status = 16 and occur_time between '2019-04-28' 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_yk where alarm_type = 4 and status = 17 and occur_time <=? and occur_time >= ? and obj_id = ?;' into cnt using occur_time_var+numtodsinterval(30,'second'),occur_time_var,obj_id_var;
		if (cnt < 1) then
			--execute immediate 'delete from scada_event_yk 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_yk_mark(alarm_type,occur_time,obj_id,status,content) values(4,?,?,?,?);' 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_yk where exists (select * from scada_event_yk_mark where isdel = 1 and scada_event_yk.occur_time=scada_event_yk_mark.occur_time and scada_event_yk.alarm_type=scada_event_yk_mark.alarm_type and scada_event_yk.status=scada_event_yk_mark.status);
commit;

5. 删除特征表

drop table scada_event_yk_mark;