Блог

У меня есть база данных, в которой хранятся данные Twitter:

         Create Table tweet(
            ID BIGINT UNIQUE,
            user_ID BIGINT,
            created_at TIMESTAMPTZ,
            tweet TEXT;
 

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

Пример:

 ("word1":[1,20,22,23,24,25,26,27,28,29,30,29,28,27,26,25,26,27,28,29,30,29,28,29,28,27,28,29,30,30,...],
'word2' [...])
 

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

Текущий запрос:

     SELECT word, count(*)
    FROM (
        SELECT regexp_split_to_table(
            regexp_replace(tweet_clean, 'y(rt|co|https|amp|f)y', '', 'g'), 's ')
        AS word
    FROM tweet
    ) t
    GROUP BY word
    ORDER BY count(*) DESC
    LIMIT 10;
 

Который возвращает:

 [('vaccine', 286669),
 ('covid', 213857),
 ('yum', 141345),
 ('pfizer', 39532),
 ('people', 28960),
 ('beer', 27117),
 ('say', 24569),
 ('virus', 23682),
 ('want', 21988),
 ('foo', 19823)]
 

Если вы хотите получать 10 лучших слов в день, вы можете сделать:

 select *
from (
    select date_trunc('day', created_at) as created_day, word, count(*) as cnt,
        rank() over(partition by date_trunc('day', created_at) order by count(*) desc) rn
    from tweet t
    cross join lateral regexp_split_to_table(
        regexp_replace(tweet_clean, 'y(rt|co|https|amp|f)y', '', 'g'),
        's '
    ) w(word)
    group by created_day, word
) t
where rn <= 10
order by created_day, rn desc
 

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

 select wd.word,
       array_agg(day_rank) over (order by created_day) as ranks
from (select date_trunc('day', t.created_at) as created_day, w.word,
             sum(count(*)) as total_cnt,
             rank() over(partition by date_trunc('day', created_at) order by count(*) desc) as day_rank
      from tweet t cross join lateral
           regexp_split_to_table(regexp_replace(tweet_clean, 'y(rt|co|https|amp|f)y', '', 'g'
                                               ), 's '
                                ) w(word)
      group by created_day, word
     ) wd
order by total_cnt desc
limit 10;
 

Проблема здесь в том, что массивы могут быть разной длины. В Postgres вы можете добавить дополнительные значения, но не совсем ясно, что должно быть размещено там для ранжирования.

Проблема в том, что ранжирование производится за день. Итак, рассмотрим два дня, один из которых содержит 100 слов, а другой — 10 слов. Во-первых, рейтинг «10» — это очень высокий рейтинг. Рейтинг 10 во втором очень низкий.

Я мог бы предложить вам подумать об этой проблеме и задать новый вопрос, если вам нужна помощь в ее решении.