2

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

  • I am not sure what type of solution you want. Clearly the question is on topic, but this is the first pipelinedb question on Pg, so I'm not sure you'll find the expertise to answer this. You want a solution that works in PostgreSQL? Or do you want to know how to do this the best way with pipelinedb features? – Evan Carroll Dec 21 '16 at 18:39
  • @EvanCarroll Pipelinedb is based on postresql server, so solution will be based on postgres syntax and use its features. Also I think solution lay in large part on postgresql, except for certain features pipelinedb, and experts in postgresql can help me, maybe. – Zaklinatel Dec 22 '16 at 7:26
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.