Статистика по периодам

vasa_c

Сообщения: 3131 Репутация: N Группа: в ухо
23 октября 2015 г. 10:05
Допустим есть MySQL, а в ней таблица. А в таблице логи неких событий, пусть это события прихода бабосов пользователям.

ID USER_ID AMOUNT TIME 10 1234 100 2015-10-23 12:00:00

Юзер #1234 разжился 100 условными единицами в полдень.

Таблица средненьких размеров. Например, пара миллионов записей.

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

Общая сумма, которую юзер с заданным ID получил с 15 марта по 25 июля

Построить график для пользователя за последний год, усредняя по дням или по неделям.

Ну и чтобы эти выборки проходили за приемлимое время.

Собственно, вопросы

Может есть какая сторонняя приблуда для подобных целей?

Либо может можно сделать волшебный запрос с группировками, который бы отработал за нормальное время?
Положение осложняется тем, что выборка по времени + user_id, а обвешивать индексами таблицу в которую часто инсертят, тоже хз насколько умно.

Первое что пришло в голову, вспомогательная таблица:

user_id
type - тип периода (month, week, day)
begin - начало периода
sum - сумма

Нужна сумма с 15 марта по 25 июля - тянем month за апрель, май и июнь, а также нужные week из марта и июля + дни, которые не вошли в эти недели.

Если каких-то периодов нет, то их уже расчитываем выборкой из основной таблицы.

Плюс отдельно хранить ID логов, которые соответствуют началам определённых дней, чтобы не делать индекс на time.

Такое извращение.
Nek

Сообщения: 109 Репутация: N Группа: Адекваты

23 октября 2015 г. 10:22, спустя 16 минут 44 секунды

@vasa_c, мне такое же решение пришло в голову, оно самое очевидное наверное.
Но надо учитывать, что если старые логи могут быть изменены (например, приход 100 у.е. какого-то дня в прошлом может быть отменен/заблочен), то данные из вспомогательной таблички за прошедшие периоды придется как-то обновлять.
vasa_c

Сообщения: 3131 Репутация: N Группа: в ухо

23 октября 2015 г. 10:24, спустя 1 минуту 48 секунд

@Nek, не, меняться не должны.
ну или можно сделать время устаревания для кэша, заодно грохать те данные, которыми давно не пользовались, чтобы не мешали.
kostyl

Сообщения: 5210 Репутация: N Группа: Джедаи

23 октября 2015 г. 10:37, спустя 13 минут 13 секунд

По-моему можно одним запросом, и лучше его резалты кешировать, чем городит что-то, вообще пробовал сам одним запросом? + не понятно почему индекс на тайм не надо делать, ну пиши туда не дату а timestamp если медленно по дате.
Может это поможет Представления (VIEW) в MySQL [habrahabr.ru]

Представления (VIEW) в MySQL

В комментариях Хабра упоминались вопросы по использованию представлений. Данный топик является обзором представлений, появившихся в MySQL версии 5.0.

habrahabr.ru
artoodetoo

Сообщения: 5147 Репутация: N Группа: в ухо

23 октября 2015 г. 10:45, спустя 7 минут 32 секунды

@vasa_c, то что ты придумал с периодами, реально используется кое-где.
а ещё можно не отдельную таблицу, а доп. поля с округленными значениями в той же таблице.

Спустя 180 сек.

kostyl, 23 октября 2015 г. 7:37

не понятно почему индекс на тайм не надо делать

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

ιιlllιlllι унц-унц
artoodetoo

Сообщения: 5147 Репутация: N Группа: в ухо
23 октября 2015 г. 10:59, спустя 14 минут 48 секунд
Общее правило оптимизации такое: надо стараться отбросить как можно больше лишнего как можно раньше. Поэтому надо искать варианты сокращения набора:

SELECT `user_id`, `day`, SUM(`amount`) FROM ( SELECT `user_id`, DATE(`time`) AS `day`, `amount` FROM `huge` WHERE `time` BETWEEN :t1 AND :t2 ) AS `small` GROUP BY `user_id`, `day`

Подзапрос использует индекс по time. Главное избежать соблазна округлять в WHERE!
Внешний запрос работает с временной таблицей, которая скорее всего помещается в памяти.

Партицирование также помогает быстро работать с актуальным периодом.
ιιlllιlllι унц-унц
vasa_c

Сообщения: 3131 Репутация: N Группа: в ухо

23 октября 2015 г. 11:39, спустя 39 минут 31 секунду

@artoodetoo, спасибо, попробую запросик.
Ivan.

Сообщения: 495 Репутация: N Группа: Адекваты

25 октября 2015 г. 3:42, спустя 1 день 16 часов 3 минуты

sphinx - у него в движке есть кеш такой залупы, можно сделать индексирование ночью, чтобы не грузить сервер
годовалые выборки наура можно и кешировать в отдельной таблице:
ID USER_ID MONTH YEAR AMOUNT

а отсюда же выборка с 15 мая по дохуя февраля будет делать из суммы с 15 по конец мая + с июня по январь, и там дохуя февраль

Спустя 233 сек.

REDIS: сейвим транзакции ключами вида transaction:user_id:day:month:year:hour:minute:sec
сумму можно будет запрашивать по маске transaction:user_id::jun:2015:::

Пожалуйста, авторизуйтесь, чтобы написать комментарий!

Статистика по периодам

Войти