sql - Postgres - 聚合分钟到小时

Question

我需要一些有关 PostgresSQL 的帮助。我试图将一些记录(5、10、15、20 等)分组为 60 分钟的间隔。

我需要的是对给定小时内的分钟值进行 GROUP BY 和 AVG 到相应的小时。

SELECT id, value, 
extract(year from GDDP.timestamp) as YEAR, 
extract(month from GDDP.timestamp) as MONTH,
extract(day from GDDP.timestamp) as DAY, 
extract(hour from GDDP.timestamp) as "HOUR",
extract(minute from GDDP.timestamp) as MINUTE,

FROM GDDP

WHERE value > 0 AND 
GDDP.timestamp  BETWEEN '2016-07-01 00:00:00' and '2016-12-31 23:55:00'

ORDER BY YEAR, MONTH, DAY, HOUR

目前，这是上面查询的结果:

id | value | YEAR | MONTH | DAY | HOUR | MINUTE
-------------------------------------------------
1  | 100   | 2016 | 07    | 01  | 1    | 05
2  | 200   | 2016 | 07    | 01  | 1    | 10
3  | 100   | 2016 | 07    | 01  | 1    | 15
4  | 300   | 2016 | 07    | 01  | 1    | 20
5  | 200   | 2016 | 07    | 01  | 1    | 25
6  | 500   | 2016 | 07    | 01  | 1    | 30

但是，我希望结果看起来像这样:

id | value  | YEAR | MONTH | DAY | HOUR
---------------------------------------
1  | 233.3  | 2016 | 07    | 01  | 1

在此先感谢您的帮助!

Answer 1

使用聚合函数avg()按年月日小时分组

SELECT 
    min(id) as id,
    avg(value) as value, 
    extract(year from gddp.timestamp) as year, 
    extract(month from gddp.timestamp) as month,
    extract(day from gddp.timestamp) as day, 
    extract(hour from gddp.timestamp) as hour
FROM gddp
WHERE value > 0 
AND gddp.timestamp  BETWEEN '2016-07-01 01:00:00' AND '2016-12-31 01:23:00'
GROUP BY year, month, day, hour
ORDER BY year, month, day, hour;

 id |        value         | year | month | day | hour 
----+----------------------+------+-------+-----+------
  1 | 233.3333333333333333 | 2016 |     7 |   1 |    1
(1 row)