有条件地加入bigquery

本文介绍了有条件地加入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

• 文档: 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