如何使用间隔交叉连接大查询？ [英] How to cross join in Big Query using intervals?

问题描述

我如何在Google Big Query中使用间隔连接两个表？

我有两张表：

 表CarsGPS：
 ID |汽车|纬度|经度
 1 | 1 | -22.123 | -43.123 
 2 | 1 | -22.234 | -43.234 
 3 | 2 | -22.567 | -43.567 
 4 | 2 | -22.678 | -43.678 
 ... 
 
表格区域：
 ID | LatitudeMin | LatitudeMax | LongitudeMin | LongitudeMax 
 1 | -22.124 | -22.120 | -43.124 | -43.120 
 2 | -22.128 | -22.124 | -43.128 | -43.124 
 ... 
  

我想交叉加入这些表以检查哪些区域是每辆车通过Google Big Query传递的。

在一个普通的SQL服务器中，我会做：

  SELECT A.ID，C.Car 
 FROM Cars C，Areas A 
 WHERE C.Latitude BETWEEN A.LatitudeMin AND A.LatitudeMax AND 
 C .Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax 
  

但Google Big Query只允许我进行连接甚至是JOIN EACH）使用连接表中的完全匹配。

所以，这不是一个选项：

pre> SELECT A.ID，C.Car
FROM Cars C
加入每个
区域A
ON C.Latitude之间A.LatitudeMin AND A.LitudeitudeMax AND
C.Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax


然后，我该如何运行类似于它的东西来识别哪些车在每个区域内通过？

感谢高级，

解决方案

BigQuery现在支持CROSS JOIN。您的查询将如下所示：

  SELECT A.ID，C.Car 
 FROM Cars C 
 CROSS加入区域A 
其中A.LatitudeMin和A.LatitudeMax之间的距离和
 C.LongitudeMin和A.LongitudeMax之间的距离
  

How can I join two tables using intervals in Google Big Query?

I have two table:

Table CarsGPS:
ID | Car | Latitude | Longitude
1  | 1   | -22.123  | -43.123
2  | 1   | -22.234  | -43.234
3  | 2   | -22.567  | -43.567
4  | 2   | -22.678  | -43.678
...

Table Areas:
ID | LatitudeMin | LatitudeMax | LongitudeMin | LongitudeMax
1  | -22.124     | -22.120     | -43.124      | -43.120
2  | -22.128     | -22.124     | -43.128      | -43.124
...

I'd like to cross join these tables to check in which areas each car has passed by using Google Big Query.

In a regular SQL server I would make:

SELECT A.ID, C.Car 
FROM Cars C, Areas A
WHERE C.Latitude BETWEEN A.LatitudeMin AND A.LatitudeMax AND
      C.Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax

But Google Big Query only allows me to do joins (even JOIN EACH) using exact matches among joined tables. And the "FROM X, Y" means UNION, not JOINS.

So, this is not an option:

SELECT A.ID, C.Car 
FROM Cars C
JOIN EACH
  Areas A
  ON C.Latitude BETWEEN A.LatitudeMin AND A.LatitudeMax AND
     C.Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax

Then, how can I run something similar to it to identify which cars passed inside each area?

Thanks in advanced,

解决方案

BigQuery now supports CROSS JOIN. Your query would look like:

SELECT A.ID, C.Car 
FROM Cars C 
CROSS JOIN Areas A
WHERE C.Latitude BETWEEN A.LatitudeMin AND A.LatitudeMax AND
  C.Longitude BETWEEN A.LongitudeMin AND A.LongitudeMax

这篇关于如何使用间隔交叉连接大查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！