rolling_percentile() plugin
Returns an estimate for the specified percentile of the ValueColumn population in a rolling (sliding) BinsPerWindow size window per BinSize.
The plugin is invoked with the evaluate operator.
Syntax
T | evaluate rolling_percentile(ValueColumn, Percentile, IndexColumn, BinSize, BinsPerWindow [, dim1, dim2, ...] )
Arguments
- T: The input tabular expression.
- ValueColumn: The name of the column with values to calculate the percentile of.
- Percentile: Scalar with the percentile to calculate.
- IndexColumn: The name of the column to run the rolling window over.
- BinSize: Scalar with size of the bins to apply over the IndexColumn.
- BinsPerWindow: Scalar with number of bins included in each window.
- dim1, dim2, ... : (optional) list of the dimensions columns to slice by.
Returns
Returns a table with a row per each bin (and combination of dimensions if specified) that has the rolling percentile of values in the window ending at the bin (inclusive). Output table schema is:
| IndexColumn | dim1 | ... | dim_n | rolling_BinsPerWindow_percentile_ValueColumn_Pct |
|---|
Examples
Rolling 3-day median value per day
The next query calculates a 3-day median value in daily granularity. Each row in the output represents the median value for the last 3 bins (days), including the bin itself.
let T =
range idx from 0 to 24*10-1 step 1
| project Timestamp = datetime(2018-01-01) + 1h*idx, val=idx+1
| extend EvenOrOdd = iff(val % 2 == 0, "Even", "Odd");
T
| evaluate rolling_percentile(val, 50, Timestamp, 1d, 3)
| Timestamp | rolling_3_percentile_val_50 |
|---|---|
| 2018-01-01 00:00:00.0000000 | 12 |
| 2018-01-02 00:00:00.0000000 | 24 |
| 2018-01-03 00:00:00.0000000 | 36 |
| 2018-01-04 00:00:00.0000000 | 60 |
| 2018-01-05 00:00:00.0000000 | 84 |
| 2018-01-06 00:00:00.0000000 | 108 |
| 2018-01-07 00:00:00.0000000 | 132 |
| 2018-01-08 00:00:00.0000000 | 156 |
| 2018-01-09 00:00:00.0000000 | 180 |
| 2018-01-10 00:00:00.0000000 | 204 |
Rolling 3-day median value per day by dimension
Same example from above, but now also calculates the rolling window partitioned for each value of the dimension.
let T =
range idx from 0 to 24*10-1 step 1
| project Timestamp = datetime(2018-01-01) + 1h*idx, val=idx+1
| extend EvenOrOdd = iff(val % 2 == 0, "Even", "Odd");
T
| evaluate rolling_percentile(val, 50, Timestamp, 1d, 3, EvenOrOdd)
| Timestamp | EvenOrOdd | rolling_3_percentile_val_50 |
|---|---|---|
| 2018-01-01 00:00:00.0000000 | Even | 12 |
| 2018-01-02 00:00:00.0000000 | Even | 24 |
| 2018-01-03 00:00:00.0000000 | Even | 36 |
| 2018-01-04 00:00:00.0000000 | Even | 60 |
| 2018-01-05 00:00:00.0000000 | Even | 84 |
| 2018-01-06 00:00:00.0000000 | Even | 108 |
| 2018-01-07 00:00:00.0000000 | Even | 132 |
| 2018-01-08 00:00:00.0000000 | Even | 156 |
| 2018-01-09 00:00:00.0000000 | Even | 180 |
| 2018-01-10 00:00:00.0000000 | Even | 204 |
| 2018-01-01 00:00:00.0000000 | Odd | 11 |
| 2018-01-02 00:00:00.0000000 | Odd | 23 |
| 2018-01-03 00:00:00.0000000 | Odd | 35 |
| 2018-01-04 00:00:00.0000000 | Odd | 59 |
| 2018-01-05 00:00:00.0000000 | Odd | 83 |
| 2018-01-06 00:00:00.0000000 | Odd | 107 |
| 2018-01-07 00:00:00.0000000 | Odd | 131 |
| 2018-01-08 00:00:00.0000000 | Odd | 155 |
| 2018-01-09 00:00:00.0000000 | Odd | 179 |
| 2018-01-10 00:00:00.0000000 | Odd | 203 |