MySQL按两个平均值的平均值排序

Question

我在一个竞赛网站上工作，那里有两种类型的用户，普通网站成员和评委。每个人都可以使用拖放工具按照他们选择的顺序对特定竞赛中的参赛作品进行排序。当它们完成后，相关的参赛作品ids被附加一个排名值，然后可以用来确定竞赛中哪个参赛作品获得了最高的平均分数。获胜者实际上将由每组的平均值来决定。

我希望做的是用一个表格显示特定竞赛中的每个参赛作品，并显示标题，然后显示3个值，该参赛作品的avg_normal，该参赛作品的avg_judge，然后这两个值相加并除以2，因此avg_normal和avg_judge各占avg_all的50%。最后，按avg_all对表进行排序。

avg_all = ((avg_normal + avg_judge) / 2)

他们按顺序排列entry_ids 1、2、3、4、5。排名值从零开始，因此：

entry_id, entry_ranking, author_id
1, 0, 1
2, 1, 1
3, 2, 1
4, 3, 1
5, 4, 1

我希望在1-100的范围内确定平均值，所以0= 100分，1= 90，2= 80，3= 70的条目排名，以及任何大于4=5分的条目

每个用户都连接到另一个表中的一个组，因此他们要么是普通用户，要么是法官

我希望能够编写一个查询来查找

1.)普通用户的平均投票分数

2.)平均评委用户投票得分

3.)正常和判断分数的平均值。

因此正常用户平均值= 93.3333，判断平均值= 70，总平均值= 81.66665

感谢下面的答案，这两个查询都像冠军一样工作。

Answer 1

请注意以下事项：

• 我假设在members中有一个字段user_type，用于存储'NORMAL‘或’

‘

• 我已经删除了与数据的连接和titles.title的group by，因为我看不出它们与您的平均值有什么关系。

。

SELECT
  t.title,
  AVG(CASE WHEN user_type = 'NORMAL' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value = '4', 5, 0) END) AS avg_normal,
  AVG(CASE WHEN user_type = 'JUDGE' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value = '4', 5, 0) END) AS avg_judge,
  (AVG(CASE WHEN user_type = 'NORMAL' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value = '4', 5, 0) END) +
  AVG(CASE WHEN user_type = 'JUDGE' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value = '4', 5, 0) END)) / 2 AS avg_all
FROM rankings r
LEFT JOIN titles t
  ON r.entry_id = t.entry_id
LEFT JOIN members m
  ON t.author_id = m.member_id
WHERE r.contest_id IN ('CONTEST ID NUMBER')
GROUP BY
  t.title
ORDER BY
  avg_all;