Как подсчитать количество вхождений слов из одной таблицы в комментарии в другой таблице

Я пытаюсь выполнить задачу в Google BigQuery, которая может потребовать логики. Я не уверен, что SQL может обрабатывать ее изначально.

У меня есть 2 таблицы:

  1. Первая таблица имеет один столбец, где каждая строка представляет собой одно слово в нижнем регистре.
  2. Вторая таблица представляет собой базу данных комментариев (с такими данными, как автор комментария, сам комментарий, отметка времени и т. д.).

Я хочу отсортировать комментарии во второй таблице по количеству вхождений слов в первой таблице.

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

words = ['a','b','c','d','e']

comments = ['this is the first sentence', 'this is another comment', 'look another sentence, which is also a comment', 'nope', 'no', 'run']

wordcount = {}

for comment in comments:
    for word in words:
        if word in comment:
            if comment in wordcount:
                wordcount[comment] += 1
            else:
                wordcount[comment] = 1

print(sorted(wordcount.items(), key = lambda k: k[1], reverse=True))

Выход:

[('look another sentence, which is also a comment', 3), ('this is another comment', 3), ('this is the first sentence', 2), ('nope', 1)]

Лучшее, что я видел до сих пор для создания SQL-запроса, это делать что-то вроде следующего:

SELECT
    COUNT(*)
FROM
    table
WHERE
    comment_col like '%word1%'
    OR comment_col like '%word2%'
    OR ...

Но там более 2000 слов... как-то не по себе. Какие-нибудь советы?


sql google-bigquery bigquery-standard-sql
person Shawn Tabrizi    schedule 22.10.2017    source источник
comment
Поскольку вы используете буквы вместо слов, я могу понять, почему вы получаете такой вывод!   -  person shA.t    schedule 22.10.2017

Ответы (2)


arrow_upward
2
arrow_downward

Ниже приведен стандартный SQL BigQuery.

#standardSQL
SELECT comment, COUNT(word) AS cnt
FROM comments
JOIN words
ON STRPOS(comment, word) > 0 
GROUP BY comment
-- ORDER BY cnt DESC

Как вариант, вы можете использовать регулярное выражение, если хотите:

#standardSQL
SELECT comment, COUNT(word) AS cnt
FROM comments
JOIN words
ON REGEXP_CONTAINS(comment, word)
GROUP BY comment
-- ORDER BY cnt DESC

Вы можете протестировать/поиграть с выше, используя фиктивный пример из вашего вопроса.

#standardSQL
WITH words AS (
  SELECT word
  FROM UNNEST(['a','b','c','d','e']) word
),
comments AS (
  SELECT comment 
  FROM UNNEST(['this is the first sentence', 'this is another comment', 'look another sentence, which is also a comment', 'nope', 'no', 'run']) comment
)
SELECT comment, COUNT(word) AS cnt
FROM comments
JOIN words
ON STRPOS(comment, word) > 0 
GROUP BY comment
ORDER BY cnt DESC

Обновление для:

Есть ли какие-нибудь быстрые предложения, чтобы сделать только полное соответствие строки?

#standardSQL
WITH words AS (
  SELECT word
  FROM UNNEST(['a','no','is','d','e']) word
),
comments AS (
  SELECT comment 
  FROM UNNEST(['this is the first sentence', 'this is another comment', 'look another sentence, which is also a comment', 'nope', 'no', 'run']) comment
)
SELECT comment, COUNT(word) AS cnt
FROM comments
JOIN words
ON REGEXP_CONTAINS(comment, CONCAT(r'\b', word, r'\b')) 
GROUP BY comment
ORDER BY cnt DESC
person Mikhail Berlyant    schedule 22.10.2017
comment
Спасибо, Михаил, я считаю, что это работает, как я описал. Но я обнаружил, что совпадений подстрок слишком много. Есть ли какие-нибудь быстрые предложения, чтобы сделать только полное соответствие строки? Кроме того, я не ограничиваюсь стандартным SQL, я думал, что это единственное, что поддерживает BigQuery, но, похоже, он может больше. - person Shawn Tabrizi; 23.10.2017
comment
Ты самый лучший! Спасибо за вашу помощь. Собираюсь пойти и прочитать больше SQL :) - person Shawn Tabrizi; 23.10.2017

arrow_upward
1
arrow_downward

Если я это хорошо понимаю, я думаю, вам нужен такой запрос:

select comment, count(*) cnt
from comments
join words
  on comment like '% ' + word + ' %'   --this checks for `... word ..`; a word between spaces
  or comment like word + ' %'          --this checks for `word ..`; a word at the start of comment
  or comment like '% ' + word          --this checks for `.. word`; a word at the end of comment
  or comment = word                    --this checks for `word`; whole comment is the word
group by comment
order by count(*) desc

Демонстрация SQL Server Fiddle в качестве примера

person shA.t    schedule 22.10.2017