如何在Python/SQLAlchemy/Flask中计算累积移动平均值
我将提供一些背景，以便它有意义。我正在表（评级）中捕获产品的客户评级，并希望能够返回基于时间的评级的累积移动平均值。
一个基本的例子是每天进行评级：
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...
我正在尝试考虑一种不会严重扩展的方法。
我当前的想法是有一个函数，当将一行插入到评级表中时，该函数会根据该产品的前一行计算出 Cum Avg
因此，字段将类似于：
TABLE: Rating
| RatingId | DateTime | ProdId | RatingVal | RatingCnt | CumAvg |
但这似乎是一种相当狡猾的方式来存储数据。
实现这一目标的（或任何）方法是什么？如果我要使用某种“触发器”，您如何在 SQLAlchemy 中做到这一点？
任何和所有建议表示赞赏！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我不了解 SQLAlchemy，但我可能会使用这样的方法：
更新平均值和评分计数可以使用单个 SQL 语句来完成。
I don't know about SQLAlchemy, but I might use an approach like this:
Updating the average and rating count could be done with a single SQL statement.