Skip to content

Latest commit

 

History

History
84 lines (63 loc) · 4.34 KB

row-window-session-function.md

File metadata and controls

84 lines (63 loc) · 4.34 KB
Raw
title description ms.reviewer ms.topic ms.date
row_window_session()
Learn how to use the row_window_session() function to calculate session start values of a column in a serialized row set.
alexans
reference
08/11/2024

row_window_session()

[!INCLUDE applies] [!INCLUDE fabric] [!INCLUDE azure-data-explorer] [!INCLUDE monitor] [!INCLUDE sentinel]

Calculates session start values of a column in a serialized row set.

Syntax

row_window_session ( Expr , MaxDistanceFromFirst , MaxDistanceBetweenNeighbors [, Restart] )

Parameters

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.

[!INCLUDE syntax-conventions-note]

Returns

The function returns the values at the beginning of each session. It uses the following conceptual calculation model:

  1. Iterates over the input sequence of Expr values in order.

  2. For each value, it decides whether to create a new session.

  3. 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 to true.

Examples

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.

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

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

Related content