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

Question

[Алексей Скляров]

На Хабре есть хороший перевод статьи, по которой рекомендуется сортировать посты по по нижней границе доверительного интервала Вильсона. В ней приводится формула и реализация 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-запроса из статьи и где-то эти два значения (кол-во положительных и отрицательных оценок) хранить.

Answer 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;