mysql - mysql中基于事件的报告

Question

我有如下表格

geofence  time   event    vehicle   
g1        10:00  enter    BMW
g1        10:05  inside   BMW
g2        11:00  enter    AUDI
g2        11:06  inside   AUDI
g1        11:07  exit     BMW
g5        12:00  enter    BMW
g5        12:05  inside   BMW
g5        12:10  inside   BMW
g5        13:00  exit     BMW
g1        15:00  enter    BMW
g1        15:05  inside   BMW
g1        16:00  exit     BMW

我需要报告车辆在地理围栏内的时间、进出时间

vehicle  geofence enter  duration     exit
BMW      g1       10:00  1 hour 7min  11:07 
AUDI     g2       11:00  -            -
BMW      g5       12:00  1 hour       13:00
BMW      g1       15:00  1 hour       16:00 

编辑车辆可以多次进出同一个地理围栏，我们需要从同一辆车的当前进入事件中找出最近的导出。车辆每 10 秒发出一次数据，有时我们需要制作 1 周数据的报告，如 24*7*60*60/10 数据。

Answer 1

这是我的想法:
按 geofence 和 vehicle 对行进行分组然后为每个 enter 找到下一个 exit ，然后进行 diff。

select p1.vehicle,
  p1.geofence as geofence,
  p1.time as enter,
  ROUND(time_to_sec(TIMEDIFF (STR_TO_DATE(p2.time, "%H:%i"), STR_TO_DATE(p1.time, "%H:%i"))) /60) as duration,
  p2.time as _exit
  from (select * from vehicle_event WHERE event = "enter" GROUP BY geofence, vehicle) p1

  INNER JOIN

  (select * from vehicle_event WHERE event = "exit"  GROUP BY geofence, vehicle ORDER BY id LIMIT 1) p2
    on p1.vehicle = p2.vehicle AND p1.geofence = p2.geofence;

结果:

注意:持续时间以分钟表示，您可以将其更改为您想要的任何格式。

测试数据:

CREATE TABLE IF NOT EXISTS vehicle_event (
  id bigint(20) NOT NULL AUTO_INCREMENT,
   geofence VARCHAR(30) NOT NULL,
  time VARCHAR(30) NOT NULL,
  event VARCHAR(30) NOT NULL,
  vehicle VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id`));


  insert into vehicle_event (geofence, time, event, vehicle) VALUES("g1", "10:00", "enter", "BMW");
  insert into vehicle_event (geofence, time, event, vehicle) VALUES("g2", "10:05", "inside", "BMW");
  insert into vehicle_event (geofence, time, event, vehicle) VALUES("g2", "11:00", "enter", "AUDI");
  insert into vehicle_event (geofence, time, event, vehicle) VALUES("g2", "11:06", "inside", "AUDI");
  insert into vehicle_event (geofence, time, event, vehicle) VALUES("g2", "11:07", "exit", "BMW");

---

更新:

---

如果车辆可以多次进入同一个geofence，我们只需要为每次进入找到下一个导出并将分组标准放在加入之后:

select p1.vehicle,
  p1.geofence as geofence,
  p1.time as enter,
  ROUND(time_to_sec(TIMEDIFF (STR_TO_DATE(p2.time, "%H:%i"), STR_TO_DATE(p1.time, "%H:%i"))) /60) as duration,
  p2.time as _exit
  from (select * from vehicle_event WHERE event = "enter") as p1

  INNER JOIN

  (select * from vehicle_event WHERE event = "exit"  ORDER BY id ) p2
    on p1.vehicle = p2.vehicle AND p1.geofence = p2.geofence AND p2.id > p1.id
  GROUP BY vehicle, geofence, enter;

使用的额外测试数据:

  insert into vehicle_event (geofence, time, event, vehicle) VALUES("g1", "12:00", "enter", "BMW");
  insert into vehicle_event (geofence, time, event, vehicle) VALUES("g1", "12:30", "exit", "BMW");

新结果: