MySQL:如何将多个条件 AVG 值与 Group By 组合

Question

我有一个网站，允许用户根据不同的标签对视频进行评分，例如他们认为视频有多有趣或有多有趣，从而允许用户以多种方式对同一视频进行评分。

该网站目前允许按特定标签对视频进行排序，该标签会考虑该视频标签的平均用户评分。我现在正在尝试创建一个 MySQL 查询，该查询可以根据多个标签的平均聚合评分对视频进行排序。例如，展示所有既有趣又有趣的视频。

我不知道如何通过一个查询来做到这一点。

这是用来按一个标签排序的

SELECT *, AVG(ratings.rating) as avgTagFunny 
FROM videos, ratings 
WHERE videos.id = ratings.video_id 
AND ratings.tag_id = 2 
GROUP BY video_id
ORDER BY avgTagFunny DESC

我最接近的是做这样的事情

SELECT *, AVG(ratings.rating) as avgTag, count(distinct tag_id) as distinctTags
FROM videos, ratings 
WHERE videos.id = ratings.video_id 
AND ratings.tag_id IN (2, 12) 
GROUP BY video_id 
HAVING distinctTags > 1
ORDER BY avgTag DESC

这样做的问题是，它取所有评分的平均值，因此如果 3 位用户评价一个视频有多有趣，而只有 1 位用户评价它有多有趣，那么总体平均值就会出现偏差。我想要的是按照一个标签的平均评分加上另一个标签的平均评分除以 2 进行排序。

<小时/>

这是根据要求提供的一些示例数据。

表格:

videos: id, title
tags: id, name
users: id, name
ratings: id, user_id, video_id, tag_id, rating

视频 ID 342 的所有评分的示例数据:

1, 7, 342, 2, 90
2, 10, 342, 2, 80
3, 10, 342, 12, 70
4, 11, 342, 2, 85
5, 7, 342, 12, 50

如您所见，标签 2 的平均评分为 85，标签 12 的平均评分为 60。这两个评分加在一起的平均值为 72.5。我们不需要所有评分的平均值，因为这将是 75，并且偏向于一个标签，因为标签 2 的数量多于标签 12 的数量。

我按一个标签的平均评分发布订单的第一个查询。我发布的第二个查询按两个标签的所有评级对它们进行排序，但并不理想，因为它是倾斜的。

我正在寻找一种按(标签 2 的平均评分 + 标签 12 的平均评分)/2 对结果进行排序的方法。我希望这是有道理的!

Answer 1

SELECT  video_id, AVG(rating) rating
FROM    (
        SELECT  video_id, AVG(rating) rating
        FROM    ratings
        WHERE   tag_id IN (2, 12)
        GROUP BY
                video_id, tag_id
        ) q
GROUP BY
        video_id
ORDER BY
        rating DESC