如何获取时间戳的平均值？PostgreSQL

Question

我目前正在使用这个:

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...

再次说明，这只是样例数据，被平均的行数达到了上百行。谢谢。

- Derek

---

请编辑您的问题并提供示例数据和所需结果。 - Gordon Linoff

将 to_timestamp(avg(extract(epoch from timestamp_col))) 翻译为中文： 取时间戳列的平均值并将其转换为时间格式。 - pozs

Answer 1

你可以将时间戳转换为 Unix 时间戳，对这些值求和，再将结果除以数量，最后再将结果转换回时间戳。下面是一个快速可行的示例：

with
    __ts as(
        select unnest(array[
            '2015-11-22 09:31:00', '2015-11-22 09:32:00', '2015-11-23 11:31:00', '2015-11-23 11:32:00',
            '2015-11-23 11:34:00', '2015-11-23 15:28:00', '2015-11-23 15:29:00', '2015-11-24 10:49:00',
            '2015-11-24 10:50:00', '2015-11-24 11:18:00'
        ]::timestamp without time zone[]) as ts
    )
select
    to_timestamp(sum(extract(epoch from ts)) / (select count(1) from __ts))
from
    __ts

- Scoots

---

Unix时间戳（又称“双精度”）可以直接平均（avg()），不需要手动计算sum() / count()（特别是因为count()可能返回零，所以等效的方法会更繁琐——sum() / nullif(count(), 0)）。 - pozs

@pozs 是的，我那样做真的很愚蠢。你发现得好。 - Scoots

Answer 2

选择to_timestamp(avg(timestamps)) "时间戳", avg(tank_level) "油罐水平" from ( select row_number() over (order by id) as rn, tank_level, extract(epoch from timestamps) "timestamps" 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) order by timestamps asc ; 弄清楚了 感谢你们的示例

- Derek