mavgTopN

Syntax

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

Please see Moving TopN Functions (mTopN functions) for the parameters and windowing logic.

Details

After stably sorting S in the specified ascending order, the function calculates the average for the first top elements of X in the sliding window.

Examples

$ X = 1..7
$ S = 0.3 0.5 0.1 0.1 0.5 0.2 0.4
$ mavgTopN(X, S, 4, 2)
[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)
[,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)

#0

#1

1

6

1.5

6.5

2

7

2.5

7.5

4

9

$ 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)

#0

#1

1

1.5

7

2

7.5

2.5

7.5

4

8.5

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

114

107

APPL

2020.01.03

66

106

IBM

2020.01.04

36

106

APPL

2020.01.05

52

101

IBM

2020.01.06

28

100

APPL

2020.01.07

55

108

IBM

2020.01.08

54

106

APPL

2020.01.09

103

106

IBM

2020.01.10

94

104

APPL

2020.01.11

82

102

IBM

2020.01.12

98

103

APPL

2020.01.13

118

101

IBM

2020.01.14

61

105

APPL

2020.01.15

43

105

IBM

2020.01.16

41

104

APPL

2020.01.17

111

106

IBM

2020.01.18

119

103

APPL

2020.01.19

24

107

IBM

2020.01.20

22

109

APPL

2020.01.21

26

103

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

code

date

mavgTopN_close

APPL

2020.01.03

106

APPL

2020.01.05

103.5

APPL

2020.01.07

105

APPL

2020.01.09

106.6667

APPL

2020.01.11

104.6667

APPL

2020.01.13

103

APPL

2020.01.15

103

APPL

2020.01.17

104.3333

APPL

2020.01.19

103

APPL

2020.01.21

104

IBM

2020.01.02

107

IBM

2020.01.04

106.5

IBM

2020.01.06

104.3333

IBM

2020.01.08

106.3333

IBM

2020.01.10

105.6667

IBM

2020.01.12

104.3333

IBM

2020.01.14

104

IBM

2020.01.16

104

IBM

2020.01.18

103.3333

IBM

2020.01.20

103.6667

Related function: mavg