How to create a window of arbitrary size in Kusto?

Question

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?

Answer 1

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

Fiddle