Tabellen miteinander verknüpfen u. Queries geschickt formulieren

[Rülps]

Hallo, ich programmiere gerade für unsere Firma ein kleines Datenbanktool, das Abschaltungen dokumentieren soll. Ich hatte vorher noch nie mit Datenbanken zu tun gehabt. Im Wesentlichen parse ich bei Abschaltungen automatisch erstellte Logfiles und extrahiere Event, Datum, Zeit, etc. in eine SQLite Datenbank. Das Programm funktioniert soweit ganz gut. Ich benutze SQLite zusammen mit dem Sqlite 3 Wrapper von Tim Anderson.

Ich habe zwei Tabellen erstellt:

Die erste enthält Details über die Abschaltung, also genaue Uhrzeit, Dauer des Ausfalls, betroffenes System, etc.

Code:

Event     Date          Begin       Duration      System
-------------------------------------------------------------
11.506    18.07.2016    09:53       2             PowerSupply
#13       18.07.2016    14:23       15            Door
11.509    18.07.2016    17:02       3             PowerSupply

Die zweite Tabelle enthält pro Tag nur die Zeiten in der die Anlage lief:

Code:

Date          Begin     End
-----------------------------
18.07.2016    06:55     18:20
19.07.2016    06:20     17:55
20.07.2016    07:00     18:00

Das Ziel des Programms ist, dass man mit SQL Queries Downtimes abfragen kann, z.B. Fragestellungen wie wieviele PowerSupply Abbrüche gab es im Zeitraum vom 18.07. bis 20.07. und ähnliche. Wie gesagt ich bin totaler Anfänger und ich weiß nicht wie ich am Geschicktesten, die beiden Tabellen miteinander verknüpfe und wie man Suchanfragen so stellt, dass es mir die Resultate so schnell wie möglich liefert.

[jobo]

