postgresql - In SQL how to select previous rows based on the current row values? -

i've simple sql table looks this-

create table msg (     from_person character varying(10),     from_location character varying(10),     to_person character varying(10),     to_location character varying(10),     msglength integer,     ts timestamp without time zone ); 

i want find out each row in table if different 'from_person' , 'from_location' has interacted 'to_person' in current row in last 3 minutes.

for example, in above table, row # 4, other mary mumbai (current row), nancy nyc , bob barcelona has sent message charlie in last 3 minutes count 2.

similarly, row#2, other bob barcelona (current row), nancy nyc has sent message charlie in ca (current row) count 1

example desired output-

0 1 0 2 

i tried using window function seems in frame clause can specify rows count before , after can't specify time itself.

as known, every table in postgres has primary key. or should have @ least. great if had primary key defining expected order of rows.

example data:

create table msg (     id int primary key,     from_person text,     to_person text,     ts timestamp without time zone );  insert msg values (1, 'nancy',   'charlie', '2016-02-01 01:00:00'), (2, 'bob',     'charlie', '2016-02-01 01:00:00'), (3, 'charlie', 'nancy',   '2016-02-01 01:00:01'), (4, 'mary',    'charlie', '2016-02-01 01:02:00'); 

the query:

select m1.id, count(m2) msg m1 left join msg m2 on m2.id < m1.id , m2.to_person = m1.to_person , m2.ts >= m1.ts- '3m'::interval group 1 order 1;   id | count  ----+-------   1 |     0   2 |     1   3 |     0   4 |     2 (4 rows) 

in lack of primary key can use function row_number(), example:

with msg_with_rn (     select *, row_number() on (order ts, from_person desc) rn     msg     ) select m1.id, count(m2) msg_with_rn m1 left join msg_with_rn m2 on m2.rn < m1.rn , m2.to_person = m1.to_person , m2.ts >= m1.ts- '3m'::interval group 1 order 1; 

note have used row_number() on (order ts, from_person desc) sequence of rows have presented in question. of course, should decide how resolve ambiguities arising same values of column ts (as in first 2 rows).