我有一个包含150.000行的表，其中包含DateTime和Speed列。行之间的时间戳差异为10秒。我想为每个20秒段（2x 10秒）计算Speed列的MAX和AVG，因此基本上将每个当前行与其前一行进行比较，并计算Speed列的MAX和AVG。

预期结果：

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