How to calculate the number of messages within 10 seconds before the previous one?-CodePudding

I have a table with messages and I need to find chats where were two or more messages in period of 10 seconds. table

id message_id time
1  1          2021.11.10 13:09:00
1  2          2021.11.10 13:09:01
1  3          2021.11.10 13:09:50
2  1          2021.11.10 15:18:00           
2  2          2021.11.10 15:20:00
3  1          2021.11.12 15:00:00
3  2          2021.11.12 15:10:00
3  2          2021.11.12 15:10:10

So the result looks like

id
1
3

I can't come up with the idea how to group by a period or maybe it can be done other way?

select id
from t
group by id, ?
having count(message_id) > 1

CodePudding user response：

You can join the table with itself, matching them on the chat id and your timeframe.

create table messages (chat_id integer,message_id integer,"time" timestamp);
insert into messages values
(1,1,'2021.11.10 13:09:00'),
(1,2,'2021.11.10 13:09:01'),
(1,3,'2021.11.10 13:09:50'),
(2,1,'2021.11.10 15:18:00'),
(2,2,'2021.11.10 15:20:00'),
(3,1,'2021.11.12 15:00:00'),
(3,2,'2021.11.12 15:10:00'),
(3,2,'2021.11.12 15:10:10');

select  target_chat,
        target_message,
        count(*) "number of messages preceding by no more than 10 seconds"
from
    (select  t1.chat_id target_chat,
            t1.message_id target_message,
            t1.time,
            t2.chat_id,
            t2.message_id,
            t2.time
    from messages t1 
        inner join messages t2 
            on  t1.chat_id=t2.chat_id 
            and t1.message_id<>t2.message_id
            and (t2.time<=t1.time-'10 seconds'::interval and t2.time<=t1.time)) a
group by 1,2;
-- target_chat | target_message | number of messages preceding by no more than 10 seconds
--------------- ---------------- ---------------------------------------------------------
--           1 |              3 |                                                       2
--           2 |              2 |                                                       1
--           3 |              2 |                                                       2
--(3 rows)

From that you can select the records with your desired number of preceding messages.

CodePudding user response：

select id from test_table t where 
       t.time   interval '10 second' >= 
       (select time from test_table where id=t.id and time>t.time limit 1) 
       group by id;

results

id
----
  1
  3