Pipelinedb: How to group stream data by each N minutes in continuous view How to group data from pipelinedb's stream by each N minutes in continuous view select?

Pipelinedb's stream gets data about the events that comes from a many remote hosts. I need to group this events by type, ip and time intervals in 5 minutes, for example, and count them.

So on input I have (very roughly):

CREATE STREAM event (ip varchar(15), type varchar(32));
CREATE CONTINUOUS VIEW AS SELECT ??? from event group by ??? etc...;
INSET INTO event VALUES
  ('111.111.111.111', 'page_open'),   -- 22:35
  ('111.111.111.111', 'page_open'),   -- 22:36
  ('111.111.111.111', 'page_close '), -- 22:37
  ('111.111.111.111', 'page_close'),  -- 22:42
  ('222.111.111.111', 'page_open '),  -- 22:42
  ('222.111.111.111', 'page_open'),   -- 22:43
  ('222.111.111.111', 'page_close'),  -- 22:44
  ('111.111.111.111', 'page_open')    -- 22:44
); 

arrival_timestamp | ip               | type      
------------------------------------------------
22:35             | 111.111.111.111  | page_open -- new interaval, ends in 22:40
22:36             | 111.111.111.111  | page_open
22:37             | 111.111.111.111  | page_close 
22:42             | 111.111.111.111  | page_close -- event comes in next interval, ends in 22:45
22:42             | 222.111.111.111  | page_open 
22:43             | 222.111.111.111  | page_open
22:44             | 222.111.111.111  | page_close 
22:44             | 111.111.111.111  | page_open


And what must be in continuous view select:

time  | ip               | type       | count
---------------------------------------------
22:40 | 111.111.111.111  | page_open  | 2
22:40 | 111.111.111.111  | page_close | 1
22:45 | 111.111.111.111  | page_open  | 1
22:45 | 111.111.111.111  | page_close | 1
22:45 | 222.111.111.111  | page_open  | 2
22:45 | 222.111.111.111  | page_close | 1


p.s.
Sorry for my english
Maybe this can helps you.

I've divided the clock in groups of 5 minutes:

group of 5 min = extract('minute' from time)::int / 5

min 0 to 4 = 0
min 5 to 9 = 1
....


This adds an extra column to your current table.

select *, extract('minute' from time)::int / 5 as g_of_5m 
from foo 
order by ip, time;

+----+----------+-----------------+------------+---------+
|    |   time   |        ip       |    type    | g_of_5m |
+----+----------+-----------------+------------+---------+
| 1  | 22:35:00 | 111.111.111.111 |  page_open |    7    |
+----+----------+-----------------+------------+---------+
| 2  | 22:36:00 | 111.111.111.111 |  page_open |    7    |
+----+----------+-----------------+------------+---------+
| 3  | 22:37:00 | 111.111.111.111 | page_close |    7    |
+----+----------+-----------------+------------+---------+
| 4  | 22:42:00 | 111.111.111.111 | page_close |    8    |
+----+----------+-----------------+------------+---------+
| 5  | 22:44:00 | 111.111.111.111 |  page_open |    8    |
+----+----------+-----------------+------------+---------+
| 6  | 22:42:00 | 222.111.111.111 |  page_open |    8    |
+----+----------+-----------------+------------+---------+
| 7  | 22:43:00 | 222.111.111.111 |  page_open |    8    |
+----+----------+-----------------+------------+---------+
| 8  | 22:44:00 | 222.111.111.111 | page_close |    8    |
+----+----------+-----------------+------------+---------+


As Evan Carroll has pointed in his comment, it should be grouped by hour,g_of_5m, just to take care of this case: (same g_of_5m, more than hour)

22:40  111.111.111.111  page_open
23:41  111.111.111.111  page_open


And this is the query:

select min(time) as init_time, ip, type, count(*) as count
from foo
group by ip, type, extract('hour' from time), extract('minute' from time)::int / 5
order by ip, min(time);

+----+-----------+-----------------+------------+-------+
|    | init_time |        ip       |    type    | count |
+----+-----------+-----------------+------------+-------+
| 1  |  22:35:00 | 111.111.111.111 |  page_open |   2   |
+----+-----------+-----------------+------------+-------+
| 2  |  22:37:00 | 111.111.111.111 | page_close |   1   |
+----+-----------+-----------------+------------+-------+
| 3  |  22:42:00 | 111.111.111.111 | page_close |   1   |
+----+-----------+-----------------+------------+-------+
| 4  |  22:44:00 | 111.111.111.111 |  page_open |   1   |
+----+-----------+-----------------+------------+-------+
| 5  |  22:42:00 | 222.111.111.111 |  page_open |   2   |
+----+-----------+-----------------+------------+-------+
| 6  |  22:44:00 | 222.111.111.111 | page_close |   1   |
+----+-----------+-----------------+------------+-------+


Just if you want to check it: http://rextester.com/CVWBM15291