Обработка Emojis в SQLite

Question

Я надеюсь определить, какие эмодзи используются чаще всего в текстовом разговоре с использованием SQL Lite. Я использую DB Browser, и смайлики отображаются так же, как в iMessage (см. Рисунок ниже), но я не знаю, как их посчитать.

Я думал, что если есть способ проверить и проверить, не является ли символ буквой / цифрой / пунктуацией, то я мог бы посчитать частоту всех символов, которые не соответствуют списку предварительных условий. Тем не менее, я незнаком с командами SQLite и как я могу это сделать.

Есть ли лучший способ сделать это? Дайте мне знать, если вам нужно больше контекста, чтобы ответить на этот вопрос.

Answer 1

Создайте таблицу смайликов, содержащую строки смайликов в текстовых полях, затем попробуйте это:

select emojis.text, sum(length(replace(message.text, emojis.text, emojis.text || '_')) - length( message.text))
from message
inner join emojis on message.text like '%' || emojis.text || '%'
group by emojis.text

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

Answer 2

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

Однако вы планируете сделать это только один раз, и перекомпиляция SQLite может быть немного излишней.

Вместо этого вы можете скопировать text столбец в простой текстовый файл и выполните следующую команду:

sed 's/\(.\)/\1\n/g' temp.txt | sed 's/[[:alnum:].-]//g' | sort -r | uniq -c

Это может превратить следующее:

Hello! Are you stuck? I saw in the park!!!!! - all lies. Easy as 123!

в:

  1 
 11 
  3 
  6 
  1 ?
  7 !
 17
 50

Который, надеюсь, будет достаточно близко, чтобы вы достигли своей цели. Последние две записи предназначены для вкладок и пробелов.

sed это команда linux, поэтому, если вы работаете в Windows, вы можете получить версию для Windows здесь: https://github.com/mbuilov/sed-windows

Answer 3

Возможно, это не самый эффективный пример, но вы могли бы использовать функцию group_concat для объединения текста в одну строку, а затем использовать рекурсивный cte, чтобы превратить это в единую таблицу символов. Вы можете отфильтровать все "нормальные" символы acii, используя шестнадцатеричную функцию больше 7F( см. Таблицу asii), вам может потребоваться поиграть с этим в зависимости от того, какие у вас смайлы и есть ли у вас там много нелатинского юникода. Тогда это простой случай подсчета, если вы просто хотите получить общее количество или группу со счетом, если вы хотите знать, каково количество отдельных эмодзи.

WITH RECURSIVE theChars (
    position
    ,charValue
    )
AS (
    SELECT 2
        ,substr((
                SELECT group_concat(TEXT, '')
                FROM message
                ), 1, 1)

    UNION ALL

    SELECT position + 1
        ,substr((SELECT group_concat(TEXT, '') FROM message)
            ,position
            ,1)
    FROM theChars LIMIT length((SELECT group_concat(TEXT, '') FROM message))
    )
SELECT charValue,count(*)
FROM theChars
WHERE hex(charValue) > '7F'
group by charValue;