加权平均数 [英] Weighted Mean

问题描述

我有一个现有的Web应用程序，该应用程序允许用户根据他们的难度对项目进行评分". (0到15).目前，我只是简单地取每个用户意见的平均值，然后直接从MySQL提出平均值.但是，对我(和我的用户)来说，逐渐弄清楚数字的加权会更合适.

I have an existing web app that allows users to "rate" items based on their difficulty. (0 through 15). Currently, I'm simply taking the average of each user's opinion and presenting the average straight from MySQL. However, it's becoming clear to me (and my users) that weighting the numbers would be more appropriate.

奇怪的是，几个小时的Google-ing并没有出现太多问题.我确实找到了两篇文章，这些文章展示了基于贝叶斯过滤器"(我部分理解)的站点范围分级系统. 这里的一个例子:

Oddly enough, a few hours of Google-ing hasn't turned up much. I did find two articles that showed site-wide ratings systems based off of "Bayesian filters" (which I partially understand). Here's one example:

公式为:

WR =(V/(V + M))* R +(M/(V + M))* C

WR=(V/(V+M)) * R + (M/(V+M)) * C

位置:

* WR=Weighted Rating (The new rating)
* R=Average Rating (arithmetic mean) so far
* V=Number of ratings given
* M=Minimum number of ratings needed
* C=Arithmetic mean rating across the whole site

我喜欢在这里根据每个项目的投票总数增加权重的想法...但是，因为我网站上的难度级别在每个项目之间都可能很大，取"C"(算术平均评分)整个网站)无效.

I like the idea here of ramping up the weighting based on the total number of votes per item...however, because the difficulty levels on my site can range drastically from item to item, taking "C" (arithmetic mean rating across the whole site) is not valid.

所以，我的问题要重述:

so, a restate of my question:

我尝试使用MySQL，PHP或同时使用这两种方法:

Using MySQL, PHP, or both, I'm try to get from aritmetic mean:

(5 + 5 + 4)/3 = 4.67 (rounded)

...加权平均值:

rating  / weight
5 / 2 (since it was given 2 times)
5 / 2
4 / 1

(sum[(rate * weight)])/(sum of weights)
(5 * 2) + (5 * 2) + (4 * 1) / (2 + 2 + 1)
(24)/(5)
= 4.8

推荐答案

这是一个有关如何直接在MySQL中直接进行操作的简单示例.当然，您需要在子查询上添加一个条件，以仅获取相关项目的投票，而不是所有投票.

This is a simple example about how to do it in MySQL directly. You of course would need to add a condition on the subquery to get only the votes for the relevant item instead of all the votes.

mysql> create table votes( vote int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into votes values (5),(5),(4);
Query OK, 3 row affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from votes;
+------+
| vote |
+------+
|    5 |
|    5 |
|    4 |
+------+
3 rows in set (0.00 sec)

mysql> select vote,count(vote),vote*count(vote) from votes group by vote;
+------+-------------+------------------+
| vote | count(vote) | vote*count(vote) |
+------+-------------+------------------+
|    4 |           1 |                4 |
|    5 |           4 |               20 |
+------+-------------+------------------+
2 rows in set (0.00 sec)

mysql> select sum(vt)/sum(cnt) FROM (select 
count(vote)*count(vote) as cnt,vote*count(vote)*count(vote) 
as vt from votes group by vote) a;
+------------------+
| sum(vt)/sum(cnt) |
+------------------+
|           4.8000 |
+------------------+
1 row in set (0.00 sec)

这篇关于加权平均数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！