Postgres平均时间数据
问题描述:
我们使用Postgres来记录来自我们流程的数据。我们的数据库的一个例子如下:Postgres平均时间数据
CREATE TABLE Data
("time_stamp" timestamp, "pressure" numeric, "temperature" numeric, "flow" numeric);
INSERT INTO Data
("time_stamp", "pressure", "temperature", "flow")
VALUES
('2016-05-12 20:42:24', 97.68, 9.02564, 2.24664),
('2016-05-12 20:42:25', 97.68, 9.02564, 2.24054),
('2016-05-12 20:42:26', 122.1, 9.01832, 2.24054),
('2016-05-12 20:42:27', 122.1, 9.01099, 2.23443),
('2016-05-12 20:42:28', 97.68, 9.01099, 2.23443),
('2016-05-12 20:42:29', 122.1, 9.01099, 2.24054),
('2016-05-12 20:42:30', 97.68, 9.01099, 2.23443),
('2016-05-12 20:42:31', 122.1, 9.01099, 2.23443),
('2016-05-12 20:42:32', 122.1, 9.01832, 2.24054),
('2016-05-12 20:42:33', 122.1, 9.01832, 2.23443);
我所试图做的就是Postgres的(使用pgAdminIII)来生成一个任意时间片的平均数据,比如5秒。它会输出一个平均的时间,压力,温度和流量列这五秒钟。该数据将有两个条目,时间条目为'2016-05-12 20:42:26'和'2016-05-12 20:42:31'。
答
下面是一个解决方案,这不是最好的,但有点作品。
SELECT timestamp without time zone '1970-01-01' + cast(avg(extract(epoch from time_stamp))::text as interval),
sub.press,
sub.temp,
sub.flow
FROM data d join (
SELECT
(extract(seconds from time_stamp)/5)::integer as num,
avg(pressure) as press,
avg(temperature) as temp,
avg(flow) as flow
FROM
data
group by 1) sub on sub.num=(extract(second from d.time_stamp)/5)::integer
group by sub.press,sub.temp,sub.flow
order by 1
子选择从数据表中选择数据并将其分组5秒。时间戳需要外部选择才能获得“平均”。我认为这对功能来说应该更容易。
编辑: 这是我为此写的功能。假设每秒只有一行。
您需要首先创建一个自定义类型:
CREATE TYPE t_data AS
(
time_stamp timestamp,
pressure numeric,
temp numeric,
flow numeric);
这是函数:
CREATE OR REPLACE FUNCTION dataCheck (timeInterval integer) RETURNS setof t_data AS $BODY$
DECLARE
pressure numeric[];
temp numeric[];
flow numeric[];
rec record;
i integer default 1;
ret t_data;
BEGIN
for rec in select * from data order by time_stamp
loop
pressure[i]=rec.pressure;
temp[i]=rec.temp;
flow[i]=rec.flow;
if i=(timeInterval/2 + case when timeInterval%2 <> 0 then 1 else 0 end) then
ret.time_stamp=rec.time_stamp;
end if;
if i=timeInterval then
i=0;
ret.pressure=avg((select avg(a) from unnest(pressure) as a));
ret.temp=avg((select avg(a) from unnest(temp) as a));
ret.flow=avg((select avg(a) from unnest(flow) as a));
return next ret;
end if;
i=i+1;
end loop;
return ;
end;$BODY$ LANGUAGE plpgsql;
这就是你如何执行它:
SELECT * from dataCheck(5);
的功能每“5”行返回平均数据。
你会推荐什么功能?我试过了这个代码,但它不能很好地处理我的整个数据集。 – cshoopman
我编辑了我的答案与功能。 – perzsa
由于某种原因昨天,我的数据输出并不是每秒都是,它在整个一天的过程中跳过了25秒。当每秒没有条目时,这段代码如何处理这种情况? 我还使用了函数'select * from dataCheck(300)WHERE TIME_STAMP :: DATE ='YESTERDAY';'将其应用于昨天的数据文件。第一次的时间戳平均为00:04:20,我预计它是00:02:30左右。 – cshoopman