时间戳

首页 > 解决方案 > BigQuery中，根据一张表的计算值匹配两张表

sql - BigQuery中，根据一张表的计算值匹配两张表

问题描述

我在 BigQuery 中有两张表：一张包含道路摄像头的位置，名为cameras...

| city | state |  road  | mile |
|------|-------|--------|------|
| Abcd | Wxyz  | 10101  | 12.3 |
| Efgh | Wxyz  | 98765  | 7.8  |
...

...还有一个包含事故的，名为accidents.

| date  | street | mile |
|-------|--------|------|
| 12-01 | 10101  | 11.9 |
| 12-02 | 10101  | 21.1 |
| 12-02 | 10101  | 12.1 |
| 12-02 | 98765  | 7.1  |
| 12-03 | 98765  | 7.6  |
| 12-03 | 98765  | 5.3  |
...

我需要检查在摄像机之前 0.5 英里和之后 0.5 英里范围内发生了多少事故。

所以，我需要做的是：

  1. 在名为 的表中建立一个 0.5 英里的周长cameras；
  2. accidents检查在桌子的每 0.5 英里范围内发生了多少次事故cameras。

我怎样才能做到这一点？有什么帮助吗？

标签： sqlgoogle-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT c.*, accidents_in_perimeter FROM (
  SELECT ANY_VALUE(c) AS c, COUNT(1) AS accidents_in_perimeter 
  FROM `project.dataset.cameras` c
  JOIN `project.dataset.accidents` a
  ON c.road = a.street
  AND a.mile BETWEEN c.mile - 0.5 AND c.mile + 0.5
  GROUP BY FORMAT('%t', c)
)

如果适用于您的问题的样本数据 - 结果是 

Row city    state   road    mile    accidents_in_perimeter   
1   Abcd    Wxyz    10101   12.3    2    
2   Efgh    Wxyz    98765   7.8     1

推荐阅读