sql-server - 确定 1 到 0 转换之间的时间

Question

我的表格显示泵的开/关状态如下

Value   timestamp
1       2013-09-01 00:05:41.987
0       2013-09-01 00:05:48.987
1       2013-09-01 00:05:59.987
0       2013-09-01 00:06:15.987
1       2013-09-01 00:06:34.987
etc etc. 

我需要一个 MSSQL 查询，该查询可能需要一个月的时间，并告诉我开启的分钟数 (1) 和关闭的分钟数 (0)，即占空比

Answer 1

使用CTE和RowNumber()函数 Fiddle demo :

declare @date date = '20130925'

;with cte as (
  select value, timestamp, row_number() over(order by timestamp) rn
  from table1
)
select c1.value, sum(datediff(second, c1.timestamp, c2.timestamp)) diffInSeconds
from cte c1 join cte c2 on c1.rn = c2.rn -1
where month(c1.timestamp) = month(@date) and month(c2.timestamp) = month(@date)
group by c1.value