Traversing to previous rows and getting the data based on condition
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-25-2024 04:10 AM
Sample Input data set
| ClusterId | Event | EventTime |
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T11:38:30.168+00:00 |
| 1212-18-r9u1kzn1 | TERMINATING | 2024-02-02T13:43:33.933+00:00 |
| 1212-18-r9u1kzn1 | STARTING | 2024-02-02T15:50:05.174+00:00 |
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T15:54:21.510+00:00 |
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T16:09:20.576+00:00 |
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T16:19:58.744+00:00 |
| 1212-18-r9u1kzn1 | TERMINATING | 2024-02-02T17:18:33.863+00:00 |
| 1212-18-r9u1kzn1 | STARTING | 2024-02-02T17:22:38.635+00:00 |
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T17:23:40.781+00:00 |
| 1212-18-r9u1kzn1 | TERMINATING | 2024-02-02T18:03:33.953+00:00 |
| 1212-18-r9u1kzn1 | STARTING | 2024-02-02T21:10:21.651+00:00 |
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T21:13:59.842+00:00 |
| 1212-18-r9u1kzn1 | TERMINATING | 2024-02-02T22:43:34.022+00:00 |
Below is sample expected output. In this RunningEventTime will show the event time corresponding to the previous running event time for the event "TERMINATING". In case "STARTING" event is present then for that event time should be showing in "StartingEventTime" column.
| ClusterId | Event | EventTime | RunningEventTime | StartingEventTime |
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T11:38:30.168+00:00 | ||
| 1212-18-r9u1kzn1 | TERMINATING | 2024-02-02T13:43:33.933+00:00 | 2024-02-02T11:38:30.168+00:00 | |
| 1212-18-r9u1kzn1 | STARTING | 2024-02-02T15:50:05.174+00:00 | ||
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T15:54:21.510+00:00 | ||
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T16:09:20.576+00:00 | ||
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T16:19:58.744+00:00 | ||
| 1212-18-r9u1kzn1 | TERMINATING | 2024-02-02T17:18:33.863+00:00 | 2024-02-02T15:50:05.174+00:00 | |
| 1212-18-r9u1kzn1 | STARTING | 2024-02-02T17:22:38.635+00:00 | ||
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T17:23:40.781+00:00 | ||
| 1212-18-r9u1kzn1 | TERMINATING | 2024-02-02T18:03:33.953+00:00 | 2024-02-02T17:22:38.635+00:00 | |
| 1212-18-r9u1kzn1 | STARTING | 2024-02-02T21:10:21.651+00:00 | ||
| 1212-18-r9u1kzn1 | RUNNING | 2024-02-02T21:13:59.842+00:00 | ||
| 1212-18-r9u1kzn1 | TERMINATING | 2024-02-02T22:43:34.022+00:00 | 2024-02-02T21:10:21.651+00:00 |
I tried few option such using self join but that is not ideal when data set is large. Another option i tried is looping but here also same problem. It will not be good for large data sets. I tried windowing function "lag" but could not make it work. Any suggestion or hint would be really helpful.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-27-2024 10:18 PM
This event data does not have specific patter, I can not group it based on interval. Only option what is see is self join or looping. But i want to avoid it, is there any other option for given data set?
