As you can see, there are travels made in the morning, and others made in the afternoon, What I need is to group those travels made one after another with a difference in time no longer than 30 minutes to consider them as one long travel from A to D. At the end there would be to long travels one made in the morning and other made in the adternoon, both from A to D.
Is important to note that if the individual travel that was done with a difference loger than 30 minutes can not be considered as part of a group of travels but as an individual one.
It could be done with interrecord-functions within an appropriate sorted resident-load, here a simplified example as starting point:
load *, if(TimeDiff <= 1/24/60*30, 'Group', 'individual') as Flag; load User, Time, if(User = previous(User), Time - previous(Time), null()) as TimeDiff resident X order by User, Time;
I see in this expression that you're using previous(), but, what if I want to evaluate not the previous record but the following. Since my first travel is From: A To: B, my start point is "A", and I need to know if the next travel which is From: B To: C is part of the same travel, and obviously the evaluation will continue to the following travels such as the third one wich is From: C To: D.
With sort-suffixes of asc (it's the default) and desc you could adjust the sort-order, like:
... resident X order by User asc, Time desc;
Depending on the real complexity of your requirement you may also add the From/To fields to the sort-order and/or combining some fields in beforehand (to simplify the sorting respectively sometimes are sorting-challenges only solvable with it).
Further possible and occasionally needed is to run such sorting multiple times forward and backwards with more (nested) if-loops to flag a multi-level ordering and on top of it the final grouping/accumulation is applied. If so are several simple steps after another easier to develop and to maintain as doing everything within a single load.
Timediff: load *, if(TimeDiff <= 1/24/60*30, 'Group', 'individual') as Flag; load Id_User, Time, if(Id_User = previous(Id_User), previous(Time) - Time, null()) as TimeDiff, If(Id_User = previous(Id_User), previous(Id_viaje), null()) as Next_travel resident Datos order by Id_User, Time desc;
And this is the output:
Id_viaje
Id_User
From
To
Time
Flag
TimeDiff
Next_travel
Comments
1
U123
A
B
11:14:00
Group
00:24:12
3
Ok
2
U123
A
B
17:36:05
Group
00:22:58
4
Ok
3
U123
B
C
11:38:12
Group
00:15:40
5
Ok
4
U123
B
C
17:59:03
Group
00:14:22
6
Ok
5
U123
C
D
11:53:52
individual
05:42:13
2
TimeDiff and Next_travel comes from Id_viaje = 2 but they are not related, answer should be null.
6
U123
C
D
18:13:25
individual
Ok
Please check my comments, also Flag column is not working well I think, but probably it won't be necessary at least by now.
Like hinted above you may need some more measurements and/or considering a higher complexity. The main-logic behind the use of interrecord-functions like previous/peek is quite simple:
query a current key against the previous one
accumulate a previous value with the current value respectively calculate any offset between them
(optional) deriving any flags or categories from the results
In many scenarios it's not really complicated even if there exists a suitable key and/or the sorting-field else if it needs to be created by n steps in beforehand in which multiple fields are (conditionally) combined to serve the purpose.
In your case it seems that you need to order the locations and the users before you compares the time-offsets. I don't know how complex this might be and if it's sensible to implement it within a single logic. Like above mentioned are n simple steps often easier as one complex one which means it might be more practically to include n parallel check-routines and the final categorizing is performant against all results.
More simple expressed: one logic may compares the offsets and another checks for any exceptions with a simple TRUE/FALSE flag and everything with FALSE is FALSE/NULL or whatever is suitable.
