sequence_detect plugin
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer
Detects sequence occurrences based on provided predicates. The plugin is invoked with the evaluate operator.
Syntax
T | evaluate sequence_detect (TimelineColumn, MaxSequenceStepWindow, MaxSequenceSpan, Expr1, Expr2, ..., Dim1, Dim2, ...)
Learn more about syntax conventions.
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| T | string |
✔️ | The input tabular expression. |
| TimelineColumn | string |
✔️ | The column reference representing timeline, must be present in the source expression. |
| MaxSequenceStepWindow | timespan |
✔️ | The value of the max allowed timespan between 2 sequential steps in the sequence. |
| MaxSequenceSpan | timespan |
✔️ | The max timespan for the sequence to complete all steps. |
| Expr1, Expr2, ... | string |
✔️ | The boolean predicate expressions defining sequence steps. |
| Dim1, Dim2, ... | string |
✔️ | The dimension expressions that are used to correlate sequences. |
Returns
Returns a single table where each row in the table represents a single sequence occurrence:
- Dim1, Dim2, ...: dimension columns that were used to correlate sequences.
- Expr1TimelineColumn, Expr2TimelineColumn, ...: Columns with time values, representing the timeline of each sequence step.
- Duration: the overall sequence time window
Examples
The following query looks at the table T to search for relevant data from a specified time period.
T | evaluate sequence_detect(datetime_column, 10m, 1h, e1 = (Col1 == 'Val'), e2 = (Col2 == 'Val2'), Dim1, Dim2)
Exploring Storm Events
The following query looks on the table StormEvents (weather statistics for 2007) and shows cases where sequence of 'Excessive Heat' was followed by 'Wildfire' within 5 days.
StormEvents
| evaluate sequence_detect(
StartTime,
5d, // step max-time
5d, // sequence max-time
heat=(EventType == "Excessive Heat"),
wildfire=(EventType == 'Wildfire'),
State
)
Output
| State | heat_StartTime | wildfire_StartTime | Duration |
|---|---|---|---|
| CALIFORNIA | 2007-05-08 00:00:00.0000000 | 2007-05-08 16:02:00.0000000 | 16:02:00 |
| CALIFORNIA | 2007-05-08 00:00:00.0000000 | 2007-05-10 11:30:00.0000000 | 2.11:30:00 |
| CALIFORNIA | 2007-07-04 09:00:00.0000000 | 2007-07-05 23:01:00.0000000 | 1.14:01:00 |
| SOUTH DAKOTA | 2007-07-23 12:00:00.0000000 | 2007-07-27 09:00:00.0000000 | 3.21:00:00 |
| TEXAS | 2007-08-10 08:00:00.0000000 | 2007-08-11 13:56:00.0000000 | 1.05:56:00 |
| CALIFORNIA | 2007-08-31 08:00:00.0000000 | 2007-09-01 11:28:00.0000000 | 1.03:28:00 |
| CALIFORNIA | 2007-08-31 08:00:00.0000000 | 2007-09-02 13:30:00.0000000 | 2.05:30:00 |
| CALIFORNIA | 2007-09-02 12:00:00.0000000 | 2007-09-02 13:30:00.0000000 | 01:30:00 |