MongoDb Aggregation - sliding window averages?

I'm trying to use the mongodb aggregation framework to average the last hour of "scores" per term, every minute. I've no issues grouping by minute of course.. just am not clear on the best way to group by minute but then have mongo go back an hour per minute to calculate the average score. After searching here and finding some options, I get the sense that $bucket is not the best approach due to performance reasons, same for map/reduce.. so just looking for opinions. I won't need to run this query often as I'll typically be doing this running calculation in my application every minute.. but need to first "seed" the data based off the raw data I've collected, and then would also like to have this query available to run in the case of a service outage and my running calculation stops..

sample raw documents:

{      "_id" : ObjectId("603e472aba52820ab3e139ae"),      "term" : "sports",      "created_at" : ISODate("2021-03-02T14:09:46.701Z"),      "score" : -0.331166666666667,  },  {      "_id" : ObjectId("6031272aba52820ab3e139ef"),      "term" : "sports",      "created_at" : ISODate("2021-03-02T14:09:55.000Z"),      "score" : 1.05  },  {      "_id" : ObjectId("io12iopeipweipqeqweqwewq"),      "term" : "food",      "created_at" : ISODate("2021-03-10T16:02:12.091Z"),      "score" : 0.25,  }  

I'd like a result set similar to

term   | for minute "x"      | average of scores from x to (x - one hour)  ------------------------------------------------------------------------------------  sports | 03/02/2021 14:10:00 | (average of all "sports" scores from 3/31/2021 13:10:00 to 3/31/2021 14:10:00)  food   | 03/02/2021 14:10:00 | (average of all "food" scores from 3/31/2021 13:10:00 to 3/31/2021 14:10:00)  sports | 03/02/2021 14:11:00 | (average of all "sports" scores from 3/31/2021 13:11:00 to 3/31/2021 14:11:00)  

Thank you in advance for any advice someone might be willing to offer!

https://stackoverflow.com/questions/66897197/mongodb-aggregation-sliding-window-averages April 01, 2021 at 09:08AM