Дизайн базы данных mysql - регистрация показателей использования видео

Я недавно сдал это существующее приложение. Существует таблица базы данных mysql, которая используется для отслеживания времени до того момента, когда пользователь последний раз смотрел видео.

Упрощенная голая версия таблицы

id -> Primary_key
user_id
video_id
last_watched_time
last_viewed_time (DateTime)

В столбце last_watched_time они хранят время в секундах, с которого следует снова начать воспроизведение видео.

Это делается так: когда пользователь начинает воспроизведение видео, в таблицу вставляется новая запись (если запись для этого пользователя и видео не существует), а затем, пока пользователь смотрит видео - каждые 20 секунд - для этой записи выполняется обновление, чтобы зафиксировать время последнего просмотра. Это так, если пользователь нажимает на следующее видео, не нажимая кнопку остановки, - мы знаем время, в котором он остановился.

Таким образом, если пользователь смотрит новое видео в течение 1 минуты и закрывает браузер, столбец last_watched_time обновляется 3 раза 20,40,60. Последнее сохраненное значение - 60. Итак, когда он возвращается - видео начинает проигрываться с отметки 1 мин.


id  |  user_id  |  video_id  |  last_watched_time  |  last_viewed_time
-------------------------------------------------------------------------
1   |     10    |     6      |       60            | 2018-10-01 10:10:10

Итак, это существующая таблица с данными в реальном времени.

Теперь они хотят начать измерять подробные показатели использования пользователями, например - За последние 7 дней, сколько часов видео смотрел пользователь, с разбивкой по дням. - За последние 6 часов, сколько часов видео смотрел пользователь, с разбивкой по часам.

Итак, моей первой мыслью было сделать следующее - добавить в эту таблицу еще один столбец с именем view_time - менять каждые 20 секунд обновления для вставки операторов

Таким образом, для того же сценария, приведенного выше, данные в таблице будут

id  |  user_id  |  video_id  |  last_watched_time  |  view_time  |  last_viewed_time
-----------------------------------------------------------------------------------------
1   |     10    |     6      |         0           |      0      |   2018-10-01 13:10:10
2   |     10    |     6      |        20           |     20      |   2018-10-01 13:10:30
3   |     10    |     6      |        40           |     20      |   2018-10-01 13:10:50
4   |     10    |     6      |        60           |     20      |   2018-10-01 13:11:10

Теперь, если тот же пользователь возвращается через 2 часа, пересылает видео на 10 минут и смотрит 25 секунд.

5   |     10    |     6      |       660           |    600      |   2018-10-01 15:11:10
6   |     10    |     6      |       680           |     20      |   2018-10-01 15:11:30

При этом я все еще отслеживаю last_watched_time, и если им нужны ежедневные или ежечасные показатели, я могу сгруппировать по дням или часам и суммировать view_time, чтобы узнать, сколько минут пользователь смотрел в тот день или час.

Очевидная проблема, которую я вижу с этим подходом, заключается в том, что если пользователь смотрит видео в течение 4 часов - переходя на 20-секундные операторы вставки для захвата last_watched_time - в эту таблицу будет вставлено примерно 720 строк. А если мы говорим о 100 пользователях - число просто умножается.

Правильный ли такой подход. Как мне это сделать?

0
0
315
1

Ответы 1

Работайте на INSERT вместо SELECT. Таким образом, нужно хранить гораздо меньше данных, и SELECTs работают намного быстрее.

Если вам нужно только общее время просмотра, сохраните только это. То есть, когда поступает новая запись, увеличивайте существующую запись если, которая была 20 секунд назад.

Поскольку вам нужно ежедневное и ежечасное время просмотра, каждый раз, когда наступает новый час, не добавляйте к существующей записи, а начинайте новую запись. В крайнем случае это сократит количество строк в 180 раз (60 минут с 20-секундными интервалами). Для «серфинга» усадки может и не быть.

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

Gublooo 01.11.2018 11:40

@Gublooo - добавил в свой ответ.

Rick James 01.11.2018 19:04

Другие вопросы по теме