Diese Herausforderung stammt aus den Bereichen Kapazitätsplanung und Ressourcenallokation, insbesondere in Systemen wie Hotelreservierungsplattformen, Cloud-Infrastruktur mit automatischer Skalierung und der Terminplanung in medizinischen Einrichtungen. Frühe Lösungen beruhten auf zeigerbasierter Iteration oder Logik externer Anwendungen, um durch Zeitpläne zu iterieren, was bei großen Datensätzen zu erheblichen Leistungsproblemen führte. Die Einführung von ANSI SQL:2003 Fensterfunktionen ermöglichte rein relationale Ansätze zur zeitlichen Analyse, wodurch Datenbanken komplexe Intervallarithmetik effizient innerhalb der Engine verarbeiten konnten.
Angesichts einer Tabelle von Ressourcenbuchungen mit den Zeitstempeln start_time und end_time besteht das Ziel darin, die maximale Anzahl gleichzeitiger Reservierungen zu bestimmen, die zu jedem Zeitpunkt aktiv sind, und die spezifischen Zeitfenster zu identifizieren, in denen dieser Höhepunkt auftrat. Die Komplexität ergibt sich daraus, dass die Standardaggregation zeitliche Daten komprimiert, während einfache Joins eine kartesische Explosion erzeugen, wenn Intervalle überlappen. Eine robuste Lösung muss Intervalle mit Beginn und Ende als diskrete Ereignisse behandeln und eine laufende Zählung aktiver Ressourcen an jedem Übergangspunkt berechnen.
Der kanonische Ansatz wandelt Intervalle in diskrete Ereignisse um, indem UNION ALL verwendet wird, um Beginn (Gewicht +1) und Ende (Gewicht -1) zu trennen. Anschließend wird eine kumulative Summe über SUM() OVER (ORDER BY timestamp) angewendet, um die Gleichzeitigkeit zu verfolgen. Um gleichzeitige Beginn/Ende-Ereignisse deterministisch zu verarbeiten, müssen Endereignisse vor den Beginnereignissen am selben Zeitstempel bearbeitet werden (unter Verwendung eines sekundären Sortierschlüssels). Schließlich wird dies in einem CTE eingekapselt, um den maximalen Gleichheitswert zu filtern.
WITH events AS ( SELECT start_time AS ts, 1 AS delta, 0 AS is_end FROM reservations UNION ALL SELECT end_time AS ts, -1 AS delta, 1 AS is_end FROM reservations ), concurrency AS ( SELECT ts, SUM(delta) OVER (ORDER BY ts, is_end, delta ROWS UNBOUNDED PRECEDING) AS concurrent_count FROM events ) SELECT MAX(concurrent_count) AS peak_concurrency FROM concurrency;
Um die spezifischen Zeitfenster der Höchstnutzung zu finden, wird zurückgejoined, um Perioden zwischen aufeinanderfolgenden Zeitstempeln zu identifizieren, bei denen die Zählung dem Maximum entspricht.
Eine SaaS-Plattform verfolgte Millionen von Video-Transcoding-Jobs in einer Tabelle jobs mit den Zeitstempeln started_at und completed_at. Das Operationsteam musste die genauen Zeiträume identifizieren, in denen die GPU-Nutzung 100% erreichte, um die Warteschlangenplanung zu optimieren.
Ein Ansatz, der in Betracht gezogen wurde, war die Verwendung eines Cursors, um chronologisch zu iterieren, einen Zähler bei Beginn zu erhöhen und bei Ende zu verringern. Während dies für Entwickler, die mit imperativen Sprachen vertraut sind, einfach erschien, verarbeitete diese Methode die Zeilen sequentiell, was in der Produktionsumgebung über 45 Minuten in Anspruch nahm und Tabellen sperrte. Außerdem erforderte es eine komplexe Transaktionsverwaltung, um die Konsistenz der Lesevorgänge sicherzustellen.
Eine andere Alternative bestand darin, eine Zeitreihentabelle mit einer Zeile pro Minute zu generieren und diese gegen Intervalle unter Verwendung von BETWEEN-Prädikaten zu joinen. Dies führte zu genauen Ergebnissen, erforderte jedoch Milliarden von Zeilen für eine Minuten-genaue Präzision über ein Jahr, was Terabytes temporären Speicherplatz verlangte und nicht in der Lage war, Spitzen von weniger als einer Minute zu erfassen.
Das Team wählte den ereignisbasierten Ansatz mit UNION ALL in Verbindung mit ANSI SQL Fensterfunktionen. Durch die Behandlung von Beginn und Ende als +1/-1-Ereignisse wurde die Abfrage in 12 Sekunden unter Verwendung standardmäßiger B-Baum-Indizes auf den Zeitstempelspalten ausgeführt. Diese Methode behandelte korrekt Randfälle, in denen Jobs endeten, genau wenn andere begannen.
Die Analyse ergab, dass die maximale Gleichzeitigkeit während der nächtlichen Batch-Verarbeitung zwischen 02:00 und 02:07 UTC auftrat, mit bis zu 847 gleichzeitigen Jobs. Durch die Implementierung einer dynamischen Warteschlangen-Drosselung speziell für diesen Zeitraum konnten sie Kaskadenfehler verhindern und die Überversorgung der Infrastruktur um 30% reduzieren.
Wie gehen Sie mit Intervallen von Null-Dauer (start_time = end_time) um, ohne die Gleichheitszahl fälschlicherweise zu erhöhen?
Intervalle mit Null-Dauer stellen sofortige Ereignisse dar, die nicht zur gleichzeitigen Belastung beitragen sollten. Wenn sie als Standardintervalle behandelt werden, könnten sie während ihres eigenen Endereignisses als aktiv gezählt werden. Die Lösung erfordert die Zuordnung eines strikten Ordnungskennzeichens: Verarbeiten Sie Endereignisse (-1) vor Beginnereignissen (+1), wenn Zeitstempel kollidieren, und schließen Sie Intervalle mit Null-Dauer vollständig vom Ereignisstrom aus oder weisen Sie ihnen je nach Geschäftsanalyse einen Delta von 0 zu. In ANSI SQL wird dies umgesetzt, indem eine Diskriminator-Spalte hinzugefügt wird: ORDER BY ts, is_end ASC, delta ASC, um sicherzustellen, dass Beendigungen die Zählung verringern, bevor neue Zuweisungen sie am identischen Zeitstempel erhöhen.
Warum kann der ereignisbasierte Ansatz potenziell falsche Ergebnisse liefern, wenn Sie UNION anstelle von UNION ALL verwenden, wenn Sie Start- und Endereignisse kombinieren?
UNION führt implizit eine DISTINCT-Operation durch, wobei doppelte Zeitstempel zusammengefasst werden. Wenn zwei Reservierungen genau um 2023-10-01 10:00:00 beginnen, reduziert UNION dies auf eine einzige Zeile, wodurch die kumulative Summe eines +1-Inkrements verpasst wird. Dies führt zu einer Unterzählung der Gleichzeitigkeit. UNION ALL bewahrt jede individuelle Intervallgrenze als separates Ereignis, was mathematisch erforderlich ist, da jede Reservierung unabhängig zur Gesamtlast beiträgt. Kandidaten übersehen oft diesen Unterschied und gehen davon aus, dass Zeitstempel einzigartig sind, während Vielfältigkeit für eine korrekte Aggregation entscheidend ist.
Wie vermeiden Sie Lücken in der Ausgabe, wenn mehrere aufeinanderfolgende Zeiträume den gleichen Höchstwert teilen, wenn Sie die spezifischen Zeitfenster der maximalen Gleichzeitigkeit (nicht nur den maximalen Wert) berechnen?
Nachdem der maximale Gleichheitswert identifiziert wurde, ergibt das Zurückjoinen mit allen Zeitstempeln, bei denen dies auftritt, diskrete Punkte. Um kontinuierliche Zeitblöcke zu rekonstruieren, müssen Sie die Gaps and Islands-Technik anwenden: Verwenden Sie LAG(), um zu überprüfen, ob die vorherige Zeile ebenfalls am Höhepunkt war, und LEAD(), um zu überprüfen, ob die nächste Zeile am Höhepunkt ist. Geben Sie nur Zeilen aus, bei denen der vorherige Wert unterschiedlich ist (Beginn der Insel) oder der nächste Wert unterschiedlich ist (Ende der Insel). Pärchen Sie diese dann mit ROW_NUMBER(), um Start-Ende-Paare zu erstellen. Kandidaten geben häufig unformatierte Zeitstempellisten aus oder verwenden GROUP BY auf dem Zählwert, wodurch die zeitliche Nachbarschaftsinformation verloren geht, die erforderlich ist, um separate Spitzenereignisse von einem kontinuierlichen Spitzeneereignis zu unterscheiden.