Verknüpfung überlappende timebereiche, dann Summierung der Gesamtzeit

Ich habe eine Hardware-Gruppe und viele Geräte in diese Gruppe. Beispiel:

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

Alle Geräte werden mit Ping überwacht. Wenn ein Gerät nicht funktioniert das Programm fügen Sie eine Zeile in eine Tabelle sagen, der Beginn der Pause. Wenn das Gerät wieder auf, dann das Programm aktualisieren diese Zeile sagen, das Ende der Pause.

Es ist ok, die Gesamtsumme für jedes Gerät zu kennen. Mein Bedürfnis ist die richtige Summe aller Gruppen. Beispiel:

 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 

Die reale Ausfallzeit der Gruppe "Raum 1" beträgt 39 Sekunden (NICHT 58).

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

Über die beiden ersten Zeilen, casting Sie einen Blick, warum ist 27 Sekunden und nicht 46 Sekunden:

 | 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 | 

Nun … ich habe viele Gruppen und viele Geräte pro Gruppe. Wie kann ich es mit SQL verwenden?

Um auf Tests zu helfen …

 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 

Sie schauen, um die verschiedenen Gruppen zu "Inseln" zu kombinieren und die Ausdehnung der Inseln zu zählen. Deshalb wird diese Art von Problem manchmal auch Lücken und Inseln genannt.

Lassen Sie mich davon ausgehen, dass Sie SQL server 2012+ verwenden. Das vereinfacht leicht die Berechnungen. Die Idee ist, die Starts zu bestimmen und endet für überlappende Gruppen. Folgendes bestimmt, ob eine Gruppe Überschneidungen hat:

 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 

Mit diesem können Sie jeder Zeile in der Tabelle nur die Anzahl der "NoOverlapBefore" datasätze zuordnen, die vor ihm auftreten und das Ergebnis für die Aggregation verwenden:

 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; 

BEARBEITEN:

Ich habe ein paar Dinge über Ihre datastruktur missverstanden. Die SQL-Geige ist eine große Hilfe. Hier ist eine, die eigentlich funktioniert.

Die Abfrage ist:

 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; 

Ein bisschen verworren, aber ich habe eine funktionierende Lösung. Der Trick war, die datapräsentation zu verändern.

EDIT: Diese Lösung funktioniert so lange, wie es niemals zwei Ereignisse gibt, die gleichzeitig auf demselben Gerät stattfinden.

Ich habe hier eine SQL-Geige hinterlassen: 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 ,sr , break_start = s.dt , break_end = e.dt FROM breakdownstart s INNER JOIN breakdownend e ON er = sr AND e.groupId = s.groupId) SELECT groupId,SUM(DATEDIFF(SECOND,break_start,break_end)) AS break_length FROM breakgroup GROUP BY breakgroup.groupId 

Versuche dies:

 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 

Sie gruppieren nach GroupId, dann für jeden Status, den Sie auf dieser Gruppe haben, get den Unterschied in Sekunden zwischen der Startzeit und der Endzeit und SUM es aggregiert auf die GroupId-Ebene.

Ich schlage vor, gruppieren von id, das Ziel hier ist, den Unterschied zwischen den timeen zu bekommen, nur dann können Sie SUM.

 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