CODEWITHSUNDEEP
google-bigquery
KayEss
KayEss

Reputation: 419

Find MAX, AVG between every current and previous row BigQuery

I have a table with 150.000 rows containing DateTime and Speed column. Timestamp difference between rows is 10 seconds. I want to calculate MAX and AVG of Speed column for each 20 second segment (2x 10 sec), so basically compare each current row with its previous row and calculate MAX and AVG of Speed column.

Expected result:

DateTime                  Speed   MAXspeed   AVGspeed
2019-03-21 10:58:34 UTC     52                 
2019-03-21 10:58:44 UTC     50       52          51
2019-03-21 10:58:54 UTC     55       55          52.5
2019-03-21 10:59:04 UTC     60       60          57.5
2019-03-21 10:59:14 UTC     65       65          62.5
2019-03-21 10:59:24 UTC     63       65          64
2019-03-21 10:59:34 UTC     50       63          56.5
2019-03-21 10:59:44 UTC     50       50          50
2019-03-21 10:59:54 UTC     50       50          50
...

I tried with query below but it is obviously wrong:

select *,
      MAX(SpeedGearbox_km_h, LAG(SpeedGearbox_km_h) over (order by DateTime)) as Maxspeeg,
      AVG(SpeedGearbox_km_h, LAG(SpeedGearbox_km_h) over (order by DateTime)) as AVGspeed,
    from `xx.yy` 
    group by 1,2
    order by DateTime

Upvotes: 1

Views: 502

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10212

Just use ROWS BETWEEN 1 PRECEDING AND CURRENT ROW in your queries:

SELECT *,
  MAX(SpeedGearbox_km_h) OVER (ORDER BY DateTime ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as MAXspeed,
  AVG(SpeedGearbox_km_h) OVER (ORDER BY DateTime ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as AVGspeed
FROM `xx.yy` 
ORDER BY DateTime

Upvotes: 1

Related Questions