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

Question

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

4

我正在开发一个竞赛网站，其中有两种类型的用户：普通站点成员和评委。每个用户都可以使用拖放工具按照自己选择的顺序对特定竞赛中的条目进行排序。完成后，相关的条目ID将附加一个排名值，然后可以使用该值确定哪个竞赛条目得到了最高平均分数。获胜者实际上是通过对每个组的平均值进行平均来确定的。

我的目标是最终获得一张表格，显示特定竞赛中的每个条目及其标题，然后显示3个值：该条目的avg_normal、avg_judge和这两个值相加并除以2得到的avg_all，因此avg_normal和avg_judge各占avg_all的50％。最后，按avg_all对表格进行排序。

avg_all = ((avg_normal + avg_judge) / 2)

他们按顺序对条目ID 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。由于下面的答案，两个查询都像冠军一样工作。

- noahkuhn

2个回答

1

这个更改只是包装了原始查询，lines314159 承担了大部分工作。

选择 aa.title，aa.avg_normal，aa.avg_judge，(aa.avg_normal + aa.avg_judge) / 2 AS avg_all 从 ( SELECT t.title, AVG(CASE WHEN group_id = '6' 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 group_id = '7' 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 FROM exp_rankings r LEFT JOIN exp_weblog_titles t ON r.entry_id = t.entry_id LEFT JOIN exp_members m ON t.author_id = m.member_id WHERE r.contest_id IN ('22') GROUP BY t.title ORDER BY avg_all) as aa;

- user271573

这已经完成了99％，我认为我遇到了等于=符号的语法问题（CASE WHEN group_id ='7' THEN IF）。作为一个测试，如果我将其更改为！=，它可以工作并给我其他值，但是现在它只返回NULL。 - noahkuhn

明白了，其中一个连接有点问题是我的错，(LEFT JOIN exp_members m ON t.author_id = m.member_id) 应该是 (LEFT JOIN exp_members m ON r.author_id = m.member_id)。谢谢 - noahkuhn

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- lins314159 · Accepted Answer

请注意以下内容：

我假设在成员中有一个名为user_type的字段，其中存储'NORMAL'或'JUDGE'
我已经删除了与data的连接以及对titles.title的分组，因为我不认为它们与您的平均值相关。

.

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;