计算同一行中的时间差动态

问题描述：

有什么办法来基于“DOWN”和“UP”值这样的同一列中计算行之间在SQL的时间差：计算同一行中的时间差动态

有3个场景（即我所知道的）：

黄色，橙色和绿色：有一个STATE_ID 2（向下），之后一个STATE_ID 5（时），所以时差需求在两行之间进行计算;
蓝色：有多个state_id 2（向下），之后有一个state_id 5（向上），所以需要在第一行和最后一行之间计算时间差;
红色：只有一个state_id 2（向下），因为它仍然随任何更新而关闭，所以需要计算时间差直到月末。

我希望你能帮助我。

您正在使用哪个版本的SQL Server？ –

答

首先考虑为此使用LAG。

但使用累积性SUM和MIN的窗口版本的作品也超过2 DOWN的：

-- test reference data 
declare @State table (id int, state varchar(4)); 
insert into @State (id, state) values 
(2,'DOWN'), 
(5,'UP') 

-- test data, using a table variable 
declare @AlertState table (alert_id int identity(1,1), host_id int, state_time datetime, state_id int); 
insert into @AlertState (host_id, state_time, state_id) values 
(119, GetDate()-0.32, 2), 
(119, GetDate()-0.31, 5), 
(119, GetDate()-0.24, 2), 
(119, GetDate()-0.23, 2), 
(119, GetDate()-0.22, 2), 
(119, GetDate()-0.21, 5), 
(119, GetDate()-0.15, 5), 
(119, GetDate()-0.11, 2); 

-- The query 
select alert_id, host_id, state_time, state_id, 
diff_min = (
    case 
    when state_id = 5 then 
    datediff(minute, min(state_time) over (partition by host_id, stategroup), state_time) 
    when state_id = 2 and stategroup is null then 
    datediff(minute, state_time, cast(EOMONTH(GetDate()) as datetime)+1) 
    end), 
s.state 
from (
    select alert_id, host_id, state_time, state_id, 
    sum(case state_id when 5 then 1 end) over (partition by host_id order by state_time desc) as stategroup 
    from @AlertState 
    where state_id in (2,5) 
) q 
left join @State s on s.id = q.state_id 
order by state_time, alert_id;

答

我这样做之前

Select a.state_time as downtime, 
    (
     select min(inner.state_time) from tablename downentry where 
     inner.state_time > outer.state_time and downentry.state='UP' 
    ) as uptime 

from tablename upentry 
where state = 'DOWN'

然后，你需要找到它们之间的DATEDIFF，如果正常运行时间为null，停机时间和“endofmonth”

之间的DATEDIFF这可能相当差的方式因此我总是将答案写入数据仓库，但认为它会提供您要求的结果。

答

SQL2012 +

你可以尝试以下解决方案：

SELECT y.group_id, host_id = MIN(host_id), start_time = MIN(state_time), end_time = MAX(state_time), diff_minute = DATEDIFF(MINUTE, MIN(state_time), MAX(state_time)) 
FROM (
    SELECT *, group_id = SUM(x.new_group_start) OVER(ORDER BY x.host_id, x.state_time) 
    FROM (
     SELECT *, new_group_start = IIF(a.state_id = 'DOWN' AND ISNULL(LAG(a.state_id) OVER(ORDER BY a.host_id, a.state_time), 'UP') = 'UP', 1, 0) 
     FROM @Alerts a 
    ) x 
) y 
GROUP BY y.group_id 
ORDER BY y.group_id

Demo

计算同一行中的时间差动态

相关推荐