Semijoin表达式必须是逻辑AND的一部分 [英] Semijoin expression must be a part of logical AND

问题描述

我有一个表（我们可以称之为A）和一些字段（model：string，age：integer，code1：integer，code2：integer，code3：integer）和另一个表（可以是codes ）分类代码（code：integer，codetype：string，description：string）。

该字段代码类型用于组代码。例如，200到300之间的代码是棕色的。每个项目最多可以有3个代码。

现在，我只想得到简单而简单的查询：

  SELECT model，age FROM dataset.A 
 WHERE code1 IN（SELECT code FROM dataset.codes WHERE codetype ='brown'）
  

它起作用，除了事实，还有两个其他的。所以，如果我这样做：

  SELECT model，age FROM dataset.A 
 WHERE code1 IN（SELECT代码FROM数据集代码WHERE codetype ='brown'）
 OR code2 IN（SELECT code FROM dataset.codes WHERE codetype ='brown'）
 OR code3 IN（SELECT code FROM dataset.codes WHERE codetype = 'brown'）
  

不起作用，我收到以下错误消息：

 错误：（xx：yy）：Semijoin表达式（即x IN（SELECT ...））必须是逻辑AND的一部分。 
  

我相信Google应该有办法做到这一点。

顺便说一句，我正在开发的这个项目不是用A和代码。我真的需要一个解决方案。感谢任何帮助。

解决方案

BigQuery的确存在限制，即IN子查询必须与ANDs连接。作为一种解决方法，您可以分别执行每个SELECT，然后UNION结果，即

  SELECT模型，age FROM 
（SELECT model，age FROM dataset.A 
 WHERE code1 IN（SELECT code FROM dataset.codes WHERE codetype ='brown'）），
（SELECT model，age FROM dataset.A 
 WHERE code2 IN（SELECT code FROM dataset.codes WHERE codetype ='brown'）），
（SELECT model，age FROM dataset.A 
 WHERE code3 IN（SELECT code FROM dataset.codes WHERE codetype ='棕色'））
  

这可能会导致（模型，年龄）对的重复，所以如果这是不需要，您可以使用 GROUP BY模型，年龄

删除重复项

I have a table (we can call it "A") with some fields (model:string, age:integer, code1:integer, code2:integer, code3:integer) and another table (it can be "codes") with classified codes (code:integer,codetype:string,description:string).

That field codetype is there in order to group codes. For example, codes between 200 and 300 are brown. And every item can have up to 3 codes.

Now, I just want to get that simple, simple query:

SELECT model,age FROM dataset.A
WHERE code1 IN (SELECT code FROM dataset.codes WHERE codetype='brown')

and it works, except the fact, there are two other ones. So, if I do in this way:

SELECT model,age FROM dataset.A
WHERE code1 IN (SELECT code FROM dataset.codes WHERE codetype='brown')
OR code2 IN (SELECT code FROM dataset.codes WHERE codetype='brown')
OR code3 IN (SELECT code FROM dataset.codes WHERE codetype='brown')

it doesn't work and I get this error message:

Error: (xx:yy): Semijoin expression (i.e. "x IN (SELECT ...)") must be a part of logical AND.

I am sure Google should have a way to do this.

By the way, the project I am developing is not with "A" and "codes". I really need a solution for this. Thanks any help.

解决方案

BigQuery indeed currently has limitation that IN subqueries must be connected with ANDs. As a workaround, you may execute each SELECT separately, and then UNION the results, i.e.

SELECT model, age FROM
(SELECT model,age FROM dataset.A
WHERE code1 IN (SELECT code FROM dataset.codes WHERE codetype='brown')),
(SELECT model,age FROM dataset.A
WHERE code2 IN (SELECT code FROM dataset.codes WHERE codetype='brown')),
(SELECT model,age FROM dataset.A
WHERE code3 IN (SELECT code FROM dataset.codes WHERE codetype='brown'))

This may result in duplicates of (model, age) pairs, so if this is not desired, you can remove duplicates using GROUP BY model, age

这篇关于Semijoin表达式必须是逻辑AND的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！