如何在純 SQL 中過濾連續的時間線事件？

我在停車場執行 LPR 系統，並記錄每個車牌以及記錄它的相機。

我有 2 個攝像頭，攝像頭 1 在入口處，攝像頭 2 在出口處。因此，對於每次閱讀，我都會在桌子上記錄以下內容

Id|Camera|Plate|Timestamp

我需要執行一個查詢，返回每輛尚未離開停車場的汽車。所以條件是：我需要返回每個有一個條目（camera = 1）而不是一個出口（camera = 2）的記錄。

哪個是最好的方法？如果可能的話，我不想使用游標。

我應該創建 2 個臨時表：

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

每個相機一個，然後

SELECT temp1_entries except temp2_exits 

上述查詢的結果將是尚未退出的汽車及其進入時間戳。

DBMS 是MariaDB，版本10.3.7。

這種方法正確嗎？有更好的方法嗎？

你不需要游標。

如果你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()邏輯是，首先我們通過視窗函式使用單個汽車的“事件歷史”順序擴展表：Raw子查詢本質上CameraLog用新的EventNo列擴展表。此列本質上是自動遞增的id，但它是為所有板單獨訂購的。

在第二步中，在主查詢中，我們將這個表與其自身連接在一起。本質上，我們試圖為所有事件尋找下一個事件。

結果是我們有這樣一個表：

columns of camera events | columns of the consecuting camera event

WHERE在這種列結構中，使用（最後一行）進行過濾已經很容易了。

請注意，如果表的大小與伺服器中可用 RAM 的順序相同，則此查詢將非常不理想。如果您需要快速處理大型數據表，您將需要更高級的技術。

我認為您可以通過一個相當簡單的查詢來做到這一點：

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"

我認為一個不同的索引 - on (plate, timestamp, camera)- 對於這個查詢會更好。

引用自：https://dba.stackexchange.com/questions/209316