如何在 Google BigQuery SQL 中检查多个模式?(喜欢 + 输入) [英] How to check for multiple patterns in Google BigQuery SQL? (LIKE + IN)

问题描述

所以我必须在 BigQuery 表中搜索一堆名称，我会定期在另一个数据集中收集这些名称.此时数据集太大，包含近 60k 个名称，我无法再做

So I have to search for a bunch of names in a BigQuery table which I collect periodically in another dataset. The dataset is too large at this point containing almost ~60k names and I no longer can do

SELECT * FROM base.table WHERE name LIKE '%name1%' OR name LIKE '%name2%.....

当我使用 python 脚本尝试它时:

As I tried it using a python script with:

SELECT * FROM base.table WHERE name LIKE({' OR '.join([f'ulv4.full_name LIKE %{name}%' for name in names])})

但是查询的字符限制超过了这么多名称.我尝试查看诸如 this 之类的解决方案以及同一问题的其他答案，但似乎没有答案适用于 BigQuery Standard SQL.非常感谢您在这方面的任何帮助.

But the character limit for query exceeds for this many names. I tried looking at solutions like this and other answers to the same question but no answer seems to work for BigQuery Standard SQL. Any help in this regard is highly appreciated.

推荐答案

您应该将名称保存在另一个表中，然后加入:

You should keep the names in another table and then join to it:

SELECT *
FROM base.table t1
WHERE EXISTS (SELECT 1 FROM other.table t2
              WHERE t1.name LIKE CONCAT('%', t2.name, '%'));

然后，base.table 中的任何记录只有在它包含来自另一个表的某个子字符串名称时才会匹配.

Then, any record in the base.table would only match if it contains some substring name from the other table.

这篇关于如何在 Google BigQuery SQL 中检查多个模式?(喜欢 + 输入)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！