我有两个桌子.
表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
- 文档: cloud.google/bigquery/sql-reference/enabling-standard-sql
- 讨论: code.google. com/p/google-bigquery/issues/detail?id = 448#c31
- Docs: cloud.google/bigquery/sql-reference/enabling-standard-sql
- Discussion: code.google/p/google-bigquery/issues/detail?id=448#c31
更多推荐
有条件地加入bigquery
发布评论