msumTopN

Syntax

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

Please see mTopN for the parameters and windowing logic.

Details

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 sums up the first top elements.

Examples

X = 1..7
S = 0.3 0.5 0.1 0.1 0.5 0.2 0.4
msumTopN(X, S, 4, 2)
// output
[1,3,4,7,7,7,10]

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

X = matrix(1..5, 6..10)
S = 2022.01.01 2022.02.03 2022.01.23 2022.04.06 2021.12.29
msumTopN(X, S, 3, 2)
#0 #1
1 6
3 13
4 14
5 15
8 18
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)
msumTopN(X, S, 3, 2)
#0 #1
1
3 7
4 15
5 15
8 17

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)
code date volume close
IBM 2020.01.02 50 107
APPL 2020.01.03 55 100
IBM 2020.01.04 75 100
APPL 2020.01.05 84 108
IBM 2020.01.06 46 103
APPL 2020.01.07 100 101
IBM 2020.01.08 96 100
APPL 2020.01.09 84 102
IBM 2020.01.10 60 107
APPL 2020.01.11 40 103
IBM 2020.01.12 92 105
APPL 2020.01.13 61 106
IBM 2020.01.14 86 107
APPL 2020.01.15 41 102
IBM 2020.01.16 85 103
APPL 2020.01.17 72 105
IBM 2020.01.18 46 108
APPL 2020.01.19 25 100
IBM 2020.01.20 114 102
APPL 2020.01.21 50 104

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

select code, date, msumTopN(close, volume, 5, 3, false) from t context by code
code date msumTopN_close
APPL 2020.01.03 100
APPL 2020.01.05 208
APPL 2020.01.07 309
APPL 2020.01.09 311
APPL 2020.01.11 311
APPL 2020.01.13 311
APPL 2020.01.15 309
APPL 2020.01.17 313
APPL 2020.01.19 313
APPL 2020.01.21 315
IBM 2020.01.02 107
IBM 2020.01.04 207
IBM 2020.01.06 310
IBM 2020.01.08 307
IBM 2020.01.10 307
IBM 2020.01.12 305
IBM 2020.01.14 312
IBM 2020.01.16 312
IBM 2020.01.18 315
IBM 2020.01.20 314

Related function: msum