sql - 从 PostgreSql 中的多个选择中获取数据(使用 DBeaver)

Question

我有一个这样的表:

10/10/2018 00:00 | 5
10/10/2018 00:10 | 7
10/10/2018 00:20 | 9
...
10/10/2018 23:40 | 5
10/10/2018 23:50 | 6

我需要以小时为单位获取平均数，并返回所有小时的平均值(从 00:00 到 23:00)

我可以像这样得到两个小时的平均值:

(select avg(value)
from public.table
where time_stamp between TO_TIMESTAMP('2018-11-05 20:00:00', 'YYYY-MM-DD HH24:MI:SS') 
and
TO_TIMESTAMP('2018-11-05 20:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 hour')
UNION ALL
(select avg(value)
from public.table
where time_stamp between TO_TIMESTAMP('2018-11-05 21:00:00', 'YYYY-MM-DD HH24:MI:SS') 
and
TO_TIMESTAMP('2018-11-05 21:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 hour')

但是我怎样才能在某个循环中得到所有 24 个平均值呢？我不想将此 SQL 复制 24 次。

Answer 1

试一试

select 
   date_trunc('hour', time_stamp), avg(value) 
from 
   public.table 
where 
   date_trunc('day', time_stamp) = date_trunc('day', TO_TIMESTAMP('2018-11-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) 
group by 
   date_trunc('hour', time_stamp);

这个想法是让所有的时间戳只具体到小时。然后选择当天出现的所有记录，按小时分组。对每个小时分组内的值进行平均。