Sql-Server

具有 GPS 麵包屑數據的間隙和島嶼

  • November 24, 2019

我有一個縫隙和孤島(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()and LEAD()。您也可以查看同一執行緒中接受的答案。

=============== 數據的完整 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');

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

相關問答

Sql-Server

我可以只計算連續記錄的 ROW_NUMBER() 嗎？

  • July 20, 2018
檢視問答
Sql-Server

隨機錯誤 824 - 所有環境

  • August 17, 2022
檢視問答
Sql-Server

無法使用主機文件中的名稱連接到連結伺服器

  • August 1, 2022
檢視問答
Sql-Server

將遞歸 CTE 過濾為視圖

  • July 15, 2022
檢視問答
Sql-Server

同一 SQL Server 實例中的不同應用程序數據庫

  • June 29, 2022
檢視問答
Sql-Server

查詢以查找指定日期之間的活動天數（狀態 = ON 的天數）

  • June 25, 2022
檢視問答