komplexe SQL-Abfrage

Loopo · 2. Juli 2011

Ich habe Daten in einer Postgres-DB, die angeben, wann ein Dienst am Server XY down war und wann er wieder zur Verfügung stand. Einfachhalber sieht die DB so aus:

outage.serviceid (integer)
outage.serverid (integer)
outage.downtime (datetime)
outage.uptime (datetime)

Ich will nun die Verfügbarkeiten mittels einer SQL-Abfrage oder Stored Procedure ermitteln. Die Herausforderung ist, dass ich zwei verschiede Verfügbarkeiten benötige: eine unbetreute von 19 Uhr bis 7 Uhr und die betreute von 7 Uhr bis 19 Uhr.

Jetzt gibt es natürlich das Problem der Überschneidung:

ein Service kann in der unbetreuten Zeit ausfallen und in der betreuten Zeit wieder on gehen bzw. umgekehrt
ein Service kann in der unbetreuten Zeit ausfallen und nach einer betreuten Zeit wieder on gehen bzw. umgekehrt
ein Service kann über mehr als einen Tag ausfallen
ein Service kann bis zur aktuellen Zeit down sein (hier könnte man annehmen, dass die Uptime NOW() ist)

Ich habe mir überlegt, dass man die Ausfallzeiten zuerst für jeden Tag zerschnippselt hinterlegt und diese dann in einem zweiten Schritt aufsummiert für jeden Tag: Also ein Service ist von 22 Uhr bis 17 Uhr ausgefallen, das heißt am ersten Tag werden 2 Stunden Ausfallzeit unbetreut gespeichert. Am zweiten Tag werden 7 Stunden Ausfallzeit unbetreut und 10 Stunden Ausfallzeit betreut gespeichert.

Vielleicht weiß ja jemand eine einfachere Lösung oder hat so ein Problem schon mal gelöst.

Eine SQL-Abfrage fürs zerschnippseln nach Tag könnte ja irgendwie so aussehen:

Code:

SELECT
	outage.serviceid,
	outage.serverid,
	MIN (Datum_23:59:59_Uhr from day_of_interest, outage.uptime) -	# Minus-Operator, falls uptime gleich Null uptime ist NOW() bzw. Ende des Tages
	MAX (Datum_0:00:00_Uhr from day_of_interest, outage.downtime),
FROM
	outage
WHERE
	outage.downtime <= Datum_23:59_Uhr
AND
	outage.uptime >= Datum_0:00_Uhr

Leicht abgeändert müsste man dies zwei mal für unbetreut und betreut machen. Wie würde zumindest obige (Postgre-)SQL-Abfrage korrekt aussehen?

AlbertLast · 2. Juli 2011

ich würde es sinvoller betrachten die tabellen struktur zu ändern,
in form eines logging struktur, daher sollte alle x zeitabständen ein eintrag in der tabelle drin sein.
Spalten könnten dann so aussehen:
Dienst, Datum/Uhrzeit, Läuft/läuft nicht, betreut/unbetreut
123, 1.3.2011 11:00, false, true

damit wäre die datenerhebung einfacher und du könntest glaube ich alle deine fragen beantworten.

wenn die stuktur angestrebt wird, sollte am besten in der tabelle oder in einer weiteren tabelle drin stehen wie groß x (der zeitabstand) ist.

Loopo · 2. Juli 2011

Die Datenbank kommt von OpenNMS und kann man daher nicht (so einfach) ändern.

AlbertLast · 2. Juli 2011

Dann erstell eine 2. tabelle und erstell ein skript welches in das andere format convertiert.

Edit:
Motivation hier für ist, das die daten so sehr einfach auszuwerten sind
und das nicht auf sql ebene sondern auch die db kann das schneller errechnen.

Loopo · 2. Juli 2011

Naja um das umwandeln der Daten geht es hier doch.

yxcv · 5. Juli 2011

Hallo Loopo,

hast du die Aufgabe schon lösen können?

Ich nutze kein PostgresSQL, aber laut Doku werden diese TSQL-Befehle unterstützt generell.

Deine Berechnung könnte also etwa mit dieser Funktion realisiert werden.

PHP:

CREATE FUNCTION [dbo].[fktBetreuteZeitBerechnung](@downtime datetime, @uptime datetime)
RETURNS datetime
BEGIN
    RETURN (@uptime - @downtime)  
			-(	
				( CASE WHEN	 CAST(@downtime   AS TIME) between '00:00:00'  and '06:59:59'  
						THEN CAST('07:00:00' AS DATETIME) - CAST(CAST(@downtime AS TIME) AS DATETIME)+'01.01.1900' ELSE '00:00:00 01.01.1900' END
				+ CASE WHEN	 CAST(@downtime   AS TIME) between '19:00:00'  and '23:59:59'
						THEN CAST('23:59:59' AS DATETIME) - CAST(CAST(@downtime AS TIME) AS DATETIME)+'01.01.1900' ELSE '00:00:00 01.01.1900' END
				)
			-	( CASE WHEN	 CAST(@uptime   AS TIME) between '00:00:00'  and '06:59:59'
						THEN CAST('07:00:00' AS DATETIME) - CAST(CAST(@uptime AS TIME) AS DATETIME)+'01.01.1900' ELSE '00:00:00 01.01.1900' END
				- CASE WHEN	 CAST(@uptime   AS TIME) between '19:00:00'  and '23:59:59'
						THEN CAST(CAST(@uptime AS TIME) AS DATETIME)+'01.01.1900' - CAST('19:00:00' AS DATETIME) ELSE '00:00:00 01.01.1900' END
				)
			)
END

Der Aufruf geht dann mit:

PHP:

WITH BspDaten 
   (  ID
   ,  serverId
   ,  downtime
   ,  uptime
   )  AS
   (			SELECT 1, 'pps1', CAST('02:00:00 05.07.2011' AS DATETIME), CAST('06:00:00 05.07.2011' AS DATETIME)
      UNION ALL SELECT 2, 'pps2', '06:00:00 05.07.2011', '08:00:00 05.07.2011'
      UNION ALL SELECT 3, 'pps2', '12:00:00 05.07.2011', '16:00:00 05.07.2011'
      UNION ALL SELECT 4, 'pps2', '17:00:00 05.07.2011', '20:00:00 05.07.2011'
      UNION ALL SELECT 5, 'pps2', '06:00:00 05.07.2011', '20:00:00 05.07.2011'
   )
SELECT 
	ID
   ,serverId
   ,downtime
   ,uptime
   ,Störungsdauer = uptime - downtime
   ,BetreuteZeit = dbo.fktBetreuteZeitBerechnung(downtime,ISNULL(uptime, GETDATE()))
FROM     BspDaten

Suche

komplexe SQL-Abfrage

Loopo

Admiral

AlbertLast

Lieutenant

Loopo

Admiral

AlbertLast

Lieutenant

Loopo

Admiral

yxcv

Gast

Anhänge

Ähnliche Themen