php - Mysql 按用户排序的项目得到总体顺序

Question

我有一个电影列表，用户可以使用 jQuery UI Sortable 按他们最喜欢的顺序进行排名(一切都很好)。阶数越低，薄膜 (1) 越好，阶数越高 (26) 越差。电影列表可能是无限的，但在数据库中是固定的(用户无法添加更多)，因此用户只能从 x 电影列表中进行选择。

电影不必出现在用户列表中，如果他们还没有看过电影 5，那么它不会被包含在内(这可能会加剧问题)。

目前这存储在表中:

film_id | user_id | order
4         2         3
5         3         3
6         2         1
7         2         2
7         3         1 
8         3         2

我想要但不知道从哪里开始的是一个整体的“前 10 名”风格列表。即电影 7 最受欢迎，因为它出现在人们的列表中更高的位置并且出现在更多的列表中。电影 6 可能是最受欢迎的，但它只出现在一个列表中？!

我被逻辑和 Mysql 查询困住了!

我想我可能需要以某种方式对订单进行加权？或者有一个单独的表格，其中包含每部电影的分数，并在每次编辑后更新它。如果以下查询仅基于表中的项目数，但当我想在方程中添加位置时则不然，那么它似乎是正确的想法。

SELECT ff.film_id, COUNT(ff.film_id) AS cnt, SUM(ff.order) AS rank FROM
`favourite_film` AS ff GROUP BY ff.film_id ORDER BY cnt DESC, rank ASC

我想我需要表中所有电影的数量以及顺序的总和(但相反？)，然后我的理论就变得平淡了!

任何帮助或链接将不胜感激。谢谢。

Answer 1

根据您的“业务规则”，我认为您应该找到某种计算方法来考虑位置和“票数”。

只是一个随机猜测，但为什么不按 COUNT(votes)/AVG(pos) 排序？出于可维护性的原因，您可能需要排除排名函数:

CREATE FUNCTION ranking(average_pos REAL, vote_count INT)
RETURNS REAL
DETERMINISTIC
  RETURN vote_count/average_pos;

现在的查询很简单:

SELECT film_id,
       AVG(pos) as a, COUNT(*) as c, ranking(AVG(pos),COUNT(*)) AS rank
  FROM vote GROUP BY film_id
  ORDER BY ranking(AVG(pos), COUNT(*)) DESC;

使用您的示例进行生产:

+----------+------+----+----------------+
| FILM_ID  |  A   | C  |      RANK      |
+----------+------+----+----------------+
|       7  | 1.5  | 2  | 1.333333333333 |
|       6  | 1    | 1  | 1              |
|       8  | 2    | 1  | 0.5            |
|       5  | 3    | 1  | 0.333333333333 |
|       4  | 3    | 1  | 0.333333333333 |
+----------+------+----+----------------+

参见http://sqlfiddle.com/#!2/3b1d9/1