需要 Sql 查询才能根据状态获取时差

Question

下面是示例数据

我需要当Ebstatus和dgstatus为打开并直到下一个DgStatus为关闭的总时间。

例如，如果我们考虑以下数据：

Ebstatus  | dgstatus | ReadTime
ON  |  ON  | 16/07/2017  3:00:00  
ON  | ON   | 16/07/2017  4:00:00  
ON  | OFF  | 16/07/2017  5:00:00
ON  | OFF  | 16/07/2017  6:00:00
ON  | ON   | 16/07/2017  7:00:00
ON  | OFF  | 16/07/2017  9:00:00

这里的总开启时间为 2 小时(3 到 4 -> 1 小时，直到 5 小时它仍然打开，所以总开启时间为 2 小时(，并且两个状态在 7 点再次打开，Dgstatus在 9 点关闭，所以这里的总开启时间为 2 小时。

最后总导通时间为 4 小时

有人可以提供SQL Server 2012的查询吗？

Answer 1

如果你能减少到每ON一行，OFF，那么聚合就可以解决这个问题。 您可以使用行号差异方法执行此操作：

select ebstatus, dgstatus,
min(readtime) as min_rt, max(readtime) as max_rt
from (select t.*,
row_number() over (partition by ebstatus order by readtime) as seqnum_d,
row_number() over (partition by ebstatus, dgstatus partition by readtime) as seqnum_sd
from t
) t
group by ebstatus, dgstatus, (seqnum_sd - seqnum_d);

由此，您可以获得总计：

select sum(datediff(hour, min_rt, next_min_rt)) as on_hours
from (select ebstatus, dgstatus,
min(readtime) as min_rt, max(readtime) as max_rt,
lead(min(readtime)) over (partition by ebstatus, dgstatus order by min(readtime)) as next_min_rt
from (select t.*,
row_number() over (partition by ebstatus order by readtime) as seqnum_d,
row_number() over (partition by ebstatus, dgstatus partition by readtime) as seqnum_sd
from t
) t
group by ebstatus, dgstatus, (seqnum_sd - seqnum_d)
) t
where dgstatus = 'ON' and ebstatus = 'ON';

Answer 2

LAG 允许您检查上一行，以便您可以检查 dgstatus v alue 的变化

SET DATEFORMAT DMY;
DECLARE @Mytable table (Ebstatus varchar(2), dgstatus varchar(3), ReadTime smalldatetime)
INSERT @Mytable VALUES
('ON', 'ON ', '16/07/2017  3:00:00'),  
('ON', 'ON ', '16/07/2017  4:00:00'),  
('ON', 'OFF', '16/07/2017  5:00:00'),
('ON', 'OFF', '16/07/2017  6:00:00'),
('ON', 'ON ', '16/07/2017  7:00:00'),
('ON', 'OFF', '16/07/2017  9:00:00');
SELECT 
SUM(Hours)
FROM 
(
SELECT 
*, 
Hours = CASE 
WHEN 
Ebstatus = 'ON' AND 
dgstatus = 'OFF' AND --may be needed to ensure we only count OFF rows
LAG(dgstatus) OVER (ORDER BY ReadTime) <> dgstatus 
THEN
DATEDIFF(HOUR, LAG(ReadTime) OVER (ORDER BY ReadTime), ReadTime)
ELSE 0 END
FROM 
@Mytable
) X;