CODEWITHSUNDEEP
sqlgoogle-bigquery
zstar
zstar

Reputation: 47

BigQuery, difference between consecutive rows related question

It was hard for me to figure out the proper question but I have the following query below which I've been able to get the difference between consecutive rows.

My issue is (as shown in my image) when the icao_address and flight_number changes (which I want) then I want the row at where the change occurs (change of icao_address and flight_number) to reset to zero.

My intention is to then group the query by icao_address and flight_number. 

WITH
  temptable AS (
  SELECT
  date(timestamp) as flight_date,
    timestamp,
    UNIX_SECONDS(timestamp) AS timestamp_unix,
    icao_address,
    flight_number,
    LAG(UNIX_SECONDS(timestamp)) OVER (ORDER BY timestamp) AS value2
  FROM
    `table`
  WHERE
    (icao_address = '70C0C7' and flight_number = 'WY144') or (icao_address = '750467' and flight_number = 'AK1311') and 
    flight_number is not null and 
    timestamp BETWEEN '2020-01-01'
    AND '2020-01-02'),
  relation2 AS (
  SELECT
  flight_date,
    timestamp,
    timestamp_unix,
    timestamp_unix - value2 AS difference,
    icao_address,
    flight_number
  FROM
    temptable
  ) 
  select * 
  from relation2

In the image, at row 11, under the difference column, I would want a zero to be there given the change in icao_address and flight_number. Is this possible, or any other way to do this. I'm trying to implement a case, but am not making any progress.

enter image description here

Upvotes: 2

Views: 964

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you want partition by:

LAG(UNIX_SECONDS(timestamp)) OVER (PARTITION BY icao_address, flight_number ORDER BY timestamp) AS value2

If you actually want 0 rather than NULL, use the three-argument form of LAG():

LAG(UNIX_SECONDS(timestamp), 1, 0) OVER (PARTITION BY icao_address, flight_number ORDER BY timestamp) AS value2

Upvotes: 3

Related Questions