如何从PostgreSQL下的一系列时间段中找到列的和？

Question

我正在写一个带宽监视器来运行我的网络，我已经到了一个阶段，我有一个充满包元数据的数据库(主机、出站/出站、大小等等)，我需要绘制它。我的方法是取2分钟内的数据包大小之和，再用120除以该时段的平均比特率，然后在24小时内每2分钟重复一次。首先，我用720个单独的查询实现了这一点，如

SELECT SUM(size) 
FROM traffic_log_outbound 
WHERE ip=INET '192.168.1.105' 
  AND time BETWEEN TIMESTAMP 'now' - INTERVAL '1402 minute' 
               AND TIMESTAMP 'now' - INTERVAL '1400 minute'

但发现这是非常低效的，大约需要400秒才能完成。

来自traffic_log_outbound的样本。还有另一个具有相同结构的表，traffic_log_inbound。

bandwidth_monitor=# SELECT * FROM traffic_log_outbound LIMIT 5;
            time            |      ip       | proto | src_port | dst_port | size
----------------------------+---------------+-------+----------+----------+------
 2014-03-01 19:51:26.851858 | 192.168.1.225 |     6 |    49365 |       80 |   40
 2014-03-01 19:51:26.851907 | 192.168.1.225 |     6 |    49365 |       80 |  377
 2014-03-01 19:51:26.851919 | 192.168.1.225 |     6 |    49343 |       80 |   40
 2014-03-01 19:51:26.853556 | 192.168.1.225 |     6 |    55492 |      443 |   40
 2014-03-01 19:51:26.855605 | 192.168.1.225 |     6 |    55492 |      443 |   40
(5 rows)

和结构

bandwidth_monitor=# \d+ traffic_log_outbound
                            Table "public.traffic_log_outbound"
  Column  |            Type             | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+---------+--------------+-------------
 time     | timestamp without time zone |           | plain   |              |
 ip       | inet                        |           | main    |              |
 proto    | integer                     |           | plain   |              |
 src_port | integer                     |           | plain   |              |
 dst_port | integer                     |           | plain   |              |
 size     | integer                     |           | plain   |              |
Check constraints:
    "traffic_log_outbound_ip_check" CHECK (ip << '192.168.1.0/24'::inet)
Has OIDs: no

Answer 1

SQL Fiddle

select
    (extract(epoch from time) / 120)::integer,
    sum(size) as size
from test
where
    ip=inet '192.168.1.69'
    and
    time > current_timestamp - interval '24 hours'
group by 1
order by 1
;
   int4   | size 
----------+------
 11613921 |  100
 11614273 |  400
 11614276 |  200
 11614278 |  400