How do I get all entries 5 minutes after a certain condition in SQL?-CodePudding

I am trying to solve the following problem using SQL:

I have a table (example shown below) with action items per user, the timestamp when the action happened and a unique identifier for each entry. I want to find out what actions each user takes in the 5 minutes after a specific action occurs. For example, I want to see for all users with the action item "sit" what happens in the 5 minutes after that, so to see all entries starting with the "sit" action item. I hope someone can help!! Thank you!

table example

I started using ROW_NUMBER and then partition by users and order by time, but after that I dont know how to continue.

CodePudding user response：

Your question is not entirely clear, however, in my understanding, it is easier to use a JOIN

create table log(UserName varchar(20),ActionTime datetime,ActionItem varchar(10),ActionId varchar(26));
insert into log values
 ('Anna'    ,cast('2022-07-30 13:17:22' as datetime),'walk'   ,'uid_1')
,('Peter'   ,cast('2022-07-30 15:39:46' as datetime),'drive'  ,'uid_2')
,('Sarah'   ,cast('2022-07-30 09:07:53' as datetime),'stand'  ,'uid_3')  
,('Kurt'    ,cast('2022-07-30 00:56:14' as datetime),'sit'    ,'uid_4')  
,('Deborah' ,cast('2022-07-30 15:26:02' as datetime),'lie'    ,'uid_5')  
,('Michelle',cast('2022-07-30 15:26:03' as datetime),'scratch','uid_6')  
,('Sven'    ,cast('2022-07-30 15:26:04' as datetime),'run'    ,'uid_7')  
,('Sarah'   ,cast('2022-07-30 15:28:06' as datetime),'swim'   ,'uid_8')  
,('Peter'   ,cast('2022-07-30 13:17:22' as datetime),'look'   ,'uid_9')  
;
select a.ActionId,a.UserName,a.ActionItem,a.ActionTime
      ,b.ActionTime,b.UserName,b.ActionItem,b.ActionId
from log a left join log b 
  on  b.ActionId<>a.ActionId
  and b.ActionTime>=a.ActionTime
  and datediff(mi,a.ActionTime,b.ActionTime)<5

CodePudding user response：

I guess this problem can not be solved with a single query. But you can use a series of queries.

In answer to your question I will use MySQL dialect of SQL. I believe it doesn't matter.

On first step let's assume that we are only interested in the last action "sit". In this case we can do such query:

SELECT * FROM user_actions WHERE ACTION_ITEM = "sit" ORDER BY TIMESTAMP DESC LIMIT 1;

So the result is

 ------ --------------------- ------------- ------------------- 
| USER | TIMESTAMP           | ACTION_ITEM | UNIQUE_IDENTIFIER |
 ------ --------------------- ------------- ------------------- 
| Kurt | 2022-07-30 00:56:14 | sit         |                 4 |
 ------ --------------------- ------------- -------------------

Then save timestamp value in variable:

SELECT TIMESTAMP INTO @reason_ts FROM user_actions WHERE ACTION_ITEM = "sit" ORDER BY TIMESTAMP DESC LIMIT 1;

And now we need to get further actions in next 5 minutes (actually I took 12 hours because 5 minutes is not enough for your example). Let's do this:

SELECT csq.* FROM user_actions AS csq WHERE TIMESTAMP BETWEEN @reason_ts AND ADDTIME(@reason_ts, '12:00:00');

The result is:

 ------- --------------------- ------------- ------------------- 
| USER  | TIMESTAMP           | ACTION_ITEM | UNIQUE_IDENTIFIER |
 ------- --------------------- ------------- ------------------- 
| Sarah | 2022-07-30 09:07:53 | stand       |                 3 |
| Kurt  | 2022-07-30 00:56:14 | sit         |                 4 |
 ------- --------------------- ------------- -------------------

If you need all further action modify query:

SELECT csq.* FROM user_actions AS csq WHERE TIMESTAMP >= @reason_ts;

If you need not only last action "sit" it will be more difficult. I think you need to write some kind of script or sql function. But still it is doable.