具有 GPS 麵包屑數據的間隙和島嶼
我有一個縫隙和孤島(GAI) 問題,我似乎無法破解以挽救我的生命。我見過的大多數 GAI 問題都涉及以下形式的數據:
[Id] [StartDateTime] [EndDateTime]
但在這種情況下,我的來自 GPS 麵包屑/數據點,所以我們沒有開始和結束,只有時間戳。
我想要做的是以下內容:
給定一個包含卡車編號、位置和日期時間的數據集,如果一行共享一輛卡車和位置,並且在另一行的 1 小時內,則連接在一起。
卡車可以 24/7 全天候執行(車隊司機)。
我的虛擬數據看起來像這樣(重複的行是故意的,因為它們是真實數據的特徵):
DECLARE @TruckLoc TABLE ( TruckId VARCHAR(50), LocationName VARCHAR(12), LocDateTime DATETIME ) ; INSERT INTO @TruckLoc (TruckId, LocationName, LocDateTime) VALUES ('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'), ('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'), ('MonsterTruck', 'Home', '2019-10-22 10:33:10.000'), ('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'), ('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'), ('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'), ('MonsterTruck', 'Gas Station', '2019-10-22 12:43:00.000'), ('MonsterTruck', 'Gas Station', '2019-10-22 13:13:00.000'), ('MonsterTruck', 'Gas Station', '2019-10-22 13:43:00.000'), ('MonsterTruck', 'Home', '2019-10-22 16:43:00.000'), ('MonsterTruck', 'Home', '2019-10-22 16:49:00.000'), ('MonsterTruck', 'Home', '2019-10-22 17:43:00.000'), ('MonsterTruck', 'Home', '2019-10-22 20:43:00.000'), ('MonsterTruck', 'Home', '2019-10-22 20:56:00.000')
我試圖獲得的輸出如下:
Monster Truck | Home | 2019-10-22 12:57:40.000 | 2019-10-22 11:22:40.000 Monster Truck | Gas Station | 2019-10-22 12:43:00.000 | 2019-10-22 13:43:00.000 Monster Truck | Home | 2019-10-22 16:43:00.000 | 2019-10-22 17:43:00.000 Monster Truck | Home | 2019-10-22 20:43:00.000 | 2019-10-22 20:56:00.000
我試過的:
我什麼都試過了。在我的真實陳述中,我按卡車和日期時間對它們進行排名。但這是我取得的最大成功。我上去嘗試使用
LAG/JOIN
函式和CTE,但問題的關鍵是我不需要查看以前的記錄,但我需要查看滿足與其他以前記錄有關的條件的所有以前的記錄即每條記錄都在前一條記錄的一小時內。非常感謝您對此的任何幫助。
這是一個棘手的問題。我已經使用 PostgreSQL 作為小提琴——你應該能夠“翻譯”成 SQL Server 方言——稍作修改,它就可以在任何具有公用表表達式的伺服器上工作。
我將概述步驟。完整的程式碼和數據都在小提琴以及這個答案的末尾。有一些“化石”——即有助於解決問題的額外欄位,我沒有刪除,但它們應該很容易清理。
首先要做的(有點像關係)是建立界限。
找到測量批次之間 1 小時差異的上限 - 從使用以下
LEAD()
函式開始:SELECT truck_id, location_name, loc_date_time, LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id, location_name ORDER BY loc_date_time) AS the_lead FROM truck_loc ORDER BY loc_date_time;
結果(為簡潔起見,僅顯示 3 條記錄):
truck_id location_name loc_date_time the_lead MonsterTruck Home 2019-10-22 10:27:40 2019-10-22 10:27:40 MonsterTruck Home 2019-10-22 10:27:40 2019-10-22 10:33:10 MonsterTruck Home 2019-10-22 10:33:10 2019-10-22 11:22:40
然後,找出大於或等於 1 小時的間隙:
SELECT t1.truck_id, t1.location_name, t1.loc_date_time FROM ( SELECT truck_id, location_name, loc_date_time, LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id ORDER BY loc_date_time) AS the_lead FROM truck_loc ) AS t1 WHERE t1.the_lead >= t1.loc_date_time + INTERVAL '1 HOUR' OR t1.the_lead IS NULL ORDER BY t1.loc_date_time;
測試在
OR t1.the_lead IS NULL
那裡,因為LEAD()
像這樣的系列的最後一個總是NULL
。結果:
truck_id location_name loc_date_time MonsterTruck Home 2019-10-22 11:22:40 MonsterTruck Gas Station 2019-10-22 13:43:00 MonsterTruck Home 2019-10-22 17:43:00 MonsterTruck Home 2019-10-22 20:56:00
檢查表明這是正確的。
然後,您基本上再次做同樣的事情
LAG()
- 程式碼在小提琴中(額外的欄位是“化石”)!substr location_name loc_date_time t tlag diff Mon Home 2019-10-22 10:27:40 1571740060 Mon Gas Station 2019-10-22 12:43:00 1571748180 Mon Home 2019-10-22 16:43:00 1571762580 1571743360 19220 Mon Home 2019-10-22 20:43:00 1571776980 1571766180 10800
因此,現在您從上面的兩個查詢中形成 2 個 CTE(公用表表達式)並執行以下 SQL:
SELECT SUBSTR(c1.truck_id, 1, 3) AS tid_1, SUBSTR(c2.truck_id, 1, 3) AS tid_2, SUBSTR(c1.location_name, 1, 3) as loc_1, SUBSTR(c2.location_name, 1, 3) AS loc_2, c2.loc_date_time AS ldt_1, c1.loc_date_time AS ldt_2, (EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) AS diff FROM cte1 c1 JOIN cte2 c2 ON c1.truck_id = c2.truck_id AND c1.location_name = c2.location_name WHERE ABS(EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) <= 3600 ORDER BY c2.loc_date_time;
結果:
tid_1 tid_2 loc_1 loc_2 ldt_1 ldt_2 diff Mon Mon Hom Hom 2019-10-22 10:27:40 2019-10-22 11:22:40 3300 Mon Mon Gas Gas 2019-10-22 12:43:00 2019-10-22 13:43:00 3600 Mon Mon Hom Hom 2019-10-22 16:43:00 2019-10-22 17:43:00 3600 Mon Mon Hom Hom 2019-10-22 20:43:00 2019-10-22 20:56:00 780
這是想要的結果!下面是完整的 SQL。它需要一些整理,但我會把它留給你 - 我已經把無關的東西留在裡面,以便(應該!)隨著解決方案的發展更容易遵循我的思維過程。
在這個執行緒中,即使我沒有回答所提出的問題,這可能有助於掌握
LAG()
andLEAD()
。您也可以查看同一執行緒中接受的答案。=============== 數據的完整 SQL 和 DDL 和 DML ================
SQL:
WITH cte1 AS ( SELECT t1.truck_id, t1.location_name, t1.loc_date_time FROM ( SELECT truck_id, location_name, loc_date_time, LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id ORDER BY loc_date_time) AS the_lead FROM truck_loc ) AS t1 WHERE t1.the_lead >= t1.loc_date_time + INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530 OR t1.the_lead IS NULL ORDER BY t1.loc_date_time ), cte2 AS ( SELECT t1.truck_id, t1.location_name, t1.loc_date_time, EXTRACT(EPOCH FROM t1.loc_date_time) AS t, EXTRACT(EPOCH FROM t1.the_lag) AS tlag, EXTRACT(EPOCH FROM t1.loc_date_time) - EXTRACT(EPOCH FROM t1.the_lag) AS diff FROM ( SELECT truck_id, location_name, loc_date_time, LAG (loc_date_time, 1) OVER (PARTITION BY truck_id, location_name ORDER BY loc_date_time) AS the_lag FROM truck_loc ) AS t1 WHERE t1.the_lag <= t1.loc_date_time - INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530 OR t1.the_lag IS NULL ORDER BY t1.loc_date_time ) SELECT SUBSTR(c1.truck_id, 1, 3) AS tid_1, SUBSTR(c2.truck_id, 1, 3) AS tid_2, SUBSTR(c1.location_name, 1, 3) as loc_1, SUBSTR(c2.location_name, 1, 3) AS loc_2, c2.loc_date_time AS ldt_1, c1.loc_date_time AS ldt_2, (EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) AS diff FROM cte1 c1 JOIN cte2 c2 ON c1.truck_id = c2.truck_id AND c1.location_name = c2.location_name WHERE ABS(EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) <= 3600 ORDER BY c2.loc_date_time;
DDL:
CREATE TABLE truck_loc ( truck_id VARCHAR(50), location_name VARCHAR(12), loc_date_time TIMESTAMP );
DML:
INSERT INTO truck_loc (truck_id, location_name, loc_date_time) VALUES ('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'), ('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'), ('MonsterTruck', 'Home', '2019-10-22 10:33:10.000'), ('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'), ('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'), ('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'), ('MonsterTruck', 'Gas Station', '2019-10-22 12:43:00.000'), ('MonsterTruck', 'Gas Station', '2019-10-22 13:13:00.000'), ('MonsterTruck', 'Gas Station', '2019-10-22 13:43:00.000'), ('MonsterTruck', 'Home', '2019-10-22 16:43:00.000'), ('MonsterTruck', 'Home', '2019-10-22 16:49:00.000'), ('MonsterTruck', 'Home', '2019-10-22 17:43:00.000'), ('MonsterTruck', 'Home', '2019-10-22 20:43:00.000'), ('MonsterTruck', 'Home', '2019-10-22 20:56:00.000');