CREATE TABLE sensdatas( idserial serial NOT NULL, id INTEGER NOT NULL,--номер устройства temperature DOUBLE PRECISION, --температура pressure DOUBLE PRECISION, -- давление humidity DOUBLE PRECISION, -- влажность curenttime TIMESTAMP WITHOUT TIME zone NOT NULL,--дата время прихода показателей --**** CONSTRAINT sensdataskey PRIMARY KEY (idserial))
CREATE TABLE public.sensdatas ( idserial SERIAL, id INTEGER NOT NULL, type_param INTEGER NOT NULL, param_values DOUBLE PRECISION, curenttime TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL, CONSTRAINT sensdataskey PRIMARY KEY(idserial), CONSTRAINT sensdatas_fk FOREIGN KEY (type_param) REFERENCES public.type_param_dic(id) ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE)
CREATE TABLE public.type_param_dic ( id SERIAL, name VARCHAR(20) NOT NULL, CONSTRAINT type_param_dic_name_key UNIQUE(name), CONSTRAINT type_param_dic_pkey PRIMARY KEY(id))
"id" "name""1" "temperature""2" "pressure""3" "humidity"
CREATE OR REPLACE FUNCTION public.f_params ( date1 timestamptz, date2 timestamptz, interv VARCHAR, device INTEGER)RETURNS SETOF record AS$body$DECLAREparams record;date_int TIMESTAMP;BEGINdate_int=date1; WHILE date_int<=date2 LOOP--крутим цикл по интервалу с шагом "interv" FOR params IN --выводим среднее значение за "шаг" SELECT s.id,s.type_param,t.name,avg(s.param_values),date_int FROM sensdatas s,type_param_dic t WHERE s.id=device AND s.curenttime BETWEEN date_int AND date_int+ interv::INTERVAL AND s.type_param = t.id GROUP BY s.id,s.type_param,t.name,date_int LOOP RETURN NEXT params; END LOOP; date_int:=date_int+ interv::INTERVAL;--продвигаемся по интервалу шагом "interv" END LOOP; RETURN;END;$body$LANGUAGE 'plpgsql'VOLATILERETURNS NULL ON NULL INPUTSECURITY INVOKERCOST 100 ROWS 1000;
select * from f_params(to_timestamp('01.05.2015 10:00:00','dd.mm.yyyy HH24:MI:SS'),to_timestamp('28.05.2015 16:00:00','dd.mm.yyyy HH24:MI:SS'),'1 hour',5)as(id integer,type_param integer,name varchar,param_values double precision,date_int timestamp)
CREATE TABLE sensdatas( idserial serial NOT NULL, id INTEGER NOT NULL, temperature DOUBLE PRECISION, pressure DOUBLE PRECISION, humidity DOUBLE PRECISION, sernum INTEGER NOT NULL, curenttime TIMESTAMP WITHOUT TIME zone NOT NULL, et INTEGER, ep INTEGER, eh INTEGER, iroom CHARACTER VARYING(64), CONSTRAINT sensdataskey PRIMARY KEY (idserial));
INSERT INTO sensdatas( idserial, id, temperature, pressure, humidity, sernum, curenttime, et, ep, eh, iroom) VALUES (1, 3, 2, 3, 4, 123456789, '28.05.2015 16:00:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (2, 3, 3, 4, 5, 123456789, '28.05.2015 16:02:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (3, 3, 4, 5, 6, 123456789, '28.05.2015 16:04:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (4, 3, 5, 6, 7, 123456789, '28.05.2015 16:05:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (5, 3, 6, 7, 8, 123456789, '28.05.2015 17:10:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (6, 3, 7, 8, 9, 123456789, '28.05.2015 17:20:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (7, 3, 2, 3, 4, 123456789, '28.05.2015 17:30:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (8, 3, 3, 4, 5, 123456789, '28.05.2015 17:40:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (9, 3, 4, 5, 6, 123456789, '28.05.2015 18:05:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (10, 3, 5, 6, 7, 123456789, '28.05.2015 18:06:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (11, 3, 6, 7, 8, 123456789, '28.05.2015 18:07:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL), (12, 3, 7, 8, 9, 123456789, '28.05.2015 18:08:00'::TIMESTAMP WITH TIME zone, NULL, NULL, NULL, NULL);
CREATE OR REPLACE FUNCTION f_myfunc(date1 TIMESTAMP WITHOUT TIME ZONE, date2 TIMESTAMP WITHOUT TIME ZONE, interv CHARACTER VARYING, device INTEGER, myparam INTEGER) RETURNS SETOF RECORD AS$BODY$DECLARE params RECORD; date_int TIMESTAMP;BEGIN date_int=date1; --цикл по интервалу с шагом "interv" WHILE date_int <= date2 LOOP FOR params IN --выводим среднее значение за "шаг" SELECT s.id, s.sernum, CASE WHEN myparam=0 THEN AVG(s.temperature) WHEN myparam=1 THEN AVG(s.pressure) WHEN myparam=2 THEN AVG(s.humidity) ELSE 0 END, date_int FROM sensdatas s WHERE s.id=device AND s.curenttime BETWEEN date_int AND date_int + interv::INTERVAL GROUP BY s.id, s.sernum, date_int ORDER BY date_int LOOP RETURN NEXT params; END LOOP; --продвигаемся по интервалу шагом "interv" date_int:=date_int+ interv::INTERVAL; END LOOP; RETURN;END;$BODY$ LANGUAGE plpgsql;
SELECT * FROM f_myfunc('28.05.2015 16:00:00'::TIMESTAMP, '28.05.2015 19:00:00'::TIMESTAMP,'1 hour',3,0) AS (id INTEGER, sernum INTEGER, pokazateli DOUBLE PRECISION, date_int TIMESTAMP);
--*****SELECT s.id, s.sernum, CASE WHEN myparam=0 THEN round(AVG(s.temperature),2) WHEN myparam=1 THEN round(AVG(s.pressure),2) WHEN myparam=2 THEN round(AVG(s.humidity),2) ELSE 0--*****
--*** CASE WHEN myparam=0 THEN round(CAST(AVG(s.temperature) AS NUMERIC),2) WHEN myparam=1 THEN round(CAST(AVG(s.pressure) AS NUMERIC),2) WHEN myparam=2 THEN round(CAST(AVG(s.humidity) AS NUMERIC),2) ELSE 0--***