Überprüfung auf Zeitbereichsüberlappung, das Wächterproblem [SQL]
Ich stoße bei einem größeren Problem in eine Straßensperre.
Als Teil einer großen Abfrage muss ich ein "Nachtwächter" -Problem lösen. Ich habe eine Tabelle mit Zeitplan Verschiebungen als solche:
%Vor%Als Teil einer Abfrage muss ich feststellen, ob in einem Raum jederzeit für einen bestimmten Zeitraum mindestens ein Wachmann ist.
Wenn ich also den Bereich 2009-1-1 06:00 bis 2009-1-1 12:00 angegeben habe, ist das Ergebnis wahr, weil die Schichten 1 und 2 zusammenlaufen, um diesen Zeitraum abzudecken - tatsächlich könnte eine beliebige Anzahl von Schichten verkettet werden, um die Überwachung aufrecht zu erhalten. Wenn ich jedoch 2009-2-1 22:00 auf 2009-1-2 10:00 überprüft habe, ist das Ergebnis falsch, weil zwischen 4 und 6 Uhr am nächsten Morgen eine Pause besteht.
Ich möchte dies entweder in LINQ oder als eine benutzerdefinierte Funktion in SQL Server (2005) implementieren, da dies in beiden Fällen nur ein Teil der Logik einer größeren Abfrage ist muss ausgeführt werden, um Elemente zu identifizieren, die Aufmerksamkeit benötigen. Der reale Datensatz enthält etwa hundert Schichtdatensätze, die sich über einen bestimmten Zeitraum schneiden, aber nicht immer den gesamten Bereich abdecken.
Der nächste, den ich gefunden habe, ist Gruppieren von Entfernungswerten mithilfe von SQL Server für Nummernbereiche, jedoch hängt es von jedem Bereich ab, der endet, bevor der nächste Bereich beginnt. Wenn ich die gleiche vereinheitlichte Ansicht der Uhren konstruieren könnte und nur überlappende Uhren in Betracht ziehen würde, wäre es trivial, zu überprüfen, ob eine bestimmte Zeit abgedeckt war. Eine einheitliche Ansicht würde folgendermaßen aussehen:
%Vor%Hinweis: Diese ganze Sache wäre relativ einfach zu implementieren, indem man einfach alle Daten zieht und eine manuelle Schleife ausführt, wie auch immer das aktuelle System ist, und es ist wegen der Anzahl der Verschiebungen und der Zeit ziemlich langsam Bereiche, die überprüft werden müssen.
4 Antworten
Hier können Sie den Datumsbereich wie folgt abflachen
%Vor%Sie müssen vorherige und nächste Daten in jeder Zeile vergleichen und sehen, ob
- Das Datum der Start der aktuellen Zeile liegt zwischen dem Datumsbereich der vorherigen Zeile.
- Das Datum der Ende der aktuellen Zeile liegt zwischen dem Datumsbereich der nächsten Zeile.
Mit dem obigen Code ist die Implementierung von UDF so einfach wie folgt.
%Vor%Ein unbewachtes Intervall beginnt offensichtlich entweder am Ende eines Beobachtungszeitraums oder zu Beginn des gesamten Zeitbereichs, den Sie überprüfen. Sie benötigen also eine Abfrage, die alle Elemente aus dieser Menge auswählt, die keine überlappende Verschiebung haben. Die Abfrage würde wie folgt aussehen:
%Vor%Wenn dies nicht leer ist, haben Sie ein unbewachtes Intervall. Ich vermute, dass es in quadratischer Zeit läuft, also könnte es langsamer sein als "sort, fetch and loop".
Eine Möglichkeit besteht darin, eine temporäre Tabelle mit einer Zeile für jeden zu prüfenden Zeitwert zu erstellen (was eine Funktion der Auflösung Ihrer Schichten ist).
Wenn es Minuten wären, hätte es 60 * 24 = 1440 Zeilen für einen Tag; ungefähr 10K Reihen für eine Woche.
Dann ist das SQL relativ einfach:
SELECT COUNT (1)
VON #minutes m
LEFT JOIN verschiebt s ON m.checktime BETWEEN s.start_time AND s.end_time
HABEN COUNT (1) = 0
Dies hat den Vorteil, dass Sie auch zeigen können, wie viele Schichten gleichzeitig ablaufen.
Die Ausführungszeit sollte in Anbetracht der von Ihnen beschriebenen Skalen vernachlässigbar sein.
Ich habe mir Datumsbereiche angesehen und dachte, ich würde diese Frage noch einmal besuchen. Ich könnte hier flach auf mein Gesicht fallen, aber es scheint, dass diese beiden Bedingungen genug wären
%Vor%Schätzen Sie, dass dies möglicherweise nicht die effizienteste ist.
%Vor%Ergebnismenge
%Vor%Eigentlich ist es nur notwendig, entweder die rechten Nachbarn oder die linken Nachbarn zu überprüfen, solange Sie sicherstellen, dass der Anfang und das Ende des Bereichs markiert sind, so dass Sie den Anfang der Entfernung als Dummy-Intervall einführen können rechte Nachbarn wie folgt: -
%Vor%Ergebnismenge
%Vor%Tags und Links sql algorithm linq-to-sql sql-server-2005 tsql