Блог

Я ломал голову над следующей проблемой. У меня есть две таблицы BigQuery, одна таблица с примерно 3 миллионами поисковых запросов:

 search_term
number of people named joe
how to paint a table black
top 100 pop songs
lovely horses
..
  

и список из 8000 «ключевых слов». Каждая строка в таблице ключевых слов — это количество слов.

 keyword
name joe
horses 
baby kitten
song top 100
..
  

Для каждой строки в таблице «запрос» я хочу проверить, содержит ли она какие-либо слова из таблицы «ключевые слова». Однако порядок, в котором ключевые слова появляются в строке «запроса», не имеет значения, и между ними могут быть другие слова. Это результирующая таблица, которую я ищу:

 search term                    contains_keywords
number of people named joe     TRUE
how to paint a table black     FALSE
top 100 pop songs              TRUE
lovely horses                  TRUE
..  
  

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

 SELECT 
  *
, CASE 
    WHEN search_term IN (
      SELECT
        keyword
      FROM 
        keywords)
    THEN true
    ELSE false
  END AS contains_keyword
FROM search_terms
  

РЕДАКТИРОВАТЬ: я предоставил две приведенные выше примеры таблиц, чтобы упростить опробование вашего собственного кода.

 
WITH 

search_terms AS (
  SELECT 'number of people named joe' AS search_term UNION ALL
  SELECT 'how to paint a table black' AS search_term UNION ALL
  SELECT 'top 100 pop songs'          AS search_term UNION ALL
  SELECT 'lovely horses'              AS search_term

  )

,keywords AS (
  SELECT 'name joe'         AS keyword UNION ALL
  SELECT 'horses lovely'    AS keyword UNION ALL  
  SELECT 'baby kitten'      AS keyword UNION ALL
  SELECT 'song top 100'     AS keyword
  )  
SELECT * FROM search_terms
  

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

Использование шаблона регулярных выражений, состоящего из более чем 8000 слов, может быть довольно ресурсоемким!
Ниже приведен обходной путь

 #standardSQL
SELECT search_term, 
  ( SELECT COUNT(1)
    FROM UNNEST(SPLIT(search_term, ' ')) word
    JOIN UNNEST(keywords) word
    USING(word)
  ) > 0 AS contains_keyword
FROM `project.dataset.search_terms`,
UNNEST([STRUCT(ARRAY(
  SELECT DISTINCT keyword
  FROM `project.dataset.keywords`, UNNEST(SPLIT(keyword, ' ')) keyword
) AS keywords)])
  

Если применить к образцу данных из вашего вопроса — вывод

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

 SELECT st.*,
       regexp_contains(search_term, k.pattern)
FROM search_terms st cross join
     (select string_agg(replace(keyword, ' ', '|'), '|') as pattern
      from keywords
     ) k;
  

Это преобразует все ключевые слова в одно регулярное выражение со строками, разделенными | символом , который является регулярным выражением, обозначающим «или».