在PostgreSQL中基于测量的时间数据库平均间隔为10分钟 [英] Average aggregation on temporal DB based on measurment with 10-minute intervals in PostgreSQL

问题描述

我有一个时间数据库，我想通过使用以下时间序列中的数据通过时间序列数据的平均函数执行时间聚合：

  HH的小时平均值=（HH-1）：51，HH：01，HH：11，HH：21，HH：31，HH：41。 
  

此外，上述值是瞬时值。

样本数据：

  ambtemp ddate_ttime 
 1- 1.42 2007-09-28 23:39:09 
 2 -1.24 2007-09-28 23:41:09 
 3 -1.28 2007-09-28 23:43:09 
 4 -1.28 2007-09-28 23:45:09 
 5 -1.24 2007-09-28 23:47:09 
 6 -1.42 2007-09-28 23:49:09 
 7 -1.68 2007-09-28 23:51:09 
 8 -1.76 2007-09-28 23:53:09 
 9 -1.96 2007-09-28 23:55:09 
 10 -2.02 2007-09-28 23:57:09 
 11 -1.92 2007-09-28 23:59:09 
 12 -1.64 2007-09-29 00:01:09 
 13 -1.76 2007-09-29 00:03:09 
 14 -1.83 2007-09-29 00:05:09 
 15 -1.86 2007-09-29 00:07:09 
 16 -1.94 2007-09-29 00:09:09 
 17 -1.87 2007-09-29 00:11:09 
 18 -1.87 2007-09-29 00:13:09 
 19 -1.80 2007-09-29 00:15:09 
 20 -1.64 2007-09-29 00:17:09 
 21 -1.60 2007-09-29 00:19： 09 
 22 -1.90 2007-09-29 00:21:09 
 23 -2.08 2007-09-29 00:23:09 
 24 -1.94 2007-09-29 00:25 ：09 
 25 -2.12 2007-09 -29 00:27:09 
 26 -1.87 2007-09-29 00:29:09 
 27 -2.18 2007-09-29 00:31:09 
 28 -1.98 2007- 09-29 00:33:09 
 29 -1.73 2007-09-29 00:35:09 
 30 -1.84 2007-09-29 00:37:09 
 31 -2.04 2007 -09-29 00:39:09 
 32 -1.86 2007-09-29 00:41:09 
 33 -1.94 2007-09-29 00:43:09 
 34 -1.77 2007-09-29 00:45:09 
  

预期结果：

  0:00的小时平均值=（23:51，0:01，0:11，0:21，0:31，0的平均值：41）
  

每小时0:00 =（-1.68 + -1.64 + -1.87 + -1.90 + -2.18 + -1.86）/ 6

解决方案

  select dt，average_temp 
 from（
 select 
 date_trunc（'hour'，ddate_ttime + interval '10 minutes'）dt，
 avg（ambtemp）over（
按date_trunc（'hour'， ddate_ttime +间隔'10分钟'）
）average_temp 
 from sample 
其中extract（minuted from ddate_ttime）in （51，01，11，21，31，41）
）s 
分别由dt 
  订单$ p> 
 
 
  SQL提琴 
 
I have a temporal database and I want to perform the temporal aggregation via average function on time series data by using data in time sequence of following:
Hourly average of HH= (HH-1):51, HH:01, HH:11, HH:21, HH:31, HH:41.
Moreover, aforementioned values are instantaneous values. 

Sample data:
    ambtemp      ddate_ttime
1     -1.42 2007-09-28 23:39:09
2     -1.24 2007-09-28 23:41:09
3     -1.28 2007-09-28 23:43:09
4     -1.28 2007-09-28 23:45:09
5     -1.24 2007-09-28 23:47:09
6     -1.42 2007-09-28 23:49:09
7     -1.68 2007-09-28 23:51:09
8     -1.76 2007-09-28 23:53:09
9     -1.96 2007-09-28 23:55:09
10    -2.02 2007-09-28 23:57:09
11    -1.92 2007-09-28 23:59:09
12    -1.64 2007-09-29 00:01:09
13    -1.76 2007-09-29 00:03:09
14    -1.83 2007-09-29 00:05:09
15    -1.86 2007-09-29 00:07:09
16    -1.94 2007-09-29 00:09:09
17    -1.87 2007-09-29 00:11:09
18    -1.87 2007-09-29 00:13:09
19    -1.80 2007-09-29 00:15:09
20    -1.64 2007-09-29 00:17:09
21    -1.60 2007-09-29 00:19:09
22    -1.90 2007-09-29 00:21:09
23    -2.08 2007-09-29 00:23:09
24    -1.94 2007-09-29 00:25:09
25    -2.12 2007-09-29 00:27:09
26    -1.87 2007-09-29 00:29:09
27    -2.18 2007-09-29 00:31:09
28    -1.98 2007-09-29 00:33:09
29    -1.73 2007-09-29 00:35:09
30    -1.84 2007-09-29 00:37:09
31    -2.04 2007-09-29 00:39:09
32    -1.86 2007-09-29 00:41:09
33    -1.94 2007-09-29 00:43:09
34    -1.77 2007-09-29 00:45:09
And Expected results:
Hourly mean for 0:00 = average of values of (23:51, 0:01, 0:11, 0:21, 0:31, 0:41)
Hourly mean 0:00 = (-1.68+-1.64+-1.87+-1.90+-2.18+-1.86)/6
解决方案
select dt, average_temp
from (
    select
        date_trunc('hour', ddate_ttime + interval '10 minutes') dt,
        avg(ambtemp) over(
            partition by date_trunc('hour', ddate_ttime + interval '10 minutes')
        ) average_temp
    from sample
    where extract(minute from ddate_ttime) in (51, 01, 11, 21, 31, 41)
) s
group by 1, 2
order by dt
SQL Fiddle

                        
这篇关于在PostgreSQL中基于测量的时间数据库平均间隔为10分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！