mavgTopN

mavgTopN(X, S, window, top, [ascending=true], [tiesMethod='oldest'])

Please see mTopN for the parameters and windowing logic.

Within a sliding window of given length (measured by the number of elements), the function stably sorts X by S in the order specified by ascending, then calculates the average of the first top elements.

X = 1..7
S = 0.3 0.5 0.1 0.1 0.5 0.2 0.4
mavgTopN(X, S, 4, 2)
// output
[1,1.5,2,3.5,3.5,3.5,5]

X = NULL 1 2 3 4 NULL 5
S = 3 5 1 1 5 2 4
mavgTopN(X, S, 4, 2)
// output
[,1,2,2.5,2.5,2.5,3]

X = matrix(1..5, 6..10)
S = 2022.01.01 2022.02.03 2022.01.23 2022.04.06 2021.12.29
mavgTopN(X, S, 3, 2)

X = matrix(1..5, 6..10)
S = matrix(2022.01.01 2022.02.03 2022.01.23 NULL 2021.12.29,NULL 2022.02.03 2022.01.23 2022.04.06 NULL)
mavgTopN(X, S, 3, 2)

A table with columns code, date, close and volume.

t = table(take(`IBM`APPL, 20) as code, 2020.01.01 + 1..20 as date, rand(100,20) + 20 as volume, rand(10,20) + 100.0 as close)

Calculate the average of the closing prices of the top 3 records with the highest trading volume in the window for each stock.

select code, date, mavgTopN(close, volume, 5, 3, false) from t context by code

Related function: mavg