aggrTopN

Example 1 Calculate the average of price corresponding to the top 3 records with the largest volume.

price = 10.0 20.0 30.0 40.0 50.0
volume = 5 2 5 1 4
aggrTopN(func=avg, funcArgs=price, sortingCol=volume, top=3, ascending=false)
// output: 30

Example 2 Calculate the correlation between price and volume for the top 3 records with the largest volume. In this case, func is a binary aggregation function, and funcArgs should be passed as a tuple.

volume = 2 3 4 5 3
price = 10.0 20.0 30.0 40.0 50.0
aggrTopN(func=corr, funcArgs=(volume, price), sortingCol=2 3 4 5 3, top=3, ascending=true)
// output: 0.69338

Example 3

Calculate the average of price corresponding to the top N records with the largest volume. Here, N can be specified either directly as an integer (top = N) or as a proportion (top = N / size).

price = 10 20 30 40 50
volume = 1 2 3 4 5

// top=3 (integer): take the first 3 rows
aggrTopN(func=avg, funcArgs=price, sortingCol=volume, top=3)
// take [10, 20, 30], output: 20

// top=0.6 (float): take the first floor(5*0.6)=3 rows
aggrTopN(func=avg, funcArgs=price, sortingCol=volume, top=0.6)
// take [10, 20, 30], output: 20

// top=0.5 (float): take the first floor(5*0.5)=2 rows (rounded down)
aggrTopN(func=avg, funcArgs=price, sortingCol=volume, top=0.5)
// take [10, 20], output: 15

// top=0.01 (float): 5*0.01=0.05, floor to 0, but at least 1 row is selected
aggrTopN(func=sum, funcArgs=price, sortingCol=volume, top=0.01)
// take [10], output: 10

Example 4

For each stock on each day, calculate the average of the value corresponding to the smallest 40% of the factor.

trade_date=sort(take(2017.01.11..2017.01.12,20))
secu_code=take(`600570`600000,20)
value=1..20
tb=table(trade_date,secu_code,value)  

select aggrTopN(avg, funcArgs=value, sortingCol=value, top=0.4, ascending=true) as factor_value from tb group by trade_date,secu_code