logo标签列表

合并重叠时间范围，然后计算总时间。

sqlsql-server
3

我有一个硬件组，其中包含许多设备。 例如：

+ Room 1
|-- Computer
|-- Camera
+ Room 2
|-- Computer
|-- Switch

所有设备都通过ping进行监控。当某个设备无法工作时，程序会在表格中添加一行，表示故障开始。当该设备重新启动时，程序会更新此行，表示故障结束。
知道每个设备的总停机时间是可以的。我需要知道整个组的实际累计时间。例如：
Group    Device     Start                 End
Room 1   Computer   2015-05-12 01:40:00   2015-05-12 01:40:20
Room 1   Camera     2015-05-12 01:40:01   2015-05-12 01:40:27
Room 2   Computer   2015-05-12 03:43:03   2015-05-12 03:46:14
Room 2   Switch     2015-05-12 03:43:00   2015-05-12 03:46:12
Room 1   Camera     2015-05-12 07:12:10   2015-05-12 07:12:22

“Room 1”这个分组的实际停机时间为39秒（不是58秒）。

01:40:00 - 01:40:20 = 20 seconds
01:40:01 - 01:40:27 = 26 seconds
07:12:10 - 07:12:22 = 12 seconds

关于前两行，为什么是27秒而不是46秒，请看下面的解释：
| 00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20                              |
|     01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27  |

嗯...我有很多组和每组很多设备。 如何使用SQL实现呢？

为了帮助测试...

DECLARE @tblGroup TABLE (
  id int,
  name varchar(20)
)
INSERT INTO @tblGroup (id, name)
  VALUES (1, 'Room 1'), (2, 'Room 2'), (3, 'Room 3'), (4, 'Room 4')

DECLARE @tblDevice TABLE (
  id int,
  name varchar(20),
  group_id int
)
INSERT INTO @tblDevice (id, name, group_id)
  VALUES (1, 'Computer', 1), (2, 'Camera', 1), (3, 'Computer', 2), (4, 'Switch', 2)

DECLARE @tblStatus TABLE (
  id int,
  device_id int,
  dtStart datetime,
  dtEnd datetime
)
INSERT INTO @tblStatus (id, device_id, dtStart, dtEnd)
  VALUES (1, 1, '2015-05-12 01:40:00.0', '2015-05-12 01:40:20.0'),
  (2, 2, '2015-05-12 01:40:01.0', '2015-05-12 01:40:27.0'),
  (3, 3, '2015-05-12 03:43:03.0', '2015-05-12 03:46:14.0'),
  (4, 4, '2015-05-12 03:43:00.0', '2015-05-12 03:46:12.0'),
  (5, 2, '2015-05-12 07:12:10.0', '2015-05-12 07:12:22.0')

SELECT
  s.id,
  s.device_id,
  g.name AS groupName,
  d.name AS deviceName,
  s.dtStart,
  s.dtEnd
FROM @tblStatus s
INNER JOIN @tblDevice d
  ON d.id = s.device_id
INNER JOIN @tblGroup g
  ON g.id = d.group_id
我认为你展示的第二行计算Group1时间差的公式有误。你应该是想表达01:40:20 - 01:40:27 = 7秒，而不是01:40:01 - 01:40:27 = 7秒，因为这个计算结果实际上是26秒，所以总时间差应该是58秒而不是你期望的39秒。我的理解正确吗？ - John Odom
我会编辑这篇文章... - Theo Fernandes
看这一行：“关于前两行，请看为什么是27秒而不是46秒：” - Theo Fernandes
4个回答
2
您希望将不同的组合成“岛屿”，并计算岛屿的范围。因此，这种类型的问题有时被称为间隙和岛屿。
让我假设您正在使用SQL Server 2012+。这稍微简化了计算过程。思路是确定重叠组的开始和结束。以下内容确定组是否存在重叠：
select t.*,
       (case when exists (select 1
                          from @tblstatus t2
                          where t2.group_id = t.group_id and
                                t2.dtend > t.dtstart and t2.dtstart <= t.dtstart and
                                t2.id < t.id
                         )
              then 0 else 1 end) as NoOverlapBefore
