@EchoStan

Как бы вы реализовали вычисление списка «любимых эмодзи» пользователя?

Здравствуйте, друзья.
В инпуте чата полезно иметь строчку с любимыми эмодзи пользователя. Вроде такой:
5f5df5ad07aa1297560246.png

Как сейчас это реализовано у меня
0. Для хранения любимых эмодзи пользователя используем отдельную небольшую таблицу:
CREATE TABLE top_used_emojis (
    user_id BIGINT PRIMARY KEY,
    emojis TEXT[] NOT NULL
);


1. Пользователь отправляет новое сообщение в чат.

2. Сообщение сохраняется в таблицу сообщений. Пусть будет chat_messages.
CREATE TABLE chat_messages (
    user_id BIGINT NOT NULL,
    text TEXT,
    ...
)


К таблице добавлен простейший индекс, на бумаге он обещал облегчать поиск по тексту.
CREATE INDEX chat_messages_text_index ON chat_messages (text);


3. В коде приложения выполняется простой поиск совпадений для того, чтобы определить: есть ли вообще в новом сообщении эмодзи.

4. Если эмодзи нет, то ничего не трогаем.

5. Если эмодзи найдены, из приложения вызывается pgplsql-функция, принимающая на вход user_id, вычисляющая топ-10 наиболее часто встречающихся эмодзи в текстах сообщений пользователя и сохраняющая результат в полеemojis таблицы top_used_emojis по ключу user_id.

В своей работе функция использует вспомогательную таблицу, где хранятся известные нам эмодзи
CREATE TABLE emojis (
    emoji TEXT PRIMARY KEY /* Здесь лежит сама эмодзи */
);


А вот и тело функции
CREATE OR REPLACE FUNCTION updateTopUsedEmojis (BIGINT) RETURNS TEXT[] AS '
    DECLARE
        _user_id ALIAS FOR $1;
        query_result TEXT[];

    BEGIN

            WITH last_top_used AS (SELECT emoji, count(*)::INT AS count
                                   FROM chat_messages cm
                                            JOIN emojis e
                                                ON (cm.text LIKE ''%'' || e.emoji || ''%'')
                                   WHERE cm.user_id = _user_id
                                   GROUP BY e.emoji
                                   ORDER BY count DESC
                                   LIMIT 10)
            INSERT INTO top_used_emojis (user_id, emojis)
            VALUES ( _user_id,
                     (SELECT array_agg(emoji) FROM last_top_used)::TEXT[]
            )
            ON CONFLICT (user_id)
            DO UPDATE
            SET emojis = (SELECT array_agg(emoji) FROM last_top_used)::TEXT[]
            RETURNING emojis::TEXT[]
        INTO query_result;

        RETURN query_result;

    END;
'LANGUAGE plpgsql;


6. Всё. На мелких объёмах это работает.

Необходимые уточнения
  • Начиная с какого-то момента, мы, понятно, будем выполнять эту процедуру не чаще, чем 1 раз в N времени. При добавлении каждого сообщения - это слишком.
  • "Любимые" и "наиболее часто встречающиеся" - не совсем одно и то же, но нам норм.
  • У меня пока некоторые трудности с EXPLAIN - seqscan, вроде, научился распознавать, а косты пока не косты.
  • Предполагаемое количество пользователей - 1млн, предполагаемое среднее количество сообщений у каждого пользователя - 100


Места, которые не дают мне покоя
  • Конкатенация в предложении FROM:
    FROM chat_messages cm
                     JOIN emojis e
                            ON (cm.text LIKE '%' || e.emoji || '%')


    Движок DML (или кто там внутре сидит) - он же как-то кэширует результаты этой самой конкатенации?
    Может, стоит заранее заполнить таблицу emojis строчками '%...%'?

  • Оператор LIKE там же. Есть ли более производительные решения?

  • Вопрос задан
  • 46 просмотров
Решения вопроса 1
bingo347
@bingo347
Бородатый программер
Я бы сделал в таблице top_used_emojis поле хранящее количество использования каждого пересечения user с emoji и повесил бы на это поле DESC индекс а в Primary пихал бы поля user_id и emoji вместе.
На таблицу chat_messages повесил бы тригеры для пересчета количества в top_used_emojis
Ну и выводил бы это простым ORDER BY по индексу с лимитом
Ответ написан
Пригласить эксперта
Ответы на вопрос 3
@antonwx
Дай пользователю самому выбрать любимые эмодзи. Задолбали ваши алгоритмы, знающие лучше меня что мне надо.
Ответ написан
@vdem
А не лучше ли хранить их в одном поле в JSON? Например, сделать два поля - mostUsed (наиболее часто использующиеся) и recentlyUsed (последние использованные, чтобы на основании этих данных новые использующиеся эмодзи могли попасть в mostUsed). ИМХО для такой функциональности не стоит заводить отдельную таблицу.

P.S. Ну и конечно хранить количество (а лучше - частоту встречаемости) случаев использования, чтобы либо добавлять в mostUsed, либо убирать оттуда в пользу эмодзи из recentlyUsed.
Ответ написан
BojackHorseman
@BojackHorseman
...в творческом отпуске...
я бы делал 10 популярных по всем пользователям.
и 10 последних по текущему.
а продукта, который хочет пересчитывать на каждый чих цифры, послал бы.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы