aggrTopN
Syntax
aggrTopN(func, funcArgs, sortingCol, top, [ascending=true])
Arguments
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.
Details
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.
First Release
1.30.16/2.00.4
Examples
$ aggrTopN(func=sum, funcArgs=1 2 3 4 5, sortingCol=5 1 2 3 4, top=3, ascending=false)
10
$ aggrTopN(func=corr,funcArgs=[1..5, 3 9 4 2 5], sortingCol=2 3 4 5 3, top=3)
0.052414
$ aggrTopN(func=min,funcArgs=1 6 4 -6 4 5, sortingCol=2 3 3 3 4 5, top=3)
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;
sym |
time |
volume |
price |
|---|---|---|---|
A |
09:30:06 |
10 |
10.05 |
A |
09:30:28 |
20 |
10.06 |
A |
09:31:46 |
10 |
10.07 |
B |
09:31:59 |
30 |
10.05 |
B |
09:30:19 |
20 |
20.12 |
B |
09:30:43 |
40 |
20.13 |
B |
09:31:23 |
30 |
20.14 |
B |
09:31:56 |
30 |
20.15 |
B |
09:30:44 |
30 |
20.12 |
B |
09:31:25 |
20 |
20.13 |
B |
09:31:57 |
40 |
20.16 |
$ select aggrTopN(func=avg, funcArgs=price, sortingCol=volume, top=0.25, ascending=false) from t group by sym
sym |
aggrTopN_avg |
|---|---|
A |
10.06 |
B |
20.145 |