ФорумРазработкаБазы данных → Статистика по периодам

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

  • vasa_c

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

    Spritz 23 октября 2015 г. 0: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

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

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

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

  • vasa_c

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

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

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

  • kostyl

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

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

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

  • artoodetoo

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

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

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

    Спустя 180 сек.

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

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

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

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

    Spritz 23 октября 2015 г. 0: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

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

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

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

  • Ivan.

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

    Spritz 24 октября 2015 г. 17: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:::

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