0

Say there is a table as below

SYMBOL,PRICE,DATE,TIME
ABC,123,D1,14
XYZ,500,D1,14
PRQ,200,D1,14
ABC,124,D1,15
XYZ,501,D1,15
PRQ,201,D1,15
ABC,125,D2,10
XYZ,502,D2,10
PRQ,202,D2,10
ABC,126,D2,11
XYZ,503,D2,11
PRQ,203,D2,11

I need to get average price for N latest records of each symbol. For N=3, Output must be

ABC,125
XYZ,502
PQR,503

For the symbol ABC, Basically it considered the 3 records viz,
ABC,126,D2,11
ABC,125,D2,10
ABC,124,D1,14

I found something similar here MySQL calculate moving average of N rows However that query does a one time operation on the given data.

I need to compute the Average on each new record.

If someone can help with queries other than cursors.

unbesiegbar
  • 461
  • 2
  • 6
  • 17
  • 1
    See the linked question for how to write a query that gets N latest results per group. Then you can calculate the averages in an outer query of that. – Barmar Aug 12 '21 at 18:33

0 Answers0