如何在Postgresql中计算具有相同时间戳的值的平均值

Question

我有下表：

平均值

	日期	值
2021-04-07 18:00:00		5
2021-04-07 18:00:00		10
2021-04-07 18:02:00	5
2021-04-07 18:02:00		4
2021-04-07 18:03:00	5
2021-04-07 18:03:00		8

在内部选择中，用group by date获得avg。则CCD_ 3——其date等于内部的记录——选择date。

SELECT t1.date,t2.value,t1.average 
FROM
(SELECT date,AVG(Cast(value as Float)) as average
FROM yourTable
GROUP BY date) t1 JOIN yourTable t2 ON t1.date = t2.date

结果：dbfiddle

一个简单的查询：

SELECT date, AVG(value) as avg_value FROM MyTable GROUP BY date

SELECT CAST(date AS DATE) as DateField, AVG(value) as avg_value
FROM MyTable
GROUP BY CAST(date AS DATE)

使用窗口函数：

select t.*,
avg(value) over (partition by date) as timestamp_average
from t;