In our project we have to periodically calculate aggregates and further calculations based on the input data received.
One frequent requirement is to calculate time differences between certain rows in our input data stream.
For example, this is my input datastream:
Timestamp Event Value
2017-05-21 11:33 e1 17
2017-05-21 11:37 e2 18
2017-05-21 11:38 e3 18
2017-05-21 11:39 e1 19
2017-05-21 11:42 e2 19
I now want to calculate all the timespans between e2 events and the last received e1 event (ordered by timestamp).
i would expect the result to be: 3 (minutes) 4 (minutes)
A similar requirement would be to calculate the timespans between the same type of event (i.e. all differences between e1 events) where i would expect this result: 6 (minutes)
My attempts so far:
This sort of analytics could pretty easily be achieved using the LAG function in conjunction with the WHEN clause but unfortunately the WHEN clause is missing in U-SQL. If it would be T-SQL it would also be possible to solve this using Sub-Selects in the SELECT-Clause of the statement but unfortunately this is also not possible in U-SQL.
Do you have any suggestions or sample scripts on how to solve this issue? Thank you very much for your help!