SQL查找重叠的时间段和子故障

Question

长期跟踪者，首次发布者(和SQL初学者)。我的问题类似于此SQL to find time elapsed from multiple overlapping intervals，除了我能够使用CTE，UDF等并且正在寻找更多详细信息。

在一件大型设备上，我记录了所有出现的故障。故障可能出现在系统的不同子组件上，有些可能使它完全脱机(完全中断=是)，而另一些则没有(完全中断=否)。故障可能会在时间上重叠，如果故障尚未修复，则可能没有结束时间。

Outage_ID     StartDateTime     EndDateTime     CompleteOutage
1             07:00 3-Jul-13    08:55 3-Jul13   Yes
2             08:30 3-Jul-13    10:00 4-Jul13   No
3             12:00 4-Jul-13                    No
4             12:30 4-Jul13     12:35 4-Jul-13  No


1 |---------|
2    |---------|
3                 |--------------------------------------------------------------
4                      |---|

我需要能够确定一个用户定义的时间段，整个系统可以正常运行多长时间(无故障)，其降级多长时间(一个或多个不完整的中断)，以及无法运行多长时间(一个或多个完整的故障)停机)。我还需要能够在任何给定的时间段内找出系统上的故障。我本想在每次打开或关闭故障时都创建一个“阶段更改”表，但是我坚持执行此操作的最佳方法-此方法或更好的解决方案的任何帮助将不胜感激!

Answer 1

这不是一个完整的解决方案(我将其保留为练习:))，但应说明基本技术。诀窍是创建一个状态表(如您所说)。如果您为“开始”事件记录1，为“结束”事件记录-1，那么事件日期/时间顺序的运行总计将为您提供该特定事件日期/时间的当前状态。下面的SQL是T-SQL，但应易于适应您使用的任何数据库服务器。

以部分中断的数据为例:

DECLARE @Faults TABLE (
    StartDateTime DATETIME NOT NULL,
    EndDateTime DATETIME NULL
)
INSERT INTO @Faults (StartDateTime, EndDateTime)
    SELECT '2013-07-03 08:30', '2013-07-04 10:00'
    UNION ALL SELECT '2013-07-04 12:00', NULL
    UNION ALL SELECT '2013-07-04 12:30', '2013-07-04 12:35'

-- "Unpivot" the events and assign 1 to a start and -1 to an end
;WITH FaultEvents AS (
    SELECT *, Ord = ROW_NUMBER() OVER(ORDER BY EventDateTime)
        FROM (
            SELECT EventDateTime = StartDateTime, Evt = 1
                FROM @Faults
            UNION ALL SELECT EndDateTime, Evt = -1
                FROM @Faults
                WHERE EndDateTime IS NOT NULL
        ) X
)
-- Running total of Evt gives the current state at each date/time point
, FaultEventStates AS (
    SELECT A.Ord, A.EventDateTime, A.Evt, [State] = (SELECT SUM(B.Evt) FROM FaultEvents B WHERE B.Ord <= A.Ord)
        FROM FaultEvents A
)
SELECT StartDateTime = S.EventDateTime, EndDateTime = F.EventDateTime
    FROM FaultEventStates S
        OUTER APPLY (
            -- Find the nearest transition to the no-fault state
            SELECT TOP 1 *
                FROM FaultEventStates B
                WHERE B.[State] = 0
                    AND B.Ord > S.Ord
                ORDER BY B.Ord
        ) F
    -- Restrict to start events transitioning from the no-fault state
    WHERE S.Evt = 1 AND S.[State] = 1

如果您使用的是SQL Server 2012，则可以选择使用 windowing function计算运行总计。