【指标统计】删除抖动遥信

1. 创建遥信抖动登记表

DROP TABLE "SMARTSYS"."scada_event_yx_shake";
CREATE TABLE "SMARTSYS"."scada_event_yx_shake"
(
"START_TIME" TIMESTAMP(0) NOT NULL,
"END_TIME" TIMESTAMP(0) NOT NULL,
"OBJ_ID" BIGINT NOT NULL,
"CNT" INTEGER default 0,
"ISDEL" INTEGER default 0) STORAGE(ON "SMART_HISTORY", CLUSTERBTR);

2. 查找抖动的遥信

declare
	pnt_id_var bigint;
	start_time_var TIMESTAMP;
	time_1_var TIMESTAMP;
	time_2_var TIMESTAMP;
	cnt_var integer;
	Pcur CURSOR;
begin
	--select min(occur_time) into start_time_var from scada_event_yx;
	select sysdate()-numtodsinterval(3,'day') into start_time_var from dual;
	OPEN Pcur FOR select distinct(obj_id) as pnt_id_var from scada_event_yx;
	LOOP
		FETCH Pcur INTO pnt_id_var;
		EXIT WHEN Pcur%NOTFOUND;
		time_1_var = start_time_var;
		WHILE TRUE LOOP
			time_2_var = time_1_var+numtodsinterval(1,'day');
			IF time_1_var > sysdate() THEN
				EXIT;
			END IF;
			select count(*) into cnt_var from scada_event_yx where obj_id = pnt_id_var and occur_time > time_1_var and occur_time < time_2_var;
			insert into scada_event_yx_shake(start_time,end_time,obj_id,cnt) values(time_1_var, time_2_var, pnt_id_var, cnt_var);
			time_1_var = time_2_var;
		END LOOP;
	END LOOP;
	CLOSE Pcur;
end;
commit;

3. 查询抖动遥信的结果

select * from scada_event_yx_shake where cnt >= 50 order by cnt desc;

4. 根据scada_event_yx_shake中的记录，从scada_event_yx表中删除相应的数据。

delete from scada_event_yx where occur_time >= ? and occur_time <= ? and obj_id = ?;