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

Question

[Anton Bormotov]

Вопрос заключается в следующем:
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?

Большое спасибо за внимание и комментарии.

Comments

[Anton Bormotov]

Дмитрий, я попробовал объяснить ситуацию более детально, если у Вас будет время, пожалуйста прочтите. Если у вас также есть ссылки касаемо выноса запроса в redis, был бы очень признателен. Спасибо.

Answer 1

[Дмитрий Энтелис]

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 ?
Если там счет не идет на сотни миллионов записей - я бы все таки хранил, потому что если что - пересчитать значения не получится иначе.