My question is about calculating the quote data in DolphinDB. The table contains four columns (ticker, date, close and volume) and is grouped by ticker and sorted by date. I want to do a window calculation and assume the window size to be 20. My purpose is to sort the data in volume column in a window and take the top five volume records to calculate the average of the corresponding values of close. What is the most efficient way to calculate it in DolphinDB?
1 Answers
Currently, there is no efficient algorithm for this case, but you can use the function moving together with user-defined functions to obtain the desired result. In the future, DolphinDB will develop functions based on the scenario.
Use a single line of code in version 1.30.15 and above to perform the calculation:
//suppose t is a four-column table
t = table(take(`IBM, 100) as code, 2020.01.01 + 1..100 as date, rand(100,100) + 20 as volume, rand(10,100) + 100.0 as close)
user-defined anonymous aggregate function is supported in function moving
select code, date, moving(defg(vol, close){return close[isort(vol, false).subarray(0:min(5,close.size()))].avg()}, (volume, close), 20) from t context by code
Related