Get the Latest Record Before Given Time with OVER PARTITION BigQuery

0
votes

I have BigQuery table that store the record of equipment position like this: GPS record

and I want to get the latest record before the given time and make the table this: first and second record combined in a row where the first and the second record combined in the same row.

I usually use PostgreSQL and would do this with LEFT JOIN LATERAL but LATERAL JOIN is not available in BigQuery and I'm having hard time to do this in standard SQL. And I think this can be solved with OVER PARTITION but i'm not sure

Anyone knows to solve it? Thanks!

1
google-bigquery

1 Answers

1
votes

Maybe LAG can help:

WITH finishers AS (
  SELECT '1' as name, TIMESTAMP '2016-10-18 2:51:45' as finish_time
  UNION ALL SELECT '2', TIMESTAMP '2016-10-18 2:54:11'
  UNION ALL SELECT '3', TIMESTAMP '2016-10-18 2:59:01'
  UNION ALL SELECT '4', TIMESTAMP '2016-10-18 3:01:17'
)
SELECT 
  name,
  finish_time,
  LAG(name) OVER (ORDER BY finish_time) AS previous_name,
  LAG(finish_time) OVER (ORDER BY finish_time) AS previous_time,
FROM finishers;

enter image description here

You can also PARTITION BY idalat