Как подсчитать активность пользователя за промежуток времени по данным из БД?
Здравствуйте!
На моем сайте имеется система уровней для пользователей, который они могут повышать путем активности: публикация сообщений, тем, оценки и т.п.
Я хочу подсчитать самых активных пользователей за неделю - те, чей опыт максимально вырос за последние 7 дней.
1 раз в день для каждого пользователя заносятся данные о его опыте в таблицу user_exp которая имеет вид:
user_id | data | exp
...
1 | 03-03-2016| 1238
1 | 02-03-2016| 1218
2 | 03-03-2016| 552
2 | 02-03-2016| 523
...
т.е. в таблице есть информация о опыте пользователя за каждый день
Вопрос: как мне посчитать у какого пользователя на сколько прирос опыт за промежуток времени (неделя) и получить ID пользователей с максимальным приростом?
Почему я записываю данные о опыте за каждый день, а не 2 раза в неделю (в начале и в конце, что бы сравнить)? Потому что я хочу что бы пользователь мог зайти на страницу и посмотреть текущий ТОП: кто сколько набрал за последние сутки, за 2ое суток, и т.п.
p.s. так же я выслушал бы предложения относительно структуры таблицы, может быть я делаю что-то не так и можно проще...
select curr.user_id, (curr.exp - coalesce(lastr.exp, 0)) as diff
from user_exp curr
left join user_exp lastr ON curr.user_id = lastr.user_id and lastr.date = CURRENT_DATE - interval '1week'
where curr.date = CURRENT_DATE
order by diff desc
По индексу по user_id & date будет внятно работать джойн, индекс по date для начальной таблицы. Но сортировка всех юзеров по вычисляемому полю - это может быть болезненно.
Спасибо! Но почему-то не получается. В результате выполнения запроса имею таблицу: user_id | diff отсортированную по diff, но в diff не разница между минимальным и максимальным кол-вом опыта, а максимальное количество опыта (т.е. сколько опыта у пользователя сегодня, а не сколько он набрал)
Я тут вот что еще придумал. Может мне сделать так: как начальную точку отсчета записать для пользователя его опыт на сегодня. А завтра уже посмотреть сколько у него было опыта и сколько есть, посчитать разницу и результат внести в мою табличку. Получится что в таблице будет не общее количество опыта пользователя на какую-то дату, а сколько опыта он набрал с момента предыдущей даты. Тогда наверно будет как-то проще считать
Вообщем у меня не работают CURRENT_DATE, если выставить даты в sql запросе руками - то все срабатывает. Но появилась другая проблема: я вывел данные не за неделю, а с 1 по 2 марта, в результате на 1ом месте человек который не заходил на сайт 1го марта и данных за эту дату о нем нет. В результате получается что сколько опыта он набрал с 1 по 2 марта = его текущему опыту. Т.е. мне надо либо для каждого пользователя каждый день ОБЯЗАТЕЛЬНО заносить данные, либо менять таблицу. Буду менять таблицу, т.к. каждый день считать эксп для всех тупо.
Да, я исходил из предположения, что в таблицу сохраняются данные каждый день для всех, у кого exp != 0. Если какого-то дня не было - значит пользователь на тот момент не был зарегистрирован вообще или имел 0 exp.
Для таблицы такого плана, да если ещё и партицировать - и сотни миллиардов строк фигня. Зато считать проще и быстрее.
CURRENT_DATE - странно, но да, мог чего-нибудь напутать.
Melkij: "партицировать" - это слишком сложно для меня. Сейчас я пробую добавить в таблицу новый столбик - diff, в котором будет разница текущего опыта и вчерашнего. Так думаю будет проще считать. Не уверен что это самый хороший вариант, но пока в голову больше ничего не лезет
