![rating]( "rating")
本文介绍了如何计算Python / SQLAlchemy / Flask中的累积移动平均值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我会给一些上下文，这是有道理的。我在表格（评级）中获取产品的客户评级，并希望能够根据时间返回评级的累积移动平均线。
一个基本的例子如下每日评级：
02二月 - 评分：5 - 累计平均：5
03二月 - 评分：4 - Cum Avg：（5 + 4）/ 2 = 4.5
04 FEB - 评分：1 - Cum Avg：（5 + 4 + 1）/ 3 = 3.3
05 FEB - 评分：5 - Cum Avg：（5 + 4 + 1 + 5）/ 4 = 3.75
等等...
我试图想到一个不会严重缩放的方法。
我目前的想法是有一个函数，当一行被插入到排名表中，并且基于该产品的上一行计算出Cum Avg
所以这些字段会是这样的：
TABLE：Rating
| RatingId | DateTime | ProdId | RatingVal | RatingCnt | CumAvg |
但是，这似乎是一个相当狡猾的方式来存储数据。
完成此操作的（或任何）方法是什么？如果我要使用触发器，你怎么去SQLAlchemy这样做？
任何和所有的建议表示赞赏！$ b
解决方案
我不知道SQLAlchemy，但我可能会使用这样的方法：
将累计平均值和评分数与个人评分分开存储。 每次获得新评分时，更新累计平均数和评分数：
new_count = old_count + 1 new_average =（（old_average * old_count）+ new_rating）/ new_count >
更新平均值和评分count可以用一个SQL语句来完成。
I'll give some context so it makes sense. I'm capturing Customer Ratings for Products in a table (Rating) and want to be able to return a Cumulative Moving Average of the ratings based on time.
A basic example follows taking a rating 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'm trying to think of an approach that won't scale horribly.
My current idea is to have a function that is tripped when a row is inserted into the Rating table that works out the Cum Avg based on the previous row for that product
So the fields would be something like:
TABLE: Rating
| RatingId | DateTime | ProdId | RatingVal | RatingCnt | CumAvg |
But this seems like a fairly dodgy way to store the data.
What would be the (or any) way to accomplish this? If I was to use the 'trigger' of sorts, how do you go about doing that in SQLAlchemy?
Any and all advice appreciated!
解决方案
I don't know about SQLAlchemy, but I might use an approach like this:
- Store the cumulative average and rating count separately from individual ratings.
- Every time you get a new rating, update the cumulative average and rating count:
- new_count = old_count + 1
- new_average = ((old_average * old_count) + new_rating) / new_count
- Optionally, store a row for each new rating.
Updating the average and rating count could be done with a single SQL statement.
这篇关于如何计算Python / SQLAlchemy / Flask中的累积移动平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！