postgresql 臭氧8小时聚合函数

1、定义数据拼接函数

CREATE OR REPLACE FUNCTION "public"."sfun"("results" _numeric, "val" numeric)

  RETURNS "pg_catalog"."_numeric" AS $BODY$

BEGIN

    results :=array_append(results,val::numeric);

RETURN results;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION "public"."sfun"("results" _numeric, "val" numeric) OWNER TO "postgres";

2、定义数据处理函数

CREATE OR REPLACE FUNCTION "public"."sffun_o3_8h"("results" _numeric)

  RETURNS "pg_catalog"."numeric" AS $BODY$

DECLARE

    tmp integer;

    len integer;

    ret numeric[];--结果

BEGIN

   len=array_length(results , 1);

     if  len<8 then return -999; end if;

    for i in 8..len loop

        tmp=0;

        for j in 0..7 loop

            tmp=tmp+results[i-j];

        end loop;

        tmp=tmp/8::numeric;

        ret:=array_append(ret,tmp::numeric);

    end loop;

    ret=array_sort(ret,'asc');

    RETURN ret[array_length(ret,1)];

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION "public"."sffun_o3_8h"("results" _numeric) OWNER TO "postgres";

3、定义臭氧8小时触发器

CREATE AGGREGATE agg_o3_8h(

BASETYPE = numeric,

SFUNC = sfun,

STYPE = numeric[],

FINALFUNC = sffun_o3_8h

);

postgresql 臭氧8小时聚合函数的相关教程结束。