Блог

У меня есть таблица с 150.000 строками, содержащая столбец даты, времени и скорости. Разница во временных метках между строками составляет 10 секунд. Я хочу рассчитать МАКСИМАЛЬНОЕ и среднее значение столбца скорости для каждого 20-секундного сегмента (2×10 сек), поэтому в основном сравниваю каждую текущую строку с ее предыдущей строкой и вычисляю МАКСИМАЛЬНОЕ и среднее значение столбца скорости.

Ожидаемый результат:

 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
... 
 

Я попытался с помощью запроса ниже, но это явно неправильно:

 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
 

Просто используйте СТРОКИ МЕЖДУ 1 ПРЕДЫДУЩЕЙ И ТЕКУЩЕЙ СТРОКОЙ в своих запросах:

 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