row_window_session()
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
Calculates session start values of a column in a serialized row set.
row_window_session
(
Expr ,
MaxDistanceFromFirst ,
MaxDistanceBetweenNeighbors [,
Restart] )
Name | Type | Required | Description |
---|---|---|---|
Expr | datetime |
✔️ | An expression whose values are grouped together in sessions. When Expr results in a null value, the next value starts a new session. |
MaxDistanceFromFirst | timespan |
✔️ | Determines when a new session starts using the maximum distance between the current Expr value and its value at the beginning of the session. |
MaxDistanceBetweenNeighbors | timespan |
✔️ | Another criterion for starting a new session using the maximum distance from one value of Expr to the next. |
Restart | boolean |
If specified, every value that evaluates to true immediately restarts the session. |
Learn more about syntax conventions.
The function returns the values at the beginning of each session. It uses the following conceptual calculation model:
Iterates over the input sequence of Expr values in order.
For each value, it decides whether to create a new session.
If a new session is created, the function returns the current value of Expr. Otherwise, it returns the previous value of Expr.
Note
The value represents a new session if it meets the following conditions using the logical OR operation:
- If there was no previous session value, or the previous session value was null.
- If the value of Expr equals or exceeds the previous session value plus MaxDistanceFromFirst.
- If the value of Expr equals or exceeds the previous value of Expr plus MaxDistanceBetweenNeighbors.
- If
Restart
condition is specified and evaluates totrue
.
The following example calculates session start values for a table, datatable
, with a sequence ID column and a Timestamp column to record the time of each record. The data is sorted by the sequence IDs and timestamps and then the example returns values for ID, Timestamp, and a new SessionStarted column. A session can't exceed one hour. It continues for as long as records are less than five minutes apart and the ID stays the same. The example includes records that are less than five minutes apart.
datatable (ID:string, Timestamp:datetime) [
"1", datetime(2024-04-11 10:00:00),
"2", datetime(2024-04-11 10:18:00),
"1", datetime(2024-04-11 11:00:00),
"3", datetime(2024-04-11 11:30:00),
"2", datetime(2024-04-11 13:30:00),
"2", datetime(2024-04-11 10:16:00)
]
| sort by ID asc, Timestamp asc
| extend SessionStarted = row_window_session(Timestamp, 1h, 5m, ID != prev(ID))
Output
ID | Timestamp | SessionStarted |
---|---|---|
1 | 2024-04-11T10:00:00Z | 2024-04-11T10:00:00Z |
1 | 2024-04-11T11:00:00Z | 2024-04-11T11:00:00Z |
2 | 2024-04-11T10:16:00Z | 2024-04-11T10:16:00Z |
2 | 2024-04-11T10:18:00Z | 2024-04-11T10:16:00Z |
2 | 2024-04-11T13:30:00Z | 2024-04-11T13:30:00Z |
3 | 2024-04-11T11:30:00Z | 2024-04-11T11:30:00Z |