如何在纯SQL中筛选连续时间线事件？

Question

我在停车场上运行了一个LPR系统，我记录了每一个车牌以及记录它的摄像机。

我有两个摄像头，一号相机在入口，二号相机在出口。所以每次阅读时，我都会把下面的内容记录在桌子上

Id|Camera|Plate|Timestamp

我需要运行一个查询，返回每个没有离开停车场的汽车。因此，条件是:我需要返回每个记录，其中有一个条目(camera = 1)，后面没有出口(camera = 2)。

哪一种是最好的方法？如果可能的话我不想用游标。

SELECT plate, MAX(Timestamp)
FROM records WHERE camera = 1 GROUP BY plate, MAX(timestamp)

那么，每台相机一台

SELECT temp1_entries except temp2_exits 

以上查询的结果将是那些没有退出的汽车以及它们的入口时间戳。

数据库管理系统是MariaDB，版本10.3.7。

有没有更好的方法？

Answer 1

你不需要游标。

如果您的id自动递增，您也不需要按时间戳排序，id就足够了。如果没有，则使用时间戳。注意，时间戳有一些有趣的效果(时区、夏令时、一秒钟内的同时效应和许多类似的“惊喜”)。

这个查询过滤那些camera1事件，这些事件没有后面跟着一个camera2事件--要么是因为车在停车场，要么是因为它们在欺骗。该查询还处理“有趣”的情况，例如，如果一辆汽车在入口处被摄像头多次检测到。

它不处理的案件，有人离开没有进入它，但理解的逻辑，这是很容易改进。

WITH Raw AS (
  SELECT RANK() OVER (PARTITION BY Plate ORDER BY Id) AS EventNo,
  Id,
  Camera,
  Plate,
  Timestamp
  FROM CameraLog
)
SELECT Prev.*
FROM CameraLog AS Prev
LEFT JOIN CameraLog AS Next
  ON Prev.Plate = Next.Plate AND Prev.EventNo + 1 = Next.EventNo
WHERE Prev.Camera = 1 AND Next.Camera <> 1;

逻辑是，首先我们通过RANK()窗口函数来扩展具有单个car的“事件历史”顺序的表：Raw子查询实质上用一个新的EventNo列扩展CameraLog表。这一栏本质上是一个自动递增的id，但它是为所有板块排序的工业.

在第二步中，在主查询中，我们将该表与其自身连接在一起。本质上，我们试图为所有事件找到下一个事件。

结果，我们有了这样一张桌子：

columns of camera events | columns of the consecuting camera event

在这个列结构中，已经很容易使用WHERE (最后一行)进行筛选。

注意，如果表的大小按照服务器中可用RAM的顺序排列，则此查询将是高度次优的。如果您需要快速处理大型数据表，则需要更高级的技术。

Answer 2

我认为您可以通过一个相当简单的查询来完成这一任务：

SELECT plate, timestamp
FROM records AS r
WHERE camera = 1
  AND NOT EXISTS
      ( SELECT 1 
        FROM records AS n
        WHERE n.camera = 2 
          AND n.plate = r.plate
          AND n.timestamp > r.timestamp
      ) ;

关于(camera, plate, timestamp)的索引将有助于提高效率。

另一种使用CTE和窗口函数的方法：

WITH cte AS
  ( SELECT plate, timestamp, camera, 
           ROW_NUMBER() OVER (PARTITION BY plate
                              ORDER BY timestamp DESC) AS rn   
    FROM records AS r
  )
SELECT plate, timestamp
FROM cte
WHERE rn = 1              -- the last entry per plate
  AND camera = 1 ;        -- is "enter"

我认为一个不同的索引--在(plate, timestamp, camera)上--会更适合这个查询。