select count(*) / count(distinct to_date(datetime)) as trips_per_dayfrom (select radar_id ,datetime ,lead(radar_id) over w as next_radar_id ,lead(datetime) over w as next_datetime from mytable where radar_id in ('A21','B15') window w as ( partition by car_id order by datetime ) ) twhere radar_id = 'A21' and next_radar_id = 'B15' and datetime + interval '30' minutes >= next_datetime;+----------------+| trips_per_day |+----------------+| 1.5 |+----------------+
ps
如果您的版本不支持间隔，则最后的代码记录可以替换为-
and to_unix_timestamp(datetime) + 30*60 > to_unix_timestamp(next_datetime)
