如何排除WHERE NOT EXISTS之类的记录 [英] How to exclude record like WHERE NOT EXISTS
问题描述
我想排除包含存储在exclusion_list表中的关键字的记录。
在典型的数据库中,我们可以通过使用下面的WHERE NOT EXISTS短语来实现这一点;
I want to exclude records which include keywords stored in exclusion_list table. In typical DB, we can do this by using WHERE NOT EXISTS phrase like below;
SELECT datetime, server, page, uniq_id, client_req, domain, search_keyword
FROM apache_log a
WHERE NOT EXISTS
(SELECT 0 FROM exclusion_list e WHERE regexp_like(a.client_req, e.ex_url))
有没有在BigQuery中实现相同的方法?
Is there a way of achieving the same in BigQuery?
推荐答案
SELECT datetime, server, page, uniq_id, client_req, domain, search_keyword
FROM apache_log a
WHERE NOT IN
(SELECT 0 FROM exclusion_list e WHERE regexp_like(a.client_req, e.ex_url))
根据文档,它以与SQL IN
条件工作非常相似的方式工作,其中每个参数都是用其他所有值进行评估和/或处理的。
According to the documentation it works in a fairly similar way to the SQL IN
condition works, where each argument is evaluated then or'd with all of the other values.
如果expr匹配expr1,expr2或
括号中的任何值,则返回true。 IN关键字是(expr =
expr1 || expr = expr2 || ...)的高效缩写。与IN
关键字一起使用的表达式必须是常量,并且它们必须与expr的数据类型匹配。
Returns true if expr matches expr1, expr2, or any value in the parentheses. The IN keyword is an efficient shorthand for (expr = expr1 || expr = expr2 || ...). The expressions used with the IN keyword must be constants and they must match the data type of expr.
更多信息< a href =https://developers.google.com/bigquery/docs/query-reference#comparisonfunctions =nofollowtitle =Google BigQuery比较函数>在这里。
这篇关于如何排除WHERE NOT EXISTS之类的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!