上一個標誌後 2 小時出現的標誌行

由於天線過於敏感，我有一些混亂的 RFID 數據。有一個物理過程可以跟踪 RFID 標籤在一個循環中通過不同站點的移動。帶有 RFID 標籤的物品每天可以在循環中移動不止一次，但它不太可能在第一次讀取後的兩個小時視窗內開始循環。

我正在嘗試創建一個標誌列來確定一個項目的新周期何時開始，或者返回一個項目經歷了多少次週期的計數。

以下是一些範例數據：

CREATE TABLE [dbo].[samplerfiddata](
   [Item] [nvarchar](50) NOT NULL,
   [Station_Type] [nvarchar](50) NOT NULL,
   [Station_Name] [nvarchar](50) NOT NULL,
   [Timestamp] [datetime2](7) NOT NULL,
   [Trying_to_Create_this_Flag_Column] [nvarchar](50) NOT NULL
);

INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-10T06:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-11T14:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Washer', N'Washer', CAST(N'2020-10-11T14:45:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-11T15:15:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-11T23:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Washer', N'Washer', CAST(N'2020-10-12T00:15:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-12T00:45:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-13T16:00:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-13T16:30:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-14T13:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Other', N'Decontamination', CAST(N'2020-10-12T08:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-12T14:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Washer', N'Washer', CAST(N'2020-10-12T14:45:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-12T15:15:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-12T18:00:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Washer', N'Washer', CAST(N'2020-10-13T18:15:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-13T19:00:00.0000000' AS DateTime2), N'1')

在此數據中，我們有兩個不同的項目在循環中移動，並擷取了所有場景。新周期的業務邏輯定義為當一個項目被一個 RFID 天線（站）讀取時，其中站類型 = 去污或站名稱 = 去污，並且距離上一次循環的第一次讀取已經超過兩個小時.

最終，我試圖返回一個如下所示的結果集：

這怎麼可能完成？

在這種情況下，您可以利用LAG()函式來獲取以前的時間戳值。

從 SQL Server 2012 (11.x) 開始，不使用自聯接訪問同一結果集中前一行的數據。LAG 提供對位於目前行之前的給定物理偏移量的行的訪問。在 SELECT 語句中使用此分析函式將目前行中的值與前一行中的值進行比較。

然後只需使用條件總和，如果目前Timestamp>= 2 的 DATEDIFF 加 1，否則將 0 添加到按 . 分組的最終結果Item。

;WITH data AS
(
SELECT
 Item,
     CASE WHEN 
               DATEDIFF (hour, 
                         COALESCE(LAG(Timestamp) 
                                      OVER (PARTITION BY Item 
                                            ORDER BY Timestamp), 
                                  DATEADD(hour, -2, Timestamp)),
                         Timestamp) >= 2 THEN 1 ELSE 0 END AS lastTM
FROM
 samplerfiddata
)
SELECT
 Item,
 SUM(lastTM) as Cycles
FROM
 data
GROUP BY
 Item;

項目 | 循環
:--- | -----:
一個 | 5
乙| 4

db<>在這裡擺弄

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