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. 將指定時間范圍內的失敗遙控找出來，將其特征記錄到特征表中

declareoccur_time_var TIMESTAMP;soe_time_var timestamp;obj_id_var bigint;status_var integer;content_var VARCHAR;cnt integer;Pcur CURSOR; beginOPEN 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';LOOPFETCH 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; 《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的【指标统计】删除失败遥控的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。