I have a simple table and want to fill null prices with last average of not null prices:
t:([] time: .z.p+til 10)
t:update price: rand 50.0 from t where i=0
t:update price: rand 50.0 from t where i=1
t:update price: rand 50.0 from t where i=2
t:update price: rand 50.0 from t where i=4
time price
--------------------------------------
2025.01.13D07:54:49.068012805 20.00
2025.01.13D07:54:49.068012806 25.00
2025.01.13D07:54:49.068012807 30.00
2025.01.13D07:54:49.068012808
2025.01.13D07:54:49.068012809 35.00
2025.01.13D07:54:49.068012810 40.00
2025.01.13D07:54:49.068012811
2025.01.13D07:54:49.068012812
2025.01.13D07:54:49.068012813 30.00
2025.01.13D07:54:49.068012814
so the output would be:
time price
--------------------------------------
2025.01.13D07:54:49.068012805 20.00
2025.01.13D07:54:49.068012806 25.00
2025.01.13D07:54:49.068012807 30.00
2025.01.13D07:54:49.068012808 25.00 / --> (20+25+30)/3
2025.01.13D07:54:49.068012809 35.00
2025.01.13D07:54:49.068012810 40.00
2025.01.13D07:54:49.068012811 37.50 / --> (35+40)/2
2025.01.13D07:54:49.068012812 37.50 / --> (35+40)/2
2025.01.13D07:54:49.068012813 30.00
2025.01.13D07:54:49.068012814 30.00 / --> (30)/1
Ultimately you need an avgs and sums that "resets" on a null/zero so you could create custom ones (would not be as fast as the built-ins of course).
q)avgs
k){(+\x)%+\~^x}
q)sums
+\
q)mysums:{(y<>0)*x+y}\
q)myavgs:{mysums[x]%mysums[0<>x]}
q)t:([]time:.z.p+til 10;price:20. 25. 30. 0n 35. 40. 0n 0n 30. 0n)
q)update fills[myavgs 0.^price]^price from t
time price
-----------------------------------
2025.01.13D15:56:41.230985000 20
2025.01.13D15:56:41.230985001 25
2025.01.13D15:56:41.230985002 30
2025.01.13D15:56:41.230985003 25
2025.01.13D15:56:41.230985004 35
2025.01.13D15:56:41.230985005 40
2025.01.13D15:56:41.230985006 37.5
2025.01.13D15:56:41.230985007 37.5
2025.01.13D15:56:41.230985008 30
2025.01.13D15:56:41.230985009 30