我目前正在使用这个:
select avg(tank_level)
from (
select row_number() over (order by id) as rn, tank_level
from data_tanksensor
where sensors_on_site_id = 91
) s
group by (rn + ((Select count(*)/10 From data_tanksensor where sensors_on_site_id = 91)-1))/ (Select count(*)/10 From data_tanksensor where sensors_on_site_id = 91)
;
从表格中获取10个平均值。这个表格也有时间戳,我想获得每个10个平均tank_level的平均时间戳。这将用于创建历史图表。如果有人能帮我修改这个查询以获得平均时间戳,那将不胜感激。提前致谢。
表格如下:
. id sensors_on_site_id tank_level timestamps [PK] bigint integer double precision time without time zone ........... .................. ................ ...................... 12345 91 7.5 2017-03-24 11:16:31.143362 12346 91 7.6 2017-03-24 11:21:31.148639 12347 91 5.4 2017-03-24 11:26:31.155739 12348 91 3.6 2017-03-24 11:31:31.156478 12349 91 8.5 2017-03-24 11:36:31.157303 12350 91 4.2 2017-03-24 11:41:31.172008
例如,如果我只想从这个表格中获取平均值,则我的原始查询将为:
select avg(tank_level) from ( select row_number() over (order by id) as rn, tank_level from data_tanksensor where sensors_on_site_id = 91 ) s group by (rn + ((Select count(*)/2 From data_tanksensor where sensors_on_site_id = 91)-1))/ (Select count(*)/2 From data_tanksensor where sensors_on_site_id = 91) ;
当然,查询缺少将平均时间戳的部分,这就是我试图解决的问题。但我想要的预期结果将是
平均值 时间戳 double precision timestamp without time zone ................ ........................... 6.833333 2017-03-24 11:21:31... 5.433333 2017-03-24 11:36:31...
再次说明,这只是样例数据,被平均的行数达到了上百行。谢谢。
to_timestamp(avg(extract(epoch from timestamp_col)))
翻译为中文: 取时间戳列的平均值并将其转换为时间格式。 - pozs