Compute a Lagging Moving Average with Window Join

Question

Given the table:

trades:([] sym:`A;
           time:09:30:00.000 09:30:15.500 09:30:32.100 09:30:58.750 09:31:12.300 09:31:45.600 09:32:03.200 09:32:28.900 09:32:55.400 09:33:18.700;
           price:100.5 100.7 100.3 100.9 101.2 101.0 100.8 101.5 101.3 101.1)

Return the same table but with an additional column avgPrice, where avgPrice is the average of price taken over the last minute of price updates — a moving time window ending at each row's time.

Solution

w:-00:01 00:00 +\: trades`time
out:wj1[w;`sym`time;update p:price from trades;(trades;(avg;`price))]
`sym`time`avgPrice`price xcol out

sym time         avgPrice price
-------------------------------
A   09:30:00.000 100.5    100.5
A   09:30:15.500 100.6    100.7
A   09:30:32.100 100.5    100.3
A   09:30:58.750 100.6    100.9
A   09:31:12.300 100.775  101.2
A   09:31:45.600 101.0333 101
A   09:32:03.200 101      100.8
A   09:32:28.900 101.1    101.5
A   09:32:55.400 101.2    101.3
A   09:33:18.700 101.3    101.1

Explanation

A window join (wj/wj1) computes an aggregate over a window of rows ending at (or around) each row of a base table — exactly what's needed for a moving average, where each row's result depends on every price update in the minute leading up to it.

w:-00:01 00:00 +\: trades`time

builds the window boundaries: for every trade time, the window starts 1 minute before it (-00:01) and ends exactly at it (00:00). Each-left (+\:) adds both offsets to every time in trades, producing two parallel lists — window-start times and window-end times — one pair per row.

wj1[w;`sym`time;update p:price from trades;(trades;(avg;`price))]

then, for every row, finds the trades falling inside its window (matched on sym, ordered by time) and computes avg over their price. The window is closed at both ends, so the current row's own price is included. wj1 considers only the rows whose time falls inside the window; plain wj would also include the last row before the window start (the value prevailing when the window opened).

The aggregate is written into a column named after the column it was computed from — price — replacing the table's original price column in place. That is why the row's own price is copied to p beforehand. The columns of out are therefore `sym`time`price`p, where price now holds the moving average and p the original price. `sym`time`avgPrice`price xcol out renames them positionally: the third column (the average) becomes avgPrice, and the fourth (the original price) becomes price. Always check cols out before an xcol rename — getting the order backwards silently mislabels the columns without raising an error.