Geek Answers Handbook

How to calculate cumulative moving average in Python / SQLAlchemy / Flask

I will talk about some context, so that makes sense. I take the Customer Ratings for the products in the table (Rating) and want to be able to return the cumulative moving average of the time ratings.

A basic example follows an assessment per day:

02 FEB - Rating: 5 - Cum Avg: 5 03 FEB - Rating: 4 - Cum Avg: (5+4)/2 = 4.5 04 FEB - Rating: 1 - Cum Avg: (5+4+1)/3 = 3.3 05 FEB - Rating: 5 - Cum Avg: (5+4+1+5)/4 = 3.75 Etc...

I am trying to think of an approach that will not scale terribly.

My current idea is to have a function that fires when you insert a row into a rating table that Cum Avg develops based on the previous row for this product.

So the fields will look something like this:

 TABLE: Rating | RatingId | DateTime | ProdId | RatingVal | RatingCnt | CumAvg |

But this seems like a pretty dodgy way to store data.

What would be (or any) way to do this? If I used a trigger, how do you do it in SQLAlchemy?

Any advice is appreciated!

+8
python flask flask-sqlalchemy sqlalchemy
source share
2 answers

I do not know about SQLAlchemy, but I could use this approach:

  • Keep the accumulated average and number of ratings separate from individual ratings.
  • Every time you get a new rating, update the total average and rating:
    • new_count = old_count + 1
    • new_average = ((old_average * old_count) + new_rating) / new_count
  • If necessary, save the line for each new rating.

Averages and ratings can be updated with a single SQL statement.

+5
source share

I think you should save MA in a list of 2 elements, this would be much simpler:

 #first rating 5 is rating number 0 a = [5,0] #next: for i in rating: a = [(a[0]*a[1]+lastRating)/(a[1]+1),a[1]+1]

Bye

-5
source share

More articles:


All Articles