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