我有一个客户,我正在开发一个视频网站。他想从数据库中拉出2个视频,以显示前端用户投票。
视频表有一个"votes"字段。他想随机抽取视频,但对票数较高的视频给予更高的偏好。某种算法给予投票优先权,而不仅仅是直接的"投票最多的2个视频被拉下"。
任何想法?
我的视频表是这样的:
$data[] = array(
'id' => $row->id,
'filename' => $row->filename,
'added' => $row->added,
'userId' => $row->userId,
'contestId' => $row->contestId,
'complete' => $row->complete,
'ended' => $row->ended,
'votes' => $row->votes
);
只要使用一个很好的平衡ORDER BY votes * ($offset + rand()) DESC
的总和,其中偏移量平衡随机性。如果偏移量为0,则大部分是随机的,如果偏移量为1或更高,则未投票的视频将永远不会显示。
您可以在试运行中尝试生成的视频几次,看看是否喜欢它。: -)
编辑:答案取决于将在数据库中的投票范围和分布
考虑以下内容…
SELECT * FROM weighted;
+----+--------+
| id | weight |
+----+--------+
| 1 | 3 |
| 2 | 1 |
| 3 | 6 |
| 4 | 5 |
| 5 | 10 |
| 6 | 2 |
| 7 | 3 |
| 8 | 7 |
| 9 | 7 |
+----+--------+
So, crudely...
SELECT *,RAND()*weight FROM weighted;
+----+--------+--------------------+
| id | weight | RAND()*weight |
+----+--------+--------------------+
| 1 | 3 | 1.7466305203238788 |
| 2 | 1 | 0.6615251914239724 |
| 3 | 6 | 3.3659728051777673 |
| 4 | 5 | 4.102008970549338 |
| 5 | 10 | 4.190225986940997 |
| 6 | 2 | 1.2678152837490806 |
| 7 | 3 | 2.737411332072142 |
| 8 | 7 | 4.624405276611824 |
| 9 | 7 | 3.9601472736896457 |
+----+--------+--------------------+
SELECT *,RAND()*weight x FROM weighted ORDER BY x DESC LIMIT 1;
+----+--------+--------------------+
| id | weight | x |
+----+--------+--------------------+
| 8 | 7 | 4.6799722441285585 |
+----+--------+--------------------+
SELECT *,RAND()*weight x FROM weighted ORDER BY x DESC LIMIT 1;
+----+--------+-------------------+
| id | weight | x |
+----+--------+-------------------+
| 9 | 7 | 5.331623214605845 |
+----+--------+-------------------+
SELECT *,RAND()*weight x FROM weighted ORDER BY x DESC LIMIT 1;
+----+--------+-------------------+
| id | weight | x |
+----+--------+-------------------+
| 4 | 5 | 4.931961209449881 |
+----+--------+-------------------+
SELECT *,RAND()*weight x FROM weighted ORDER BY x DESC LIMIT 1;
+----+--------+-------------------+
| id | weight | x |
+----+--------+-------------------+
| 8 | 7 | 6.230309537826394 |
+----+--------+-------------------+
SELECT *,RAND()*weight x FROM weighted ORDER BY x DESC LIMIT 1;
+----+--------+-------------------+
| id | weight | x |
+----+--------+-------------------+
| 5 | 10 | 5.470934962361059 |
+----+--------+-------------------+