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 пользователя оценили, насколько смешным является видео, и только один из них оценил его интересность, общее среднее значение будет искажено. Я хочу отсортировать по среднему рейтингу одного тега плюс среднее значение другого тега, деленное на 2.

---

Вот некоторые примеры данных, как и было запрошено.

Таблицы:

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

Пример данных для всех рейтингов видео с идентификатором 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