Как правильно спроектировать таблицы для рассчета рейтинга поста по нижней границе доверительного интервала Вильсона?

На Хабре есть хороший перевод статьи, по которой рекомендуется сортировать посты по по нижней границе доверительного интервала Вильсона. В ней приводится формула и реализация SQL запроса:
SQL-запрос
SELECT 
	widget_id, 
	((positive + 1.9208) / (positive + negative) - 
		1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / 
 		(positive + negative)) / (1 + 3.8416 / (positive + negative)) 
 	AS ci_lower_bound 
	FROM widgets WHERE positive + negative > 0 
	ORDER BY ci_lower_bound DESC;

Есть еще одна статья на англ. языке, в которой показывается пошаговое создание таблиц и триггеров для БД.

На данный момент у меня существует две модели Post и Reviews. В таблице Reviews есть столбец mood, в котором могут храниться только два значения negative и positive, и столбец rating -оценка данного поста, которую пользователь оставляет к посту.
Упрощенный вариант таблицы reviews
+----+---------+--------+----------+-------------+------------+
| id | post_id | rating | mood     | message     | created_at |
+----+---------+--------+----------+-------------+------------+
| 1  | 1       | 3      | positive | Some string | 26/01/2021 |
+----+---------+--------+----------+-------------+------------+
| 2  | 2       | 2      | negative | Some string | 26/01/2021 |
+----+---------+--------+----------+-------------+------------+
| 3  | 1       | 1      | negative | Some string | 26/01/2021 |
+----+---------+--------+----------+-------------+------------+


Есть ли смысл для реализации SQL запроса выше, создавать таблицу оценок для каждого поста и вести учет положительных и отрицательных оценок там?

Или можно как-то обойтись стандартными методами eloquent, чтобы через orderByRaw() получить вычисляемую колонку и по ней производить сортировку? Откровенно говоря, довольно посредственно разбираюсь в SQL, и переписать тот запрос у меня не выходит, так как вместо positive и negative у меня должно быть $post->reviews->positive()->count(), либо
SELECT COUNT(*) FROM reviews WHERE mood = 'positive'
, но это я должен каким-то образом вычислить, перед выполнением SQL-запроса из статьи и где-то эти два значения (кол-во положительных и отрицательных оценок) хранить.
  • Вопрос задан
  • 36 просмотров
Пригласить эксперта
Ответы на вопрос 1
@vascodogama
Думаю можно сделать вьюху, с чем то подобным(не знаю какая у вас субд)

Select 
   post_id, 
   sum(case when mood == 'positive' then 1 else 0 end) as count_positive,
   sum(case when mood == 'negative' then 1 else 0 end) as count_ negative
 FROM Reviews
 GROUP BY post_id

ну а потом как в статье

SELECT 
	widget_id, 
	((positive + 1.9208) / (positive + negative) - 
		1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / 
 		(positive + negative)) / (1 + 3.8416 / (positive + negative)) 
 	AS ci_lower_bound 
	FROM widgets WHERE positive + negative > 0 
	ORDER BY ci_lower_bound DESC;
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы