TSQL查询有助于计算位标志从0变为1的记录之间的时间差

I need some help with a TSQL query. I am needing to calculate total time where the bit flag in my table below = '1'. My blocker here is there are millions of potential records and I am needing to base this off of one 'LogDate' column. So I assume I need to create a "Time Start" for when the bit flag changes from 0 to 1 and a "Time End" when it goes back from 1 to 0 then iterate through and do a datediff.

我需要一些TSQL查询的帮助。我需要计算下表中的位标志='1'的总时间。我的拦截器是有数百万的潜在记录，我需要将其基于一个“LogDate”列。因此，我假设我需要为位标志从0变为1时创建“时间开始”，并且当它从1返回到0时再创建“时间结束”，然后迭代并执行日期。

My challenge with only having basic SQL knowledge, this is out of my wheelhouse. Any magic that can be shared?

我的挑战只有基本的SQL知识，这是我的驾驶室。任何可以分享的魔法？

Need to group these starts/ends and do the arithmetic on total hh:mm:ss

需要对这些开始/结束进行分组并对总hh：mm：ss进行算术运算

Example data:

示例数据：

LogDate             StateBit
12/25/17 6:41:30    0
12/25/17 6:42:45    1 <-- Start time 
12/25/17 6:44:25    1
12/25/17 6:44:40    1
12/25/17 6:44:55    1
12/25/17 6:56:11    1
12/25/17 7:00:19    1
12/25/17 7:00:34    1
12/25/17 7:02:04    1
12/25/17 7:02:43    1
12/25/17 7:02:58    1 <-- End time 
12/25/17 7:03:15    0

3 个解决方案

#1

You can identify the groups as:

您可以将组标识为：

select t.*,
       sum(case when prev_bit = bit then 0 else 1 end) over (order by dt) as grp
from (select t.*,
             lag(bit) over (order by dt) as prev_bit
      from t
     ) t;

Then you can accumulate the periods for each group:

然后，您可以累积每个组的期间：

select min(dt), max(dt), bit
from (select t.*,
             sum(case when prev_bit = bit then 0 else 1 end) over (order by dt) as grp
      from (select t.*,
                   lag(bit) over (order by dt) as prev_bit
            from t
           ) t
     ) t
group by grp, bit;

I think you can use this information to get the total spans that you want.

我认为您可以使用此信息来获得所需的总跨度。

#2

I think You can use this way

我想你可以用这种方式

-- You need to have changed states rows and dates of before states in one row (the best choice is first rows of changed states
;with MakeLag as (select *,Lag(StateBit)over(order by id) ld,Lag(LogDate)over(order by LogDate)lDt  from #t )

--Next Step is to calculate seconds between this Two dates
,CalcSeconds as(select *,datediff(second,lDt,LogDate) as Dif from MakeLag where ld<>StateBit)

--And for finish you need to change these datediffs to "dd hh:mm:ss"
select *, CAST(FLOOR(Dif / 86400) AS VARCHAR(10))+'d ' +CONVERT(VARCHAR(8), DATEADD(SECOND, Dif, '19000101'), 8) as FinDiff from CalcSeconds

#3

What I suggest is quite simple, might have misunderstood your need, but if you filter according to a stateBit, just subtract the min and max.

我建议的很简单，可能误解了你的需要，但是如果根据stateBit进行过滤，只需减去最小值和最大值。

select max(logdate) - min (logdate) from tbl where StateBit = 1;

#1