groupinggreatest-n-per-groupdolphindb

How to calculate the average of the last four records within each group in DolphinDB?


I want to calculate the average of the last four records in each group. I'm wondering if there's an efficient approach to achieve this. Could someone share any tips or methods?


Solution

  • If you are working with time-series data, you can utilize aggrTopN to extract the last four records sorted by time. Refer to the following script:

    sym = rand(`A`B`C, 15)
    price= rand(49..51, 15)
    qty = rand(100..200, 15)
    time = 09:47:00 + 1..15
    t1 = table(timestamp, sym, qty, time, price);
    
    select aggrTopN(avg, price, time, 4, false) from t1 group by sym