問題描述
我有 2 個表，其中包含一段時間內的類型化事件.
第一個表 #T1 包含的事件總是出現在第二個表 #T2 中的事件之前.
第三個表 #E 包含為事件定義分別出現在 #T1 和 #T2 中的值的記錄.>
示例數據:
創建表#T1(事件時間戳日期時間，VehicleId int,EventId varchar(50),事件值 varchar(50));創建表#T2(事件時間戳日期時間，VehicleId int,EventId varchar(50),事件值 varchar(50));創建表#E(EventId varchar(50),FirstValue int,LastValue varchar(50));INSERT INTO #T1(EventTimestamp, VehicleId, EventId, EventValue)值 (GETDATE(), 1, 'TwigStatus', '12'),(GETDATE(), 2, 'SafeProtectEvent', '5')INSERT INTO #T2(EventTimestamp, VehicleId, EventId, EventValue)值 (DATEADD(second, 30, GETDATE()), 1, 'TwigStatus', '7'),(DATEADD(second, 30, GETDATE()), 2, 'SafeProtectEvent', '6')插入 #E(EventId, FirstValue, LastValue)值 ('TwigStatus', '12', '7'),('SafeProtectEvent', '5', '6')聲明 @EventId varchar(50) = 'TwigStatus';聲明@FirstValue varchar(50) = '12';聲明@LastValue varchar(50) = '7';使用命令 AS(選擇首先，最后，EventNr = ROW_NUMBER() OVER (ORDER BY first)從(選擇第一個 = t1.EventTimestamp，最后一個 = t2.EventTimestamp，rn = ROW_NUMBER() OVER (PARTITION BY t1.VehicleId ORDER BY t2.EventTimestamp)從#T1 t1內部聯接#T2 t2 ON t2.EventTimestamp >t1.EventTimestampAND t2.EventValue = @LastValue在哪里t1.EventId = @EventId AND t1.EventValue = @FirstValue) ids在哪里rn = 1)選擇t.VehicleId, o.first, o.last, t.EventId, t.EventValue從#T2噸內部聯接ord o ON t.EventTimestamp >= o.firstAND t.EventTimestamp <= o.last;WHERE t.EventId = @EventId;刪除表#E；刪除表#T1；刪除表#T2；基本上，對于表 E 中的記錄，您會看到對于 eventID 'TwigStatus'，值 '12' 應首先出現在表 T1 中，然后是表 T2 中的下一個值 '7'.定義了第二個事件序列.
VehicleId 列是表 T1 和 T2 之間的鏈接.
我需要計算表 T1 和 T2 中兩個匹配事件之間的延遲.
為了簡單起見，我還沒有使用表 E，我使用的是包含預定義值的變量并且我正在返回時間戳.
但是上面查詢的結果；
VehicleId first last EventId EventValue1 2020-09-15 16:00:37.670 2020-09-15 16:01:07.670 TwigStatus 72 2020-09-15 16:00:37.670 2020-09-15 16:01:07.670 SafeProtectEvent 6這不是我所期望的，因為現在應該過濾掉 EventId 'SafeProtectEvent'.
所以我有兩個問題:
- 如何避免在實際查詢中顯示第二個事件.
- 如何處理表 E 的內容并擺脫變量來處理事件序列.
編輯 1:問題 1 通過對查詢添加限制來解決(見上文)
下面的更新/新版本 - 現在允許 T1 中的行而不匹配 T2 中的行.
根據對以下評論的討論，我更新了此建議.
此代碼替換了從 DECLARE @EventId 到 SELECT 語句結尾的所有內容.
邏輯如下——對于T1中的每一行...
- 確定 T1 中該行的時間邊界(在其 EventTimestamp 與該車輛的 T1 中的下一個 EventTimestamp 之間；如果沒有下一個事件，則為未來 1 天)
- 在 T2 中找到匹配的行，其中匹配"意味著 a) 相同的 VehicleId，b) 相同的 EventId，c) EventValue 受#E 中可能性的限制，并且 d) 發生在 T1 的時間邊界內
- 查找這些行中的第一行(如果有)
- 將 EventDelay 計算為兩個時間戳之間的時間
<代碼>;與 t1 AS(選擇車輛 ID，事件時間戳，事件 ID，事件值，COALESCE(LEAD(EventTimestamp, 1) OVER (PARTITION BY VehicleID ORDER BY EventTimestamp), DATEADD(day, 1, getdate())) AS NextT1_EventTimeStamp從#T1),訂單AS(選擇 t1.VehicleId，t1.EventTimestamp AS 首先，t2.EventTimestamp AS 最后，t1.EventId,t2.EventValue,ROW_NUMBER() OVER (PARTITION BY t1.VehicleId, t1.EventTimestamp, t1.EventId ORDER BY t2.EventTimestamp) AS rn從 t1LEFT OUTER JOIN #E AS e ON t1.EventId = e.EventIdAND t1.EventValue = e.FirstValue左外連接#T2 AS t2 ON t1.VehicleID = t2.VehicleIDAND t1.EventID = t2.EventIDAND t2.eventId = e.EventIdAND t2.EventValue = e.LastValueAND t2.EventTimestamp >t1.EventTimestampAND t2.EventTimestamp
不斷增長的DB<>fiddle 更新以及原始帖子和以前的建議.
I have 2 tables that contains typed events over time.
The first table #T1 contains events that always comes before events in the second table #T2.
A third table #E contains records that defines for an event the values that comes in #T1 and #T2 respectively.
Sample data:
CREATE TABLE #T1
(
EventTimestamp DateTime,
VehicleId int,
EventId varchar(50),
EventValue varchar(50)
);
CREATE TABLE #T2
(
EventTimestamp DateTime,
VehicleId int,
EventId varchar(50),
EventValue varchar(50)
);
CREATE TABLE #E
(
EventId varchar(50),
FirstValue int,
LastValue varchar(50)
);
INSERT INTO #T1(EventTimestamp, VehicleId , EventId, EventValue)
VALUES (GETDATE(), 1, 'TwigStatus', '12'),
(GETDATE(), 2, 'SafeProtectEvent', '5')
INSERT INTO #T2(EventTimestamp, VehicleId , EventId, EventValue)
VALUES (DATEADD(second, 30, GETDATE()), 1, 'TwigStatus', '7'),
(DATEADD(second, 30, GETDATE()), 2, 'SafeProtectEvent', '6')
INSERT INTO #E(EventId, FirstValue, LastValue)
VALUES ('TwigStatus', '12', '7'),
('SafeProtectEvent', '5', '6')
DECLARE @EventId varchar(50) = 'TwigStatus';
DECLARE @FirstValue varchar(50) = '12';
DECLARE @LastValue varchar(50) = '7';
WITH ord AS
(
SELECT
first, last,
EventNr = ROW_NUMBER() OVER (ORDER BY first)
FROM
(SELECT
first = t1.EventTimestamp, last = t2.EventTimestamp,
rn = ROW_NUMBER() OVER (PARTITION BY t1.VehicleId ORDER BY t2.EventTimestamp)
FROM
#T1 t1
INNER JOIN
#T2 t2 ON t2.EventTimestamp > t1.EventTimestamp
AND t2.EventValue = @LastValue
WHERE
t1.EventId = @EventId AND t1.EventValue = @FirstValue) ids
WHERE
rn = 1
)
SELECT
t.VehicleId, o.first, o.last, t.EventId, t.EventValue
FROM
#T2 t
INNER JOIN
ord o ON t.EventTimestamp >= o.first
AND t.EventTimestamp <= o.last;
WHERE t.EventId = @EventId;
DROP TABLE #E;
DROP TABLE #T1;
DROP TABLE #T2;
Basically, for a record in table E you see that for eventID 'TwigStatus' the value '12' should come first in table T1 and then '7' should be next in table T2. There is a second event sequence that is defined.
The VehicleId column is the link between the tables T1 and T2.
I need to compute the delay between two matching events in table T1 and T2.
To start simple, I do not use the table E yet, I'm using variables that contains predefined values and I'm returning timestamps.
But the result of the query above;
VehicleId first last EventId EventValue
1 2020-09-15 16:00:37.670 2020-09-15 16:01:07.670 TwigStatus 7
2 2020-09-15 16:00:37.670 2020-09-15 16:01:07.670 SafeProtectEvent 6
Is not what I'm expecting because the EventId 'SafeProtectEvent' Should be filtered out for now.
So I have 2 questions:
- How to avoid the second event to show with the actual query.
- How to work with the content of the table E and get rid of variables to process event sequences.
EDIT 1: Problem 1 Solved by adding a restriction on the query (see above)
Update/new version below - now allows rows in T1 without matching rows in T2.
Based on discussion on comments below, I have updated this suggestion.
This code replaces everything from the DECLARE @EventId to the end of that SELECT statement.
Logic is as follows - for each row in T1 ...
- Determine the time boundaries for that row in T1 (between its EventTimestamp, and the next EventTimestamp in T1 for that vehicle; or 1 day in the future if there is no next event)
- Find the matching rows in T2, where 'matching' means a) same VehicleId, b) same EventId, c) EventValue is limited by possibilities in #E, and d) occurs within the time boundaries of T1
- Find the first of these rows, if available
- Calculate EventDelay as the times between the two timestamps
; WITH t1 AS
(SELECT VehicleId,
EventTimestamp,
EventId,
EventValue,
COALESCE(LEAD(EventTimestamp, 1) OVER (PARTITION BY VehicleID ORDER BY EventTimestamp), DATEADD(day, 1, getdate())) AS NextT1_EventTimeStamp
FROM #T1
),
ord AS
(SELECT t1.VehicleId,
t1.EventTimestamp AS first,
t2.EventTimestamp AS last,
t1.EventId,
t2.EventValue,
ROW_NUMBER() OVER (PARTITION BY t1.VehicleId, t1.EventTimestamp, t1.EventId ORDER BY t2.EventTimestamp) AS rn
FROM t1
LEFT OUTER JOIN #E AS e ON t1.EventId = e.EventId
AND t1.EventValue = e.FirstValue
LEFT OUTER JOIN #T2 AS t2 ON t1.VehicleID = t2.VehicleID
AND t1.EventID = t2.EventID
AND t2.eventId = e.EventId
AND t2.EventValue = e.LastValue
AND t2.EventTimestamp > t1.EventTimestamp
AND t2.EventTimestamp < NextT1_EventTimeStamp
)
SELECT VehicleId, first, last, EventId, EventValue,
DATEDIFF(second, first, last) AS EventDelay
FROM ord
WHERE rn = 1
The ever-growing DB<>fiddle has the latest updates, as well as original posts and previous suggestions.
這篇關于SQL Server 2014:根據來自第三個表的值配對來自 2 個表的行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！