捕获字段状态更改的开始和结束时间
这是这个问题的变体来自单个列的 T-SQL 开始和结束日期时间，除了事件状态可能有多个打开或关闭状态而没有匹配的相反状态.
This is a variation of this question T-SQL Start and end date times from a single column except the event states may have multiple on or off states without a matching opposite state.
问题是我如何捕获第一个开"和下一个关"的开始和结束日期.换句话说，将每个项目的第一个 COS(状态更改)捕获为on"，并将第一个 COS 捕获为off".这将用于计算项目的总运行时间.
The question is how do I capture the start and end dates for the first "on" and next "off". In other words, capture the first COS (change of state) to "on" and the first COS to "off" for each item. This would be used to calculate a total runtime for the item.
来源:
Item EventDate Event
A 2011-10-03 00:01:00 On
A 2011-10-03 00:01:15 On
B 2011-10-03 00:01:00 On
A 2011-10-03 00:02:00 Off
A 2011-10-03 00:02:01 Off
C 2011-10-03 00:01:00 On
B 2011-10-03 00:02:00 Off
A 2011-10-03 00:02:02 On
C 2011-10-03 00:02:05 On
A 2011-10-03 00:02:07 Off
输出:
Item Start End
A 2011-10-03 00:01:00 2011-10-03 00:02:00
A 2011-10-03 00:02:02 2011-10-03 00:02:07
B 2011-10-03 00:01:00 2011-10-03 00:02:00
C 2011-10-03 00:01:00 2011-10-03 00:02:05
推荐答案
如果你有 SQL Server 2012 或更高版本，那么你可以使用 SQL Servers 窗口函数来获得所需的结果如下:
If you have SQL Server 2012 or later then you can use SQL Servers windowing function to get the desired results as follow:
CREATE TABLE [dbo].[EventStates](
[Item] [varchar](1) NOT NULL,
[EventDate] [varchar](19) NOT NULL,
[Event] [varchar](3) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'A', N'2011-10-03 00:01:00', N'On')
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'A', N'2011-10-03 00:01:15', N'On')
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'B', N'2011-10-03 00:01:00', N'On')
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'A', N'2011-10-03 00:02:00', N'Off')
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'A', N'2011-10-03 00:02:01', N'Off')
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'C', N'2011-10-03 00:01:00', N'On')
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'B', N'2011-10-03 00:02:00', N'Off')
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'A', N'2011-10-03 00:02:02', N'On')
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'C', N'2011-10-03 00:02:05', N'On')
INSERT [dbo].[EventStates] ([Item], [EventDate], [Event]) VALUES (N'A', N'2011-10-03 00:02:07', N'Off')
GO
;WITH StateChange
AS
(
SELECT E.[Item]
,E.[EventDate]
,E.[Event]
,( -- First determine if a state change to on has occurred.
CASE
WHEN E.[Event] = 'On' AND LAG(E.[Event], 1, NULL) OVER (PARTITION BY E.[Item] ORDER BY E.[EventDate] ASC) IS NULL THEN 1
WHEN E.[Event] = 'On' AND E.[Event] <> LAG(E.[Event], 1, NULL) OVER (PARTITION BY E.[Item] ORDER BY E.[EventDate] ASC) THEN 1
ELSE 0
END
) [StateChanged]
FROM EventStates E
), StateChangeGrouping
AS
(
SELECT [Item]
,[EventDate]
,[Event]
,[StateChanged]
,SUM([StateChanged]) OVER (PARTITION BY [Item] ORDER BY [EventDate] ASC) AS [GroupID]
FROM StateChange
), StateChangeRanked
AS
(
SELECT [Item]
,[EventDate]
,[Event]
,[StateChanged]
,[GroupID]
,ROW_NUMBER() OVER (PARTITION BY [Item], GroupID, [Event] ORDER BY [EventDate]) AS TransitionRank
FROM StateChangeGrouping
)
SELECT [Item]
,MIN([EventDate]) AS [Start]
,MAX([EventDate]) AS [End]
,[GroupID]
FROM StateChangeRanked
WHERE GroupID > 0 AND TransitionRank = 1
GROUP BY [Item], GroupID
ORDER BY [Item], [Start]
以下是 SQL Server 2008 的实现.
Below is an implementation for SQL Server 2008.
;WITH EventID
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [Item] ORDER BY [EventDate]) RowNr
,[Item]
,[EventDate]
,[Event]
FROM [dbo].[EventStates]
), StateChange
AS
(
SELECT C.RowNr
,P.RowNr AS P_RowNr
,C.[Item]
,C.[EventDate]
,C.[Event]
,P.[Event] AS P_Event
,( -- First determine if a state change to on has occurred.
CASE
WHEN C.[Event] = 'On' AND P.[Event] IS NULL THEN 1
WHEN C.[Event] = 'On' AND C.[Event] <> P.[Event] THEN 1
ELSE 0
END
) [StateChanged]
FROM EventID C
LEFT OUTER JOIN EventID P ON C.Item = P.Item AND C.RowNr = P.RowNr + 1
), StateChangeGrouping
AS
(
SELECT ST1.[Item]
,ST1.[EventDate]
,ST1.[Event]
,ST1.[StateChanged]
,(
SELECT SUM([StateChanged])
FROM StateChange ST2
WHERE ST2.Item = ST1.Item AND ST2.RowNr <= ST1.RowNr
) AS GroupID
FROM StateChange ST1
), StateChangeRanked
AS
(
SELECT [Item]
,[EventDate]
,[Event]
,[StateChanged]
,[GroupID]
,ROW_NUMBER() OVER (PARTITION BY [Item], GroupID, [Event] ORDER BY [EventDate]) AS TransitionRank
FROM StateChangeGrouping
)
SELECT [Item]
,MIN([EventDate]) AS [Start]
,MAX([EventDate]) AS [End]
,[GroupID]
FROM StateChangeRanked
WHERE GroupID > 0 AND TransitionRank = 1
GROUP BY [Item], GroupID
ORDER BY [Item], [Start]
相关文章