我试图通过MySQL和PHP获得上周排名最高的照片。我发现贝叶斯公式可能是我所需要的,但我一直在摆弄它却无济于事。
下面的代码不返回任何错误,它只返回一个'0'。为什么那是我一点也没有。
$bayesian_algo = "SELECT
photo_id,
(SELECT count(photo_id) FROM photo_ratings) /
(SELECT count(DISTINCT photo_id) FROM photo_ratings) AS avg_num_votes,
(SELECT avg(rating) FROM photo_ratings) AS avg_rating,
count(photo_id) as this_num_votes,
avg(rating) as this_rating
FROM photo_ratings
WHERE `date` > '$timeframe'
GROUP BY photo_id";
$bayesian_info = $mysqli->query($bayesian_algo);
$all_bayesian_info = array();
while($row=$bayesian_info->fetch_assoc()) array_push($all_bayesian_info,$row);
list($photo_id,$avg_num_votes,$avg_rating,$this_num_votes,$this_rating) = $all_bayesian_info;
$photo_id = intval($photo_id);
$avg_num_votes = intval($avg_num_votes);
$avg_rating = intval($avg_rating);
$this_num_votes = intval($this_num_votes);
$this_rating = intval($this_rating);
$bayesian_result = (($avg_num_votes * $avg_rating) + ($this_num_votes * $this_rating)) / ($avg_num_votes + $this_num_votes);
echo $bayesian_result; // 0??
我的数据库是这样的:
photo_id | user_id | rating | date
其中所有字段都存储为int(我将日期存储为UNIX时间戳)。
我累了,不顾后果地编码,通常情况下,如果有错误消息(或任何东西!),我至少可以得到一点进一步,但是如果我var_dump($all_bayesian_info)
返回0,我就没有办法得到数据。
让我们在mysql查询中自己做复杂的贝叶斯计算!
代码可以这样重写:
$bayesian_algo_result = "SELECT *,
(((resultdata.avg_num_votes * resultdata.avg_rating) + (resultdata.this_num_votes * resultdata.this_rating)) / (resultdata.avg_num_votes + resultdata.this_num_votes)) AS bayesian_result
FROM
(
SELECT
photo_id,
(SELECT count(photo_id) FROM photo_ratings) /
(SELECT count(DISTINCT photo_id) FROM photo_ratings) AS avg_num_votes,
(SELECT avg(rating) FROM photo_ratings) AS avg_rating,
count(photo_id) as this_num_votes,
avg(rating) as this_rating
FROM photo_ratings
WHERE `date` > '$timeframe'
GROUP BY photo_id
) AS resultdata;
";
$bayesian_result_info = $mysqli->query($bayesian_algo_result);
//loop through the rows.
while($row = $bayesian_result_info->fetch_assoc()) {
list(
$photo_id,
$avg_num_votes,
$avg_rating,
$this_num_votes,
$this_rating,
$bayesian_result
) = $row;
echo 'Balesian rating for photo' . $photo_id . ' is: ' . $bayesian_result;
}
注意:
这是一个工作的sql提琴:http://sqlfiddle.com/#!2/d4a71/1/0
我没有对你的公式做任何逻辑上的改变。所以请确保你的公式是正确的。
- 如果/当UNIX时间戳去64位数据类型,那么你将不得不使用MySQL的"bigint"来存储它们(为你的"日期"列)。