Интеллектуальная MySQL GROUP BY для потоков активности

Я создаю поток активности для нашего сайта и добился определенного успеха с чем-то, что работает очень хорошо.

Он работает от двух таблиц:

поток:

  • id - уникальный идентификатор элемента потока
  • user_id - идентификатор пользователя, создавшего элемент потока
  • object_type - Тип объекта (в настоящее время "продавец" или "продукт" )
  • object_id - Внутренний идентификатор объекта (в настоящее время либо идентификатор продавца, либо идентификатор продукта)
  • action_name - действие, предпринятое против объекта (в настоящее время "покупка" или "сердце" )
  • stream_date - отметка времени, в которой было создано действие.
  • hidden - логическое значение, если пользователь решил скрыть элемент.

следует

  • id - уникальный идентификатор Follow ID
  • user_id - идентификатор пользователя, инициирующего действие "Follow".
  • following_user - идентификатор пользователя.
  • followed - отметка времени, в которой выполнялось следующее действие.

В настоящее время я использую следующий запрос для вывода контента из базы данных:

Query:

SELECT stream.*,
   COUNT(stream.id) AS rows_in_group,
   GROUP_CONCAT(stream.id) AS in_collection
FROM stream
INNER JOIN follows ON stream.user_id = follows.following_user
WHERE follows.user_id = '1'
  AND stream.hidden = '0'
GROUP BY stream.user_id,
     stream.action_name,
     stream.object_type,
     date(stream.stream_date)
ORDER BY stream.stream_date DESC;

Этот запрос работает очень хорошо, и, используя небольшой PHP для анализа данных, возвращаемых MySQL, мы можем создать хороший поток активности с действиями одного и того же типа одним и тем же пользователем, сгруппированным вместе, если время между действиями isn ' t слишком велика (см. пример ниже).

Current Stream Output Example

Мой вопрос: как мне сделать это умнее? В настоящее время группа группируется по одной оси, "пользовательская" активность, когда в определенный период времени несколько пользователей задают определенный элемент, который MySQL знает, чтобы сгруппировать их.

Как я могу сделать это еще более умнее и группировать по другой оси, например, "object_id", поэтому, если в последовательности нескольких действий для одного и того же объекта эти элементы сгруппированы, но поддерживайте логику группировки, которую мы в настоящее время имеем для группировки действий/объектов пользователем. И реализовать это без дублирования данных?

Пример нескольких объектов, появляющихся в последовательности:

Multiple Objects Appearing in Sequence

Я понимаю, что решения таких проблем могут быть очень сложными, очень быстро, но мне интересно, есть ли элегантное и довольно простое решение для этого (надеюсь) в MySQL.

Ответы

Ответ 1

Мое впечатление, что вам нужно группировать себя, как и вы, а также после этой группировки по действию.

Мне кажется, что вам нужен такой подзапрос:

SELECT *, -- or whatever columns
   SUM(actions_in_group) AS total_rows_in_group,
   GROUP_CONCAT(in_collection) AS complete_collection
   FROM
     ( SELECT stream.*, -- or whatever columns
          COUNT(stream.id) AS actions_in_user_group,
          GROUP_CONCAT(stream.id) AS actions_in_user_collection
       FROM stream
       INNER JOIN follows
       ON stream.user_id = follows.following_user
       WHERE follows.user_id = '1'
         AND stream.hidden = '0'
       GROUP BY stream.user_id,
            date(stream.stream_date)
     )
   GROUP BY object_id,
            date(stream.stream_date)
   ORDER BY stream.stream_date DESC;

Ваш первоначальный запрос (теперь внутренний) - пользователем, но тогда группы пользователей перегруппируются с помощью идентичных действий, то есть идентичные продукты, купленные или проданные от одного продавца, будут собраны вместе.

Ответ 2

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

Некоторые из предметов агрегированы (Джек Спрат сердечно обслуживает семь продавцов), а другие перечислены (лорд Нельсон зафрахтовал Золотой Хинд). Вероятно, вам понадобится UNION в вашем запросе, который объединяет эти два класса элементов из двух отдельных подзапросов.

Вы используете довольно грубую функцию временной близости, чтобы группировать ваши предметы... DATE(). Возможно, вы захотите использовать более сложную и настраиваемую схему... например, возможно,

  GROUP BY TIMESTAMPDIFF(HOUR,CURRENT_TIME(),stream_date) DIV hourchunk

Это позволит вам группировать вещи по возрастным кускам. Например, если вы используете 48 для hourchunk, вы будете группировать материал, который 0-48 часов назад вместе. Когда вы добавляете трафик и действие в свою систему, вы можете уменьшить значение hourchunk.

Ответ 3

В Fashiolista мы открыли наш подход к построению систем подачи. https://github.com/tschellenbach/Feedly В настоящее время это самая большая библиотека с открытым исходным кодом, предназначенная для решения этой проблемы. (но написанный на Python)

Та же самая команда, которая создала Feedly, также предлагает размещенный API, который справляется с сложностью для вас. Посмотрите getstream.io Есть клиенты для PHP, Node, Ruby и Python. https://github.com/tbarbugli/stream-php Он также предлагает поддержку настраиваемых агрегатов, которые вы ищете.

Кроме того, посмотрите на это сообщение с высокой степенью масштабируемости, мы объясняем некоторые из принимаемых проектных решений: http://highscalability.com/blog/2013/10/28/design-decisions-for-scaling-your-high-traffic-feeds.html

Этот учебник поможет вам настроить систему, такую ​​как Pinterest, используя Redis. С этим легко начать.

Чтобы узнать больше о дизайне корма, я настоятельно рекомендую прочитать некоторые из статей, которые мы основали:

Ответ 4

Мы решили подобную проблему, используя подход "материализованный вид" - мы используем выделенную таблицу, которая обновляется при вставке/обновлении/удалении события. Все действия пользователя регистрируются в этой таблице и предварительно подготовлены для простого выбора и рендеринга.

Преимущество - простой и быстрый выбор, недостаток немного медленнее вставки/обновления/удаления, так как таблица журналов также должна быть обновлена.

Если эта система хорошо спроектирована - это решение.

Это довольно просто реализовать, если вы используете ORM с событиями post insert/update/delete (например, Doctrine)