有条件地加入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屋！