【指标统计】统计装置总招遥信

–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),
"GRAND_ID" BIGINT,
"ISDEL" INTEGER default 0) STORAGE(ON "SMART_HISTORY", CLUSTERBTR);

–2. 创建查找容器的函数

CREATE OR REPLACE FUNCTION get_dev_container(OBJ_ID IN BIGINT, CONT_TAB IN VARCHAR)
RETURN BIGINT
AS
DECLARE
	id_var bigint;
	cont_id_var bigint;
	cont_tab_var varchar(64);
	sql_str VARCHAR(1024);
BEGIN
	IF (OBJ_ID IS NULL or OBJ_ID < 2 or cont_tab IS NULL or length(cont_tab)=0) THEN
		RETURN NULL;
	ELSE
		sql_str := 'select id, container_id, container_tablename from '||cont_tab||' where id = '||OBJ_ID;
		execute immediate sql_str into id_var, cont_id_var, cont_tab_var;
		IF (cont_tab = 'obj_dev') THEN
			RETURN cont_id_var;
		END IF;
		return get_dev_container(cont_id_var, cont_tab_var);
	END IF;
END;

–3. 更新容器

update scada_event_yx_mark set grand_id = get_dev_container(obj_id, 'pnt_dig');

–4. 连续几条变位来自同一个设备的容器（馈线或者环网柜）

declare
	occur_time_var TIMESTAMP;
	obj_id_var bigint;
	status_var integer;
	grand_id_var bigint;
	
	old_occur_time_var TIMESTAMP;
	old_obj_id_var bigint;
	old_status_var integer;
	old_grand_id_var bigint;
	
	cnt integer;
	Pcur CURSOR;
begin
	old_occur_time_var := '1900-01-01';
	old_obj_id_var := -1;
	old_status_var := -1;
	old_grand_id_var := -1;
	
	update scada_event_yx_mark set isdel = 0;
	
	OPEN Pcur FOR select occur_time,obj_id,status,grand_id from scada_event_yx_mark order by occur_time;
	LOOP
		FETCH Pcur INTO occur_time_var,obj_id_var,status_var,grand_id_var;
		EXIT WHEN Pcur%NOTFOUND;
		
		if old_grand_id_var = grand_id_var THEN
			update scada_event_yx_mark set isdel = 1 where occur_time = occur_time_var and obj_id = obj_id_var and status = status_var and grand_id = grand_id_var;
			update scada_event_yx_mark set isdel = 1 where occur_time = old_occur_time_var and obj_id = old_obj_id_var and status = old_status_var and grand_id = old_grand_id_var;
		end if;
		
		old_occur_time_var := occur_time_var;
		old_obj_id_var := obj_id_var;
		old_status_var := status_var;
		old_grand_id_var := grand_id_var;
	END LOOP;
	CLOSE Pcur;
	
	update scada_event_yx_mark set isdel = 1 where grand_id is null;
end;