Also ich kann mir nicht so recht vorstellen, was da von Abschaltungen betroffen ist, Strom, Tür, ..`?
Dieses Ding was mir da fehlt, ist wahrscheinlich das Objekt, was Deine beiden Tabellen verbinden würde. Also ich sag einfach mal "der Gerät .. schläft nicht"
Wenn ein Gerät (anlage) mit einer ID erfasst ist (eine 3.Tabelle), dann würden sich die Einträge aus deinen beiden Listen jeweils mit dieser ID darauf beziehen.

Zu der Frage "schnell abfragen". Das ist so eine Sache.
Die Überlegungen sollte so ablaufen:
Priorität für das Datenmodell haben die Informationen, die erhoben werden müssen (Ausfälle,...) Dazu könntest Du Dir einfach mal das Thema Normalisierung anlesen.
Laufende Anlagen sind das Gegenteil von Ausfall, kommt mir irgendwie redundant vor. Es sei denn, Anlagen laufen auch mal so nicht, wegen Rüstzeiten, Auftragsmangel, Materialverknappung, .. oder sind das schon wieder Ausfälle
und man will natürlich so wenig wie möglich erfassen.

Was dann am Ende schnell abgefragt werden kann ist erstmal das was drin steht. Klingt trivial, ist es aber bei solchen Themen nicht unbedingt:
"Ich brauche eine Liste aller Ausfälle von Anlage B, während Anlage A nicht lief"
Was nicht in den Daten drin steht, ist etwas umständlicher und vielleicht auch langsamer abzufragen.

Am Ende helfen dann noch Indizes und ggF. Denormalisierung, wenn etwas ganz schnell sein muss.

[p80286]

Im Augenblick haben beide Tabellen nichts miteinander zu tun, da es in beiden Tabelle keine gemeinsamen Daten gibt, z.B. Maschinen-ID. "Da es sich ja nur um xy handelt ist das nicht notwendig" ist dann ein gern genutztes Argument. Dann hast Du aber auch nur eine Datensammlung, die nur für Personen verständlich ist, die alle weiteren Zusatzinformationen kennen.
Eine weitere Baustelle wäre das "Event". Hier bietet sich eine "Eventtable" mit den Feldern Eventid und Eventdescription an. Die z.B. den Vorteil hätte das Event mit mehr als 10,20 Zeichen zu beschreiben und trotzdem relativ wenig Platz zu benötigen.

Gruß
K-H

[Sir Rufo]

Die generelle Frage ist, ob man das Ein-/Auschalten nicht einfach mit in die Event-Tabelle schreibt (Es ist ja schließlich auch nur ein Ereignis) mit einer Dauer von null.

Eine zusätzliche Tabelle mit den Events und deren Beschreibung würde ich auch empfehlen, denn dort lassen sich dann auch noch bei Bedarf Meta-Informationen ablegen, die für die weitere Verarbeitung relevant sind.

Für ein konkrete Abfrage benötigt man natürlich auch das konkret gewünschte Ergebnis.

Für die angesprochene Fragestellung reicht für eine qualifizierte Aussage eine Abfrage nicht aus.

Ich erstelle mir für so etwas gerne eine/mehrere Klassen, die beschreiben, was ich denn eigentlich haben möchte. In diesem Fall wohl so etwas wie:

Delphi-Quellcode:

TSomeResult = class
public
  property QueryStart: TDateTime;
  property QueryDuration: TTimeSpan;
  property QueryEvents: TArray<string>;
  
  property SystemUptimeTotal: TTimeSpan;
  property DowntimeTotal: TTimeSpan;
  property SystemEvents: TList<TSystemEvent>;
end;

und das holt man sich dann über

Delphi-Quellcode:

function GetEvents( aFrom, aUntil: TDateTime ): TSomeResult;
function GetEvents( aFrom: TDateTime; aDuration: TTimeSpan ): TSomeResult;
function GetEvents( aFrom, aUntil: TDateTime; aMatchingEvents: array of string ): TSomeResult;
function GetEvents( aFrom: TDateTime; aDuration: TTimeSpan; aMatchingEvents: array of string ): TSomeResult;

[Rülps]

Danke für die Tipps!

Zitat von Sir Rufo:

Die generelle Frage ist, ob man das Ein-/Auschalten nicht einfach mit in die Event-Tabelle schreibt (Es ist ja schließlich auch nur ein Ereignis) mit einer Dauer von null.

Geniale Idee! Ich weiß schon, warum ich hier nachfrage
Thx, habt mir sehr geholfen!

[himitsu]

Es muß natürlich nicht unbedingt "physisch" mit in die erste Tabelle rein und wenn doch, dann könnte man sich die zweite Tabelle auch sparen, da man deren Daten aus der Ersten auslesen könnte.

SQL-Code:

-- Onlinezeiten je als ein Datensatz, von Beginn bis Ende
SELECT Event, Date, Begin, Begin+Duration AS End, Duration, System FROM EventTabelle
UNION
SELECT '...', Date, Begin, End, End-Begin AS Duration, 'Onlinetime' AS System FROM OnlineTabelle
ORDER BY Date, Begin, End

oder

SQL-Code:

-- Onlinezeiten je als zwei Datensätze, für Beginn und Ende
SELECT Event, Date, Begin, Begin+Duration AS End, Duration, System FROM EventTabelle
UNION
SELECT '...', Date, Begin, Begin AS End, 0 AS Duration, 'Online' AS System FROM OnlineTabelle
UNION
SELECT '...', Date, End AS Begin, End, 0 AS Duration, 'Offline' AS System FROM OnlineTabelle
ORDER BY Date, Begin, End

Beim Berechnen müsstest du dann nur mit überschneidenden Zeiten bissl aufpassen, also wo zwei Events übereinander liegen, vorallem bezüglich der "Online"-Zeiten, im ersten Beispiel.

Bezüglich der Duration müsste man noch aufpassen, dass hier auch mit der richtigen Zeiteinheit verrechnet wird, da das ja nur Integer sind und es Stunden/Minuten/Sonstwas sein könnten,
also vermutlich sowas wie (Begin + CAST(Duration || ' mins' AS INTERVAL)) AS End .

Im UNION SELECT muß das "... AS Feldname" nicht angegeben werden, aber man darf. (es werden die Namen des ersten SELECT in der Ausgabe stehen)

[p80286]

Zitat von himitsu:

Beim Berechnen müsstest du dann nur mit überschneidenden Zeiten bissl aufpassen, also wo zwei Events übereinander liegen, vorallem bezüglich der "Online"-Zeiten, im ersten Beispiel.

Ich denke das kommt darauf an.
Wenn das Mainsystem eingeschaltet wird ist das ein Event, die Frage ist ob die Eventdauer dann bis zum ausschalten geht, gibt es das Event "ausgeschaltet" als Beendigung von "eingeschaltet" und wenn es hierfür zwei Events gibt, gibt es für andere Events dann auch Beginn und Ende? Was man wählt ist meiner Meinung nach egal, nur mal so mal so halte ich für nicht so gelungen.
Auch wenn sich zwei Events überschneiden, halte ich das für nicht so schlimm, solange jeweils ein Subsystem betroffen ist. Interessant wird es wenn ein Subsystem gleichzeitig zwei Events hat, oder die Dauer eines Subsystemevents über den Ausschaltpunkt des Hauptsystems hinaus geht.

Gruß
K-H

[vagtler]

Zitat von Rülps:

[...] Im Wesentlichen parse ich bei Abschaltungen automatisch erstellte Logfiles und extrahiere Event, Datum, Zeit, etc. in eine SQLite Datenbank. [...] Wie gesagt ich bin totaler Anfänger und ich weiß nicht wie ich am Geschicktesten, die beiden Tabellen miteinander verknüpfe und wie man Suchanfragen so stellt, dass es mir die Resultate so schnell wie möglich liefert.

Also wenn ich so etwas lese, würde ich auf keinen Fall ans Selbermachen denken, sondern eher an eine etablierte Lösung wie Elastick Stack (https://www.elastic.co/webinars/introduction-elk-stack) oder Splunk (http://www.splunk.com/de_de/products/splunk-light.html) - um nur zwei zu nennen.