Входные данные хранятся в MySQL, Как хранить и вычислять данные «на лету» при обновлении одной из таблиц?

Вопрос заключается в следующем:
1) Входные данные хранятся в MySQL, в таблице "Results":
Results [result_id, post_id, user_id, points_bet, points_got]
Posts [post_id, category_id, text]
Categories [category_id, category_name]
Users[user_id, overall_ratio]

Значение overall_ratio для каждого пользователя рассчитывается
след. образом:
overall_ratio  = SELECT sum(points_got) / sum(points_bet) FROM Results WHERE (user_id = user->id)


2) Нам требуется рассчитать это же отношение для каждого пользователя в каждой категории:
category_id_ratio  = SELECT sum(points_got) / sum(points_bet) FROM Results WHERE (user_id = user->id) AND (category_id = category->id)


Основное значение overall_ratio, отношение сумм по всем результатам, мы храним в поле overall_ratio в таблице Users. И каждый раз при добавлении результата в таблицу Results мы пересчитываем это значение с учетом нового, добавленного, без учета категории, то есть по всем результатам пользователя.
Хорошо, мы можем посчитать это значение, это всего 1 запрос на каждый результат. (Добавляем 100 результатов, пересчитываем overall_ratio для каждого пользователя). С этим еще можно смирится, как нам кажется.

А что делать если просчитать нужно для каждого пользователя и еще в каждой категории?

Возможные проблемы, которые, возможно, возникнут в будущем:

Результатов будет большое количество, у каждого пользователя в каждой категории. И нам нужно пересчитывать данные каждый раз заново, что бы например, отобразить пользователей со смежным ratio в каждой категории.
sum(points_got) / sum(points_bet).
Например, в категории футбол, пользователь имеет ratio 1.2.
Нужно отобразить пользователей с таким же или соседним ratio (+-) из этой же категории. Но мы не храним эти данные.
Назовем эти данные промежуточными.

Вопрос: Как грамотно вычислять и хранить эти самые промежуточные данные "на лету" при добавлении данных в таблицу результатов?
То есть ration для каждой категории и для каждого пользователя.
Если просто отправлять запрос в БД и получать результат, но на большое количестве результатов, категорий и пользователей производительность может быть низкой.
Как оптимизировать данную задачу?
Следует ли применять key value storages для решения таких задач, такие как noSQL БД или cache storages?

Большое спасибо за внимание и комментарии.
  • Вопрос задан
  • 2433 просмотра
Пригласить эксперта
Ответы на вопрос 1
DmitriyEntelis
@DmitriyEntelis
Думаю за деньги
0)
Основной вопрос - а сколько у вас значений в Query?

1)
А зачем собирать результаты по всей таблице?
Когда у Вас добавляется запись вы знаете user_id, знаете категорию соответственно можно пересчитать целиком только конкретную строчку

2)
Исходя из Вашей формулы - нет никакой необходимости честно пересчитывать строчку, можно сделать то то вроде
update query set `sum` = `sum`+ (значение points_got/points_bet рассчитанное ) where user_id = ... and category_id = ...


Это можно сделать как тригером автоматически, так и в логике самого приложения.
В принципе если Query большой, можно вынести его в какой нибудь redis.

UPD
Перечитал вопрос. Вы не хотите хранить именно не агрегированные данные, т.е избавиться от Results ?
Если там счет не идет на сотни миллионов записей - я бы все таки хранил, потому что если что - пересчитать значения не получится иначе.
Ответ написан
Комментировать
Ваш ответ на вопрос

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

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