row_window_session()
row_window_session() calculates session start values of a column in a serialized row set.
Syntax
row_window_session ( Expr , MaxDistanceFromFirst , MaxDistanceBetweenNeighbors [, Restart] )
Expris an expression whose values are grouped together in sessions. Null values produce null values, and the next value starts a new session.Exprmust be a scalar expression of typedatetime.MaxDistanceFromFirstestablishes one criterion for starting a new session: The maximum distance between the current value ofExprand the value ofExprat the beginning of the session. It is a scalar constant of typetimespan.MaxDistanceBetweenNeighborsestablishes a second criterion for starting a new session: The maximum distance from one value ofExprto the next. It is a scalar constant of typetimespan.Restart is an optional scalar expression of type
boolean. If specified, every value that evaluates totruewill immediately restart the session.
Returns
The function returns the values at the beginning of each session.
Notes
The function has the following conceptual calculation model:
Go over the input sequence of values
Exprin order.For every value, determine if it establishes a new session.
If it establishes a new session, emit the value of
Expr. Otherwise, emit the previous value ofExpr.
The condition that determines if the value represents a new session is a logical OR one of the following conditions:
If there was no previous session value, or the previous session value was null.
If the value of
Exprequals or exceeds the previous session value plusMaxDistanceFromFirst.If the value of
Exprequals or exceeds the previous value ofExprplusMaxDistanceBetweenNeighbors.
Examples
The following example shows how to calculate the session start values for a table
with two columns: an ID column that identifies a sequence, and a Timestamp
column that gives the time at which each record occurred. In this example,
a session can't exceed 1 hour, and it continues as long as records are less than
5 minutes apart.
datatable (ID:string, Timestamp:datetime) [
// ...
]
| sort by ID asc, Timestamp asc
| extend SessionStarted = row_window_session(Timestamp, 1h, 5m, ID != prev(ID))