from @tblstatus t

通过这种方式，您可以为表中的每一行分配在其之前发生的“NoOverlapBefore”记录的数量，并将结果用于聚合：

with t as (
      select t.*,
             (case when exists (select 1
                                from @tblstatus t2
                                where t2.group_id = t.group_id and
                                      t2.dtend > t.dtstart and t2.dtstart <= t.dtstart and
                                      t2.id < t.id
                               )
                    then 0 else 1 end) as NoOverlapBefore
      from @tblstatus t
     )
select group_id,
       datediff(second, min(dtstart), max(dtend)) as total_seconds
from (select t.*,
             sum(NoOverlapBefore) over (partition by group_id order by dtstart, id) as grp
      from @tblstatus t
     ) t
group by group_id;

编辑：

我对您的数据结构有些误解。 SQL Fiddle是一个很大的帮助。 这里 是一个实际有效的SQL Fiddle。

查询语句如下：

WITH t AS (
      SELECT t.*, d.group_id,
             (CASE WHEN EXISTS (SELECT 1
                                FROM tblstatus t2 JOIN
                                     tbldevice d2
                                     ON d2.id = t2.device_id
                                WHERE d2.group_id = d.group_id AND
                                      t2.dtend > t.dtstart AND
                                      t2.dtstart <= t.dtstart AND
                                      t2.id <> t.id
                              )
                   THEN 0 ELSE 1
              END ) AS NoOverlapBefore
     FROM tblstatus t JOIN
          tblDevice d
          ON t.device_id = d.id
    )
SELECT group_id, SUM(total_seconds) as total_seconds
FROM (SELECT group_id, grp,
             DATEDIFF(SECOND, MIN(dtstart), MAX(dtend)) AS total_seconds
      FROM (SELECT t.*,
                   sum(t.NoOverlapBefore) over (partition BY group_id
                                                ORDER BY t.dtstart, t.id) AS grp
            FROM t
           ) t
      GROUP BY grp, group_id
     ) t
GROUP BY group_id;
Gordon，我正在尝试运行这段代码，但我认为我做错了什么。 - Theo Fernandes
错误："无法对包含聚合或子查询的表达式执行聚合函数。" - Theo Fernandes
太棒了，高登！这段代码运行得很好。 约翰的代码能跑，但是你的代码要快得多。 感谢约翰和非常感谢你。 - Theo Fernandes
1
有点复杂，但我有一个可行的解决方案。诀窍是改变数据呈现方式。
编辑：只要没有两个事件在同一时间在同一设备上发生，这个解决方案就有效。
我在这里留下了一个 SQL Fiddle: http://sqlfiddle.com/#!6/59e80/8/0 
declare @tblGroup table (id int, name varchar(20))
insert into @tblGroup (id, name) values (1, 'Room 1'), (2, 'Room 2'), (3, 'Room 3'), (4, 'Room 4')

declare @tblDevice table (id int, name varchar(20), group_id int)
insert into @tblDevice (id, name, group_id) values (1, 'Computer', 1), (2, 'Camera', 1), (3, 'Computer', 2), (4, 'Switch', 2)

declare @tblStatus table (id int, device_id int, dtStart datetime, dtEnd datetime)
insert into @tblStatus (id, device_id, dtStart, dtEnd) values
(1, 1, '2015-05-12 01:40:00.0', '2015-05-12 01:40:20.0'),
(2, 2, '2015-05-12 01:40:01.0', '2015-05-12 01:40:27.0'),
(3, 3, '2015-05-12 03:43:03.0', '2015-05-12 03:46:14.0'),
(4, 4, '2015-05-12 03:43:00.0', '2015-05-12 03:46:12.0'),
(5, 2, '2015-05-12 07:12:10.0', '2015-05-12 07:12:22.0');





