有条件地加入bigquery [英] conditional join in bigquery
问题描述
我有两个桌子.
表1是单个整数列.
表2具有三个列:start_integer,end_integer,数据
Table 2 has three COLUMNS : start_integer, end_integer, data
简单的查询是将整数列与其中的数据连接起来
The simple query is to join the column of integers with data where
integer >= start_integer AND integer <= end_integer
在许多SQL实现中,这可以通过左条件JOIN ... ON BETWEEN
In many SQL implementations this can be accomplished with a left conditional JOIN ... ON BETWEEN
SELECT tbl1.integer, tbl2.data FROM tbl1
LEFT JOIN tbl2 ON tbl1.integer BETWEEN tbl2.start_integer AND
tbl2.end_integer;
但是BigQuery似乎仅支持带有=条件的JOIN ON.
But it seems BigQuery supports only JOIN ON with only an = condition.
这可以通过交叉联接来完成,但是BigQuery抱怨我的表太大. CROSS JOIN EACH无效.
This could be accomplished with a cross join, but BigQuery complains that my tables are too big. CROSS JOIN EACH is invalid.
如何在BigQuery的SQL限制内完成此联接任务?
How can I accomplish this join task within the limitations of BigQuery's SQL?
下面是我的BigQuery SQL:
Below is my BigQuery SQL:
SELECT tbl1.integer, tbl2.data
FROM bq:data.tbl1
CROSS JOIN bq:data.tbl2
WHERE tbl1.integer BETWEEN tbl2.start_integer AND tbl2.end_integer;
哪个返回错误:
错误:4.1-4.132:JOIN运算符的右侧表必须是一个小表.如果左侧表较小,请切换表;如果两个表均大于 http:/中描述的最大值,请使用JOIN EACH. /goo.gl/wXqgHs .
推荐答案
好消息(2016)! BigQuery现在确实支持不等式联接-确保取消选中使用旧版SQL选项".
Good news (2016)! BigQuery does support inequality joins now - make sure to uncheck the "use legacy SQL option".
查询示例:
SELECT *
FROM (
SELECT 1 x
) a JOIN (
SELECT 2 y
) b
ON a.x<b.y
使用旧版SQL:
Error: ON clause must be AND of = comparisons of one field name from each table, ...
使用标准SQL:
1 2
- 文档: https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql
- 讨论: https://code.google. com/p/google-bigquery/issues/detail?id = 448#c31
- Docs: https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql
- Discussion: https://code.google.com/p/google-bigquery/issues/detail?id=448#c31
这篇关于有条件地加入bigquery的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!