netrox
@netrox

Как оптимальнее всего организовать хранение тяжёлых данных и чтобы потом максимально быстро доставать оттуда данные для отчётов?

Допустим у нас есть тяжёлая(десятки миллионов) таблица куда записываються заявки:
652508678a865804313245.png
Даны две даты start_date и end_date. В этом диапазоне нужно найти пользователей с наивысшим баллом (score) по дням. Также пользователь должен присутствовать в выборке лишь в том случае, если он подавал заявку каждый день в течении запрошенного периода. Как оптимальнее всего организовать хранения таких данных и чтобы потом максимально быстро доставать оттуда данные для отчётов?

Пример запроса:
start_date: 01-02-2023, end_date: 03-02-2023
65250b20bdd40011092365.png
1 февраля пользователь с ID 3 имел наивысший балл, но причина по которой он не появился в отчете, заключается в том, что в данный период (с 1 по 3 февраля включительно) он не каждый день подавал заявку (пропустил 3 февраля). Есть только два пользователя (пользователи 1 и 2), которые подавали заявки каждый день в течение данного периода. Таким образом, 1 февраля User ID 1 стал лучшим пользователем, обогнав User ID 2 на 40 баллов, набравшего 15 баллов. 2 февраля по аналогичной логике победителем стал пользователь с ID 2 (он обошёл пользователя с ID 1). 3 февраля, используя аналогичную логику, пользователи с идентификаторами 1 и 2 набрали равное количество баллов, и мы случайным образом выбрали пользователя с идентификатором 1 среди лучших за день.
  • Вопрос задан
  • 159 просмотров
Пригласить эксперта
Ответы на вопрос 3
mayton2019
@mayton2019
Bigdata Engineer
Я знаю два сильных пути оптимизации в БД.

1) Минимизация IOps. Тоесть уменшить число дисковых чтений. Для таблиц это достигается через
partitions by date. Вычисляешь экспериментально оптимальный размер partition (например 1 неделя).
И твои запросы по диапазону должны попадать в 1-2 partitions. Это исключает full-table-scan.
Ну и индекс попробуй построить по предикатам фильтров.

2) Материализация ответов. Для данных которые уже не будут изменяться ты строишь где-то такую
табличку (матрицу по сути) где хранишь уже заранее расчитанные данные. Эта технология по разному
может называться. Materialized View. OLAP cubes. Витрины данных. Но суть одна.

start_date    end_date     result 
01-02-2023    03-02-2023   { "1":"65", "2":"45" }


И индекс по двум датам.
Ответ написан
Комментировать
@alexalexes
Структура таблицы нормальна, только добавьте индекс на поле date_submitted и индекс на поле score и будет летать.
Запрос для получения рейтинга на каждый день для всех пользователей будет примерно такой:
with date_list as (/*Любым способом получаете непрерывный список дат интересуемого диапазона */)
select A.date, u.user_id, nvl(A.max_score, 0) max_score /* nvl зависит от СУБД */
from (select dl.date, u.user_id, tb_max_score.max_score,
          rank() over (partition by dl.date order by random() /*реализация функции random зависит от СУБД*/ ) rnk
from (select us.date_submitted, max(us.scope) max_score
           from user_score us) tb_max_score  -- выясняем макс-ные баллы
left join date_list dl on dl.date = tb_max_score.date_submitted
left join user_score us on dl.date = us.date_submitted  -- выясняем, у кого макс-ные баллы
                                  and us.score = tb_max_score.max_score
) A
cross join user u u.user_id = A.user_id -- прицепляем тех, кто возможно не участвует в рейтинге
where A.rnk = 1
Ответ написан
Комментировать
vabka
@vabka
Токсичный шарпист
Десятки миллионов - это не очень тяжёлая таблица, тем более всего три колонки.

Такое количество данных легко умещается в оперативной памяти.

Если нужно только 1 раз сохранить и потом выполнять один такой запрос, то можно попробовать parquet и какой-нибудь удобный для тебя ЯП, чтобы выборку делать.
Даже СУБД в таком случае не понадобится.

Данные отсортировать по дате.
Ответ написан
Комментировать
Ваш ответ на вопрос

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

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