CODEWITHSUNDEEP
sqlgoogle-bigquery
Mithras
Mithras

Reputation: 203

Using one table's values to query another table in BigQuery

I have two tables in BigQuery, one is a journey_times table with a schema:

  1. journey_id (int)
  2. vehicle_id (string)
  3. start (timestamp yyyy-mm-dd hh:mm:ss)
  4. finish (timestamp yyyy-mm-dd hh:mm:ss)

Sample data:

- journey_id¦ vehicle¦ start¦ finish - 1¦ car1¦ 2017-12-05 01:33:44 UTC¦ 2017-12-05 01:53:14 UTC - 2¦ car2¦ 2017-12-05 03:04:18 UTC¦ 2017-12-05 03:28:49 UTC

and the other is a distance table with a schema of:

  1. vehicle_id (string)
  2. timestamp (timestamp yyyy-mm-dd hh:mm:ss)
  3. value (float)

Sample data:

- vehicle¦ timestamp¦ value - car3¦ 2016-08-30 17:36:52 UTC¦ 0.01635375 - car3¦ 2016-08-30 17:36:53 UTC¦ 0.02862375

What I want to do is query the distance table and find the maximum and minimum value for each of the journey rows in the journey_times table (with the same vehicle ID and between the start and finish timestamps) to end up with a table that can be joined onto the journey table and look like:

  1. journey_id
  2. max_distance_value
  3. min_distance_value

How would you write this in in BigQuery standard or legacy SQL?
My attempt below doesn't produce any results. `

WITH
  distance_table AS (
  SELECT
    vehicle,
    timestamp,
    value
  FROM
    'project.trip_distance' ),
  journey_table AS (
  SELECT
    journey_id,
    vehicle,
    start,
    finish        
  FROM
    'project.journey_times')
SELECT
  MIN(distance_table.value)
FROM
  distance_table JOIN journey_table
  using (vehicle)
WHERE
  distance_table.vehicle = journey_table.vehicle
  AND distance_table.timestamp BETWEEN journey_table.start
  AND journey_table.finish

Upvotes: 2

Views: 4087

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If I understand correctly, this is a join and group by:

select j.journey_id, min(value), max(value)
from journey_times jt join
     vehicles v
     on jt.vehicle_id = v.vehicle_id and
        v.timestamp between jt.start and jt.finish
group by j.journey_id;

Upvotes: 3

Related Questions