Рассчитать среднее для конкретных строк в MySQL
У меня есть следующие две таблицы:
content:
| id | name | created_by |
+----+-------+------------+
| 1 | test | me |
| 2 | test2 | me |
| 3 | test3 | you |
| 4 | test4 | me |
like_dislike:
| id | content_id | like | dislike |
+----+------------+------+---------+
| 1 | 1 | 1 | 0 |
| 2 | 2 | 1 | 0 |
| 3 | 2 | 0 | 0 |
Мне нужно рассчитать средние лайки последних 2 строк контента, где created_by = me,
Так, например, последние два content_ids — это 2 и 4.
Таблица like_dislike содержит записи для content_id 2 со строкой, где like равно 1, и строкой, где like равно 0, поэтому среднее значение равно 1/2.
Я пробовал следующий запрос, но он не будет работать:
SELECT ((SELECT COUNT(*)
FROM `like_dislike`
WHERE `like`='1' AND `content_id`= pi.id) / (COUNT(*))
FROM content AS pi
WHERE pi.create_by = 'me'
ORDER BY pi.id DESC
LIMIT 2
Решение
SELECT x.*
, AVG(liked)
FROM content x
JOIN content y
ON y.created_by = x.created_by
AND y.id >= x.id
JOIN like_dislike z
ON z.content_id = x.id
WHERE x.created_by = 'me'
GROUP BY x.id
HAVING COUNT(DISTINCT y.id) <= 2;
или что-то типа того
Другие решения
Похоже, что то, что вы пытаетесь сделать здесь, даже если вы этого не видите, — это получение наибольшего числа на группу. Видеть это статья для дополнительной информации.
Итак, что бы я сделал, это начать с получения последних двух content строки, которые вы создали, вот так:
SELECT c.*
FROM content c
WHERE(
SELECT COUNT(*)
FROM content co
WHERE co.created_by = c.created_by AND co.id >= c.id) <= 2
AND c.created_by = 'me';
В следующей части я расскажу немного медленнее, потому что это довольно сложно. Вы можете присоединиться к вышеуказанному с таблицей like_dislike при условии, что content_id совпадает, так что вы можете просмотреть общее количество лайков для каждого из ваших последних двух сообщений:
SELECT c.*, ld.*
FROM content c
JOIN like_dislike ld ON ld.content_id = c.id
WHERE(
SELECT COUNT(*)
FROM content co
WHERE co.created_by = c.created_by AND co.id >= c.id) <= 2
AND c.created_by = 'me';
Если у вас есть это, вы можете использовать AVG() функция для получения средних лайков и группировка по каждому content_id, например так:
SELECT c.id, AVG(ld.liked) AS averageLikes
FROM content c
JOIN like_dislike ld ON ld.content_id = c.id
WHERE(
SELECT COUNT(*)
FROM content co
WHERE co.created_by = c.created_by AND co.id >= c.id) <= 2
AND c.created_by = 'me'
GROUP BY c.id;
Здесь следует отметить одну важную вещь: здесь будет отображаться только сообщение, с которым связана активность. Другими словами, content_id 4 не появится. Если вам нужно это увидеть, вы можете выполнить LEFT JOIN и проверить, является ли среднее значение нулевым, и заменить его на 0, если оно есть, например:
SELECT c.id, IFNULL(AVG(ld.liked), 0) AS averageLikes
FROM content c
LEFT JOIN like_dislike ld ON ld.content_id = c.id
WHERE(
SELECT COUNT(*)
FROM content co
WHERE co.created_by = c.created_by AND co.id >= c.id) <= 2
AND c.created_by = 'me'
GROUP BY c.id;
А вот твой SQL Fiddle.
ТАКЖЕ ПРИМЕЧАНИЕ like зарезервированное слово в MySQL, поэтому я изменил два столбца в скрипте, чтобы liked а также disliked, Я рекомендую вам сделать то же самое.
Если вы хотите, чтобы эта информация была доступна только одному пользователю за раз, то вам не нужно делать причудливые вещи с собственными объединениями или переменными. Вы можете просто использовать limit в подзапросе:
select avg(like)
from like_dislike ld join
(select c.*
from content c
where c.create_by = 'me'
order by id
limit 2
) c2
on ld.content_id = c2.id;