C++Builder
  Начало   Форум   Помощь Войти Регистрация  
Страниц: [1]   Вниз
  Печать  
Автор Тема: Математическая формула на сервере БД  (Прочитано 3728 раз)
SilverFish
Участник
**

Сказали спасибо: +14/-6
Offline Offline

Сообщений: 789


« : 28 мая 2015, 10:34:33 »

В базе данных есть таблица, куда устройства присылают свои показатели.
Код
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)
)
Одно устройство присылает данные каждые 20 секунд, другое каждое 60 секунд и т.д.

Допустим, пользователь хочет посмотреть данные по температуре за сутки от устройства, которое шлет данные каждые 20 секунд. Получается, что на клиент надо передать большое кол-во данных, а если пользователь захочет посмотреть за месяц, то кол-во данных вообще огромное.
Хотелось бы, например, иметь на клиенте возможность передать на сервер параметр, например, 1 час. Тогда, если требуются данные за сутки, то сервер должен вернуть 24 показателя. Где каждый показатель это среднее значение, которое рассчиталось на сервере по каждому часу.
Как такое реализуется? Надо придумать хранимую процедуру, которая принимает параметр обобщения, делает расчет и возвращает данные клиенту? Как должна выглядеть такая процедура для PostgreSQL или надо по-другому все организовывать, чтобы получать на клиенте усредненные данные чтобы правильно уменьшить их кол-во?
Спасибо.
Записан
gen
Участник
**

Сказали спасибо: +28/-0
Offline Offline

Сообщений: 52


« Ответ #1 : 28 мая 2015, 16:58:22 »

Я бы, наверное, нормализировал структуру. Т.е. вынес набор параметров в справочник, а в sensdatas оставил только код параметра и соответствующие значения:
Код
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"
  Таким образом, в последующем, при необходимости, можно неограниченно увеличивать количество параметров без переделки БД и клиента.
По поводу выборки средних значений: можно попробовать через процедуру, в которую передать начало и конец интересующего периода, шаг выборки (например 1 час) и, если нужно отдельно, идентификатор устройства:
Код
CREATE OR REPLACE FUNCTION public.f_params (
 date1 timestamptz,
 date2 timestamptz,
 interv VARCHAR,
 device INTEGER
)
RETURNS SETOF record AS
$body$
DECLARE
params record;
date_int TIMESTAMP;
BEGIN
date_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'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 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)
где '1 hour' - шаг выборки (тип interval может быть любым, соглассно синтакису Postgre, т.е.  '1.5 hour'  '0.5 hour' '1 day' ...),    5 - номер id устройства. Если нужно выводить все устройства, то надо убрать в запросе "s.id=device AND "
Записан
SilverFish
Участник
**

Сказали спасибо: +14/-6
Offline Offline

Сообщений: 789


« Ответ #2 : 26 июня 2015, 14:05:47 »

Cпасибо.
Без справочника такой вот вариант:

Код
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);
 

Результат
3   123456789   3.5   2015-05-28 16:00:00
3   123456789   4.5   2015-05-28 17:00:00
3   123456789   5.5   2015-05-28 18:00:00

Можно ли как-то модифицировать так, чтобы время было также среднее за период? Это даст возможность строить более точный график - имеем средний показатель, например, температуры и будем иметь средние время для этого показателя. Точка на графике будет более точная.
« Последнее редактирование: 26 июня 2015, 14:53:41 от SilverFish » Записан
SilverFish
Участник
**

Сказали спасибо: +14/-6
Offline Offline

Сообщений: 789


« Ответ #3 : 26 июня 2015, 15:05:27 »

И показатели как округлить до сотых в PostgreSQL 8.3?...
Функция round(v numeric, s int) не подходит для этой версии...

Код
--*****
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
--*****
 

ERROR:  function round(double precision, integer) does not exist
LINE 1:  SELECT s.id, s.sernum, CASE WHEN  $1 =0 THEN round(AVG(s.te...

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:   SELECT s.id, s.sernum, CASE WHEN  $1 =0 THEN round((AVG(s.temperature)),2) WHEN  $1 =1 THEN round((AVG(s.pressure)),2) WHEN  $1 =2 THEN round((AVG(s.humidity)),2) ELSE 0 END,  $2  FROM sensdatas s WHERE s.id= $3  AND s.curenttime BETWEEN  $2  AND  $2  +  $4 ::INTERVAL GROUP BY s.id, s.sernum,  $2  ORDER BY  $2
CONTEXT:  PL/pgSQL function "f_myfunc" line 9 at FOR over SELECT rows


« Последнее редактирование: 26 июня 2015, 15:34:08 от SilverFish » Записан
SilverFish
Участник
**

Сказали спасибо: +14/-6
Offline Offline

Сообщений: 789


« Ответ #4 : 26 июня 2015, 15:29:17 »

Ничего не понимаю... Непонимающий
Тут написано, что эта функция есть в 8.3: http://www.postgresql.org/docs/8.3/static/functions-math.html
Почему же ошибка?
Записан
SilverFish
Участник
**

Сказали спасибо: +14/-6
Offline Offline

Сообщений: 789


« Ответ #5 : 26 июня 2015, 15:55:12 »

Вроде так получилось:
1. Изменить вызов:
SELECT * FROM f_myfunc('2015-06-23 10:03:57'::TIMESTAMP, '2015-06-23 11:24:16'::TIMESTAMP,'0.5 hour',3,0) AS
  (id INTEGER, sernum INTEGER, pokazateli numeric, date_int TIMESTAMP);

2.
Код
--***
       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
--***
 

Остался вопрос как усереднить время?
Записан
Страниц: [1]   Вверх
  Печать  
 
Перейти в: