Как правильно реализовать ТОП дня, недели, месяца?

Question

[Narts]

Всем привет! У пользователей есть рейтинг, который меняется (как плюсуется, так и минусуется). Все изменения в рейтинге логгируются в таблице rating_log (причина изменения рейтинга; добавление или вычет рейтинга; количество добавленного или вычтенного рейтинга).

Появилась необходимость сделать топ по дню, неделе и месяцу, и не совсем понятно, как лучше это сделать.
Вижу два варианта:
1. Получаем записи из rating_log, суммируем те, что были сегодня/на неделе/в течении месяца и выводим 50 наибольших. Минусы - "тяжелые" запросы с суммированием. К тому же, записей в rating_log будет много
2. Создать отдельные таблицу top_daily, в которой будет день, месяц, год, id пользователя, рейтинг за день. И при каждом обновлении рейтинга, помимо лога в rating_log, так же обновлять и top_daily. Аналогично для топа недели и месяца. Минусы - 3 доп таблицы + 3 запроса при обновлении рейтинга (а рейтинг меняется чуть ли не от каждого действия).

Может кто сталкивался с проблемой, как Вы реализовывали?

Comments

[nokimaro]

Почитайте обсуждения Сортировка страниц по количеству просмотров за день, неделю, месяц?
Мой вариант использовать агрегированные по (день / пользователь / счётчик) таблицы.

Ваш вариант 1 тоже приемлем. Топ можно строить фоновым процессом и обновлять не в реальном времени, а скажем раз в минуту или раз в час и результаты кешировать для отображения всем пользователям.

Answer 1

[d'Ivan]

У MariaDB есть движок ColumnStore как раз хороший для агрегаций.
Ну а так, воспользоваться оконными функциями.

Answer 2

[batyrmastyr]

1. Вместо top_[daily|weekly|monthly] можно завести общую таблицу top со столбцом для признака, что это дневной/недельный/месячный рейтинг.
2. Обновлять данные в ней раз в день/час.
3. В транзакции удаляете старые записи и делаете Insert into (select ..., ‘daily’ as type
INION select ..., ‘weekly’ as type
INION select ..., ‘montly’ as type
).
При желании можно суточный рейтинг обновлять раз в час, а недельный и месячный раз в сутки.

Когда rating_log слишком распухнет, можно будет:
1) секционировать её по времени. Либо встроенными средствами БД, либо вручную перенося старые записи в таблицу-архив.
2) Воспользоваться более специализированными решениями: расширение timescaleDb для PostgreSQL или колоночную базу типа ClickHouse. Но оба варианта требуют тестирования перед использованием: например, оба не очень любят работу со строками, а Кликхаус предпочитает, чтобы данные вставляли пачками, а не построчно.