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

Question

[netrox]

Допустим у нас есть тяжёлая(десятки миллионов) таблица куда записываються заявки:

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

Пример запроса:
start_date: 01-02-2023, end_date: 03-02-2023

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 среди лучших за день.

Comments

[Akina]

у нас есть тяжёлая таблица

Что в данном конкретном случае "тяжёлая"? ну сомневаюсь я, что речь идёт о сотнях миллионов записей...

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

Если это частая и критичная по времени задача, то есть смысл подумать о предрасчёте. Хотя данные выглядят нормализованными, и для озвученной задачи есть разве что смысл формировать для каждого юзера периоды непрерывного присутствия, а затем использовать их при первичном отборе юзеров, среди которых и будет выбираться "чемпион" на каждый день заданного периода. Но, как мне кажется, обычной индексации тут за глаза.

[Михаил Р.]

максимально быстро доставать оттуда данные

"Максимально быстро" в цифрах это сколько?

Допустим у нас есть тяжёлая таблица

"Тяжелая" в цифрах это сколько?

Answer 1

[mayton2019]

Я знаю два сильных пути оптимизации в БД.

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" }

И индекс по двум датам.

Answer 2

[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

Answer 3

[Василий Банников]

Десятки миллионов - это не очень тяжёлая таблица, тем более всего три колонки.

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

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

Данные отсортировать по дате.