我相信这个可以满足您的要求：
WITH FlowIntervals AS (
SELECT
FromTime = Min(D.[Downhole Time]),
X.ToTime
FROM
dbo.vLog D
OUTER APPLY (
SELECT TOP 1 ToTime = D2.[Downhole Time]
FROM dbo.vLog D2
WHERE
D.[Downhole Time] < D2.[Downhole Time]
AND D.[Flow] <> D2.[Flow]
ORDER BY D2.[Downhole Time]
) X
WHERE D.Flow = 1
GROUP BY X.ToTime
)
SELECT Sum(DateDiff(ms, FromTime, IsNull(ToTime, GetDate())) / 1000.0)
FROM FlowIntervals
;
这个查询适用于SQL 2005及以上版本。它的性能表现不错，但需要对vLog表进行自连接，因此可能无法像使用
LEAD或
LAG的解决方案那样表现得更好。
如果您正在寻求绝对最佳的性能，这个查询可能可以胜任：
WITH Ranks AS (
SELECT
Grp =
Row_Number() OVER (ORDER BY [Downhole Time])
- Row_Number() OVER (PARTITION BY Flow ORDER BY [Downhole Time]),
[Downhole Time],
Flow
FROM dbo.vLog
), Ranges AS (
SELECT
Result = Row_Number() OVER (ORDER BY Min(R.[Downhole Time]), X.Num) / 2,
[Downhole Time] = Min(R.[Downhole Time]),
R.Flow, X.Num
FROM
Ranks R
CROSS JOIN (SELECT 1 UNION ALL SELECT 2) X (Num)
GROUP BY
R.Flow, R.Grp, X.Num
), FlowStates AS (
SELECT
FromTime = Min([Downhole Time]),
ToTime = CASE WHEN Count(*) = 1 THEN NULL ELSE Max([Downhole Time]) END,
Flow = IsNull(Min(CASE WHEN Num = 2 THEN Flow ELSE NULL END), Min(Flow))
FROM Ranges R
WHERE Result > 0
GROUP BY Result
)
SELECT
ElapsedSeconds =
Sum(DateDiff(ms, FromTime, IsNull(ToTime, GetDate())) / 1000.0)
FROM
FlowStates
WHERE
Flow = 1
;
使用您提供的示例数据，它返回
631.486000（秒）。如果只选择来自
FlowStates CTE的行，则会得到以下结果：
FromTime ToTime Flow
----------------------- ----------------------- ----
2013-03-27 19:23:48.583 2013-03-27 19:32:16.070 1
2013-03-27 19:32:16.070 2013-03-29 14:43:55.070 0
2013-03-29 14:43:55.070 2013-03-29 14:45:59.070 1
2013-03-29 14:45:59.070 NULL 0
这个查询适用于SQL 2005及以上版本，性能非常好，可以与任何其他解决方案（包括使用
LEAD或
LAG的解决方案）相媲美，它通过一种巧妙的方式模拟了
LEAD或
LAG。我不能保证它一定会胜出，但它可能表现得非常出色，最终可能会胜出。
有关查询详细信息，请参见类似问题的答案。
最后，对于SQL Server，这里是一个完整的Lag/Lead版本：
WITH StateChanges AS (
SELECT
[Downhole Time],
Flow,
Lag(Flow) OVER (ORDER BY [Downhole Time]) PrevFlow
FROM
dbo.vLog
), Durations AS (
SELECT
[Downhole Time],
Lead([Downhole Time]) OVER (ORDER BY [Downhole Time]) NextTime,
Flow
FROM
StateChanges
WHERE
Flow <> PrevFlow
OR PrevFlow IS NULL
)
SELECT ElapsedTime = Sum(DateDiff(ms, [Downhole Time], NextTime) / 1000.0)
FROM Durations
WHERE Flow = 1
;
这个查询需要 SQL Server 2012 或更高版本。它计算状态的变化（流程是否改变？），然后选择那些流程确实发生了变化的记录，最后计算从0到1（流程开始时）流程发生变化的时间段的持续时间。
我很想看看你对此查询的I/O和时间的实际性能结果与其他查询相比如何。如果仅查看执行计划，这个查询似乎会胜出 - 但在实际性能统计上可能并不是如此明显的赢家。
Unique还是最早的Time？ -- “time difference for all rows, each consecutive pair” 指的是所有行之间的时间差异，每个连续的一对是什么意思？你能举个例子吗？ - Daniel Sparing