問(wèn)題描述

這是這個(gè)問(wèn)題的變體來(lái)自單個(gè)列的 T-SQL 開始和結(jié)束日期時(shí)間，除了事件狀態(tài)可能有多個(gè)打開或關(guān)閉狀態(tài)而沒有匹配的相反狀態(tài).

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.

問(wèn)題是我如何捕獲第一個(gè)開"和下一個(gè)關(guān)"的開始和結(jié)束日期.換句話說(shuō)，將每個(gè)項(xiàng)目的第一個(gè) COS(狀態(tài)更改)捕獲為on"，并將第一個(gè) COS 捕獲為off".這將用于計(jì)算項(xiàng)目的總運(yùn)行時(shí)間.

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.

來(lái)源:

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 窗口函數(shù)來(lái)獲得所需的結(jié)果如下:

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 的實(shí)現(xiàn).

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]

這篇關(guān)于捕獲字段狀態(tài)更改的開始和結(jié)束時(shí)間的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！