我有一个很大的数据集（大约2百万行），描述了车辆在大型停车场周围移动时的数据。 也就是说，每辆车在通过结构中的多个“区域”时都会被扫描。 它看起来是这样的:
+--------+----------------+----------+---------------------+
| 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按如下方式分解:Z05-C01检测到的车辆等同于Z05-C04检测到的同一车辆。
我可以使用left()按zone_camera_id快速分组，如下所示:
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()和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的方法将这些行按不间断的序列分组？
谢谢！
这可能是一个空隙和孤岛的问题--但是你需要通过板块来巩固这个问题。
行号差值很方便:
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)
