Using prev() function I can access previous rows individually.
mytable
| sort by Time asc
| extend mx = max_of(prev(Value, 1), prev(Value, 2), prev(Value, 3))
How to define a window to aggregate over in more generic way? Say I need maximum of 100 values in previous rows. How to write a query that does not require repeating prev() 100 times?
Can be achieved by combining scan and series_stats_dynamic().
scan is used to create an array of last x values, per record.
series_stats_dynamic() is used to get the max value of each array.
// Data sample generation. Not part of the solution
let mytable = materialize(range i from 1 to 15 step 1 | extend Time = ago(1d*rand()), Value = toint(rand(100)));
// Solution starts here
let window_size = 3; // >1
mytable
| order by Time asc
| scan declare (last_x_vals:dynamic)
with
(
step s1 : true => last_x_vals = array_concat(array_slice(s1.last_x_vals, -window_size + 1, -1), pack_array(Value));
)
| extend toint(series_stats_dynamic(last_x_vals).max)
| i | Time | Value | last_x_vals | max |
|---|---|---|---|---|
| 5 | 2022-06-10T11:25:49.9321294Z | 45 | [45] | 45 |
| 14 | 2022-06-10T11:54:13.3729674Z | 82 | [45,82] | 82 |
| 2 | 2022-06-10T13:25:40.9832745Z | 44 | [45,82,44] | 82 |
| 1 | 2022-06-10T17:38:28.3230397Z | 24 | [82,44,24] | 82 |
| 7 | 2022-06-10T18:29:33.926463Z | 17 | [44,24,17] | 44 |
| 15 | 2022-06-10T19:54:33.8253844Z | 9 | [24,17,9] | 24 |
| 3 | 2022-06-10T20:17:46.1347592Z | 43 | [17,9,43] | 43 |
| 12 | 2022-06-11T00:02:55.5315197Z | 94 | [9,43,94] | 94 |
| 9 | 2022-06-11T00:11:18.5924511Z | 61 | [43,94,61] | 94 |
| 11 | 2022-06-11T00:39:40.6858444Z | 38 | [94,61,38] | 94 |
| 4 | 2022-06-11T03:54:59.418534Z | 84 | [61,38,84] | 84 |
| 10 | 2022-06-11T05:55:38.2904242Z | 6 | [38,84,6] | 84 |
| 6 | 2022-06-11T07:25:43.3977923Z | 36 | [84,6,36] | 84 |
| 13 | 2022-06-11T09:36:08.7904844Z | 28 | [6,36,28] | 36 |
| 8 | 2022-06-11T09:51:45.2225391Z | 73 | [36,28,73] | 73 |