Finding the records based on DateTime range in Google BigQuery

Question

I am trying to select the latitude and longitude for each id in tb1 based on the DateTime range in tb2.

In tb2, for each id, there are 4 DateTime's. I need to get that DateTime range from tb2.

For eg, for id = 162, tb2 will be like

id        DateTime
162     2016-08-12 00:02:13 UTC
162     2016-08-12 00:04:55 UTC
162     2016-08-12 00:24:03 UTC
162     2016-08-12 00:24:30 UTC

For this id in tb2, I should get the Latitude and Longitude from tb1 where c.DateTime should be between '2016-08-12 00:02:13 UTC' and '2016-08-12 00:24:30 UTC'. Not sure on how to pick the first and last rows for each id and apply the range function on it. Any help would be appreciated.

SELECT
*
FROM (
 SELECT
 c.HardwareId AS HardwareId,
 d.DateTime AS DateTime,
 c.Latitude AS Latitude,
 c.Longitude AS Longitude
 FROM (
  SELECT
   *
  FROM
   [db1.tb2]) AS d
  INNER JOIN (
    SELECT
     Latitude,
     Longitude,
     Id,
     DateTime
    FROM TABLE_DATE_RANGE([db1.tb1],TIMESTAMP(CURRENT_TIMESTAMP()),TIMESTAMP(CURRENT_TIMESTAMP())) AS c
 ON
 d.Id = c.Id
 AND c.DateTime = d.DateTime)

if specific user with date range minDateTime and maxDateTime has multiple Long,Lat values in tb1 - which you want to pick? and why yo apply TABLE_DATE_RANGE to tb1? - is tb1 one table or it is partitioned ad has format tb1YYYYMMDD? please clarify — Mikhail Berlyant

Haipeng Su Haipeng Su · Accepted Answer · 2016-08-12T19:09:55

Since you only need Max(DateTime) and Min(DateTime), Try this,

SELECT c.HardwareId AS HardwareId, c.DateTime AS DateTime,
       c.Latitude AS Latitude, c.Longitude AS Longitude
FROM
(
  SELECT id, MAX(DateTime) AS MaxDatetime, MIN(DateTime) as MinDateTime
  FROM [db1.tb2]
  GROUP BY id
) AS d
JOIN
[db1.tb1] AS c
ON c.Id = d.Id
WHERE c.DateTime >= d.MinDateTime and c.DateTime <= d.MaxDateTime

Finding the records based on DateTime range in Google BigQuery

1 Answers