mysql - 如何分析时间序列数据并删除“重复”记录

问题描述

我有一大组数据（约 2M 行）来描述车辆在大型停车场周围移动时的数据。也就是说，每辆车在通过结构中的多个“区域”时都会被扫描。它看起来像这样：

+--------+----------------+----------+---------------------+
|   id   | zone_camera_id |  plate   |      timestamp      |
+--------+----------------+----------+---------------------+
| 453445 | Z05-C01        | AAAABBBB | 2020-06-25 08:02:23 |
| 453446 | Z05-C02        | AAAABBBB | 2020-06-25 08:04:55 |
| 453447 | Z03-C01        | CCCCDDDD | 2020-06-25 08:05:19 |
| 453448 | Z02-C02        | AAAABBBB | 2020-06-25 08:05:23 |
| 453449 | Z07-C03        | CCCCDDDD | 2020-06-25 08:09:08 |
| 453450 | Z07-C04        | CCCCDDDD | 2020-06-25 08:10:01 |
| 453451 | Z04-C04        | AAAABBBB | 2020-06-25 08:11:44 |
| 453452 | Z04-C01        | AAAABBBB | 2020-06-25 08:11:59 |
| 453453 | Z04-C03        | AAAABBBB | 2020-06-25 08:12:06 |
| 453454 | Z05-C03        | AAAABBBB | 2020-06-25 08:13:00 |
+--------+----------------+----------+---------------------+

camera_id像这样分解：<Zone ID>-<Camera ID>，并且<Camera ID>通常是不相关的；被检测到的车辆Z05-C01等同于被检测到的同一车辆Z05-C04。

我可以快速GROUP BY使用zone_camera_idusing LEFT()，如下所示：

SELECT Count(*) AS scan_count,
       LEFT(zone_camera_id, 3) AS zone
FROM   vehicle_scans
WHERE  plate = 'AAAABBBB'
GROUP  BY LEFT(zone_camera_id, 3)

我看到了：

+------------+------+
| scan_count | zone |
+------------+------+
| Z05        |    3 |
| Z02        |    1 |
| Z04        |    3 |
+------------+------+

这是很好的信息，但它并没有提供对驾驶员所走“路径”的任何见解。此查询不知道时间顺序，因此如果驱动程序从开始Z02、转到Z05并 * 回到 * Z02，则这些Z02扫描将集中在一起。

我要做的是删除“重复”扫描，其中司机在同一区域连续多次被扫描（没有离开该区域，ID=453445,453446但不是ID=453454当司机返回时）但从未离开该区域. 基本上，我想知道驾驶员何时进入区域并离开区域，而在该时间段内没有访问其他区域。

我正在寻找确定每辆车在每个区域连续花费多少时间，即使它们稍后返回该区域，如下所示：

+---------+------------+---------------------+---------------------+
| zone_id | scan_count |     enter_time      |      exit_time      |
+---------+------------+---------------------+---------------------+
| Z05     |          2 | 2020-06-25 08:02:23 | 2020-06-25 08:04:55 |
| Z02     |          1 | 2020-06-25 08:05:23 | 2020-06-25 08:05:23 |
| Z04     |          3 | 2020-06-25 08:11:44 | 2020-06-25 08:12:06 |
| Z05     |          1 | 2020-06-25 08:13:00 | 2020-06-25 08:13:00 |
+---------+------------+---------------------+---------------------+

Z05出现两次，因为他们两次访问该区域，中间还有两次其他区域访问。

这是我尝试过的，使用Min()and Max()：

SELECT Count(*)                 AS scan_count,
       LEFT(camera_zone_id, 3)  AS zone_id,
       Min(timestamp)           AS enter_time,
       Max(timestamp)           AS exit_time
FROM   vehicle_scans
WHERE  plate = 'AAAABBBB'
GROUP  BY LEFT(camera_zone_id, 3)
ORDER  BY enter_time

这是很好的信息，它与我想要的输出的结构相匹配，但是Min()和Max()值反映了该区域内扫描的绝对最小和最大时间戳，而不是单个区域中扫描序列的最小和最大时间戳。在与上述类似的情况下AAAABBBB，车辆从开始Z05，访问另外两个区域，然后返回Z05。上面的查询使用了第一次Z05扫描和最后一次Z05扫描，即使中间还有两个访问过的区域 (Z02和Z04)。我正在寻找对自己行中区域的每次不间断“访问”，在它们继续被扫描时删除“重复”扫描LEFT(camera_zone_id, 3)。

是否有基于 SQL 的方法将这些行分组为不间断的序列？

谢谢！

标签： mysqlsqltime-series

select plate, left(camera_zone_id, 3), min(timestamp), max(timestamp) from (select vs.*, row_number() over (partition by plate, left(camera_zone_id, 3) order by timestamp) as seqnum_pc, row_number() over (partition by plate order by timestamp) as seqnum_p from vehicle_scans vs ) vs group by plate, (seqnum_pc - seqnum_p)

mysql - 如何分析时间序列数据并删除“重复”记录

问题描述

解决方案

推荐阅读