aggrTopN

aggrTopN(func, funcArgs, sortingCol, top, [ascending=true])

func is an aggregate function.

funcArgs are the parameters of func. It can be a scalar or vector. It is a tuple if there are more than 1 parameter of func.

sortingCol is a numeric/temporal vector, based on which funcArgs are sorted.

top is an integer or floating-point number.

• If it is an integer, select the first top rows of records for calculation.

• If it is a floating-point number, the value should be less than 1.0 to indicate a percentage. The function will select top of the rows in funcArgs for calculation. If the result is less than 1, select the first row. If the result is not an integer, it is rounded down and at least one row is selected.

ascending is a Boolean value indicating whether to sort the sortingCol in ascending order. It is an optional parameter and the default value is true.

After sorting funcArgs based on sortingCol, aggrTopN applies func to the first top elements in funcArgs. NULL value in sortingCol is treated as the minimum value.

aggrTopN(func=sum, funcArgs=1 2 3 4 5, sortingCol=5 1 2 3 4, top=3, ascending=false)
// output
10

aggrTopN(func=corr,funcArgs=[1..5, 3 9 4 2 5], sortingCol=2 3 4 5 3, top=3)
// output
0.052414

aggrTopN takes the first 3 elements for calculation as top is set to 3. There are three occurrences of 3 in the following example, and only three sortingCol elements are selected.

aggrTopN(func=min,funcArgs=1 6 4 -6 4 5, sortingCol=2 3 3 3 4 5, top=3)
// output
1

Calculate the average price of the highest 25% of the daily trading volume for each stock.

t = table(`A`A`A`B`B`B`B`B`B`B`B as sym, 09:30:06 09:30:28 09:31:46 09:31:59 09:30:19 09:30:43 09:31:23 09:31:56 09:30:44 09:31:25 09:31:57 as time, 10 20 10 30 20 40 30 30 30 20 40 as volume, 10.05 10.06 10.07 10.05 20.12 20.13 20.14 20.15 20.12 20.13 20.16 as price);
t;

select aggrTopN(func=avg, funcArgs=price, sortingCol=volume, top=0.25, ascending=false) from t group by sym