WITH eventlist as
(select
    s.id,
    s.device_id,
    g.Id AS groupId,
    g.name as groupName,
    d.name as deviceName,
    s.dtStart AS dt,
    'GO_DOWN' AS eventtype,
    1 AS eventcount

from
    @tblStatus s
inner join
    @tblDevice d on d.id = s.device_id
inner join
    @tblGroup g on g.id = d.group_id
UNION
select
    s.id,
    s.device_id,
    g.Id AS groupId,
    g.name as groupName,
    d.name as deviceName,
    s.dtEND AS dt,
    'BACK_UP' AS eventtype,
     -1 AS eventcount
from
    @tblStatus s
inner join
    @tblDevice d on d.id = s.device_id
inner join
    @tblGroup g on g.id = d.group_id
),
breakdown AS(
SELECT 
    principal.groupId
    ,principal.groupName
    ,principal.dt
    ,principal.deviceName
    ,principal.eventtype
    ,was_broken = ISNULL(SUM(before.eventcount),0) 
    ,is_broken = ISNULL(SUM(before.eventcount),0) + principal.eventcount
    FROM  
eventlist principal 
LEFT JOIN  eventlist before ON before.groupId = principal.groupId 
AND 1 = CASE WHEN before.dt < principal.dt  THEN 1
               WHEN before.dt = principal.dt AND before.device_id < principal.device_id THEN 1 
              ELSE 0 END
GROUP BY 
         principal.eventcount
         ,principal.deviceName
        ,principal.eventtype
        ,principal.groupId
        ,principal.groupName
        ,principal.dt
)
,breakdownstart AS
( SELECT groupId,dt, r = RANK() OVER (PARTITION BY groupId ORDER BY dt) FROM breakdown WHERE was_broken = 0  AND is_broken =1 )
,breakdownend AS
( SELECT groupId,dt, r = RANK() OVER (PARTITION BY groupId ORDER BY dt) FROM breakdown WHERE was_broken = 1  AND is_broken = 0 )
,breakgroup as
(SELECT s.groupId
,s.r
, break_start = s.dt
, break_end = e.dt FROM breakdownstart s INNER JOIN breakdownend e ON e.r = s.r AND e.groupId = s.groupId)
SELECT groupId,SUM(DATEDIFF(SECOND,break_start,break_end)) AS break_length FROM breakgroup GROUP BY breakgroup.groupId
非常好，Johan！在我的情况下，我永远不会同时拥有同一设备的两条记录。所以这对我来说很好用。谢谢！ - Theo Fernandes
1
约翰，有265k行，这非常缓慢。也许下一步是找到一种优化的方法。 - Theo Fernandes
0

试试这个：

select
    g.id, SUM(DATEDIFF(SECOND, s.dtStart, s.dtEnd))
from
    @tblStatus s
    inner join  @tblDevice d on d.id = s.device_id
    inner join  @tblGroup g on g.id = d.group_id
group by
    g.id

你按 GroupId 进行分组，然后对于该组中的每个状态，你获取开始时间和结束时间之间的秒数差，并将其汇总到 GroupId 级别。
1
通过这种方式，我得到了58秒。我期望的是39秒。 - Theo Fernandes
0

我建议按照ID进行分组，这样才能得到时间差，然后再进行求和。

SELECT
    group.id, SUM(DATEDIFF(SECOND, status.dtStart, status.dtEnd))
FROM
    @tblStatus status
    inner join  @tblDevice device ON device.id = status.device_id
    inner join  @tblGroup group ON group.id = device.group_id
GROUP BY
    group.id
1
通过这种方式，我得到了58秒。我期望的是39秒。 - Theo Fernandes
这个答案是错误的，给出了58秒的第一组，而预期是39秒。 - Johan Buret
1
正如@JohnOdom已经提到的，我认为问题中的数学计算有误：“01:40:01 - 01:40:27 = 7秒”，实际上是26秒，因此时间差异需要更正。否则，我认为问题需要更好地解释。 - Juan
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接