Wie würden Sie Werte rückwärts auf vorhergehende vorläufige Datensätze innerhalb geordneter Partitionen propagieren, wenn Sie Audit-Trails analysieren, bei denen definitive Statusdatensätze nur an den Intervallgrenzen eintreffen, und dabei ausschließlich ANSI SQL-Fensterfunktionen verwenden, ohne Selbst-Joins und rekursive CTEs?

Antwort auf die Frage

Geschichte: In der zeitlichen Datenlagerung dominiert die Technik der Letzten Beobachtung vorwärts tragen (LOCF) die Imputation von fehlenden Werten, indem vorhergehende gültige Datensätze zur Auffüllung von Lücken verwendet werden. Allerdings erfordern spezifische analytische Bereiche – wie beispielsweise die Anwendung von Tagesend-Abstimmungen auf intraday-finanzielle Transaktionen oder das Rückpropagieren von Laborbestätigungen zu früheren vorläufigen Diagnosen – den umgekehrten Ansatz der Nächsten Beobachtung rückwärts tragen (NOCB). Historisch wurde NOCB über korrelierte Unterabfragen oder prozedurale Cursor implementiert, die beide eine Komplexität von O(n²) zeigen und moderne set-basierte Optimierer nicht nutzen.

Das Problem: Gegeben ist eine völlig geordnete Sequenz (z.B. event_time), wobei jeder NULL-Wert durch den nächstliegenden nicht-NULL-Wert ersetzt werden muss, der nach ihm in der Sequenz auftritt. Konsecutive NULL-Werte vor einem gültigen Datensatz sollten den gleichen nachfolgenden Wert erhalten. Standardfunktionen wie LEAD() greifen nur auf die unmittelbare nächste Zeile zu und können versagen, wenn mehrere aufeinanderfolgende NULL-Werte vor einem nicht-NULL-Anker existieren. Selbst-Joins und rekursive CTEs sind aufgrund von Leistungsbeschränkungen verboten.

Die Lösung: Die Lösung nutzt die NULL-ignorierende Semantik von COUNT(expression). Indem wir nicht-NULL-Werte von der aktuellen Zeile bis zum Ende der Partition zählen (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), erzeugen wir einen stabilen "Bucket-Identifier", der für alle Zeilen zwischen zwei nicht-NULL-Ankern identisch ist. Innerhalb jedes Buckets ruft MAX(val) – das auch NULL ignoriert – den Ankerwert ab und überträgt ihn an alle Zeilen in dieser Gruppe.

WITH bucketed AS (
  SELECT 
    record_id,
    event_time,
    status_code,
    COUNT(status_code) OVER (
      ORDER BY event_time, record_id
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS bucket_id
  FROM audit_log
)
SELECT 
  record_id,
  event_time,
  COALESCE(
    MAX(status_code) OVER (
      PARTITION BY bucket_id 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ),
    'UNKNOWN'
  ) AS confirmed_status
FROM bucketed;

Lebenssituation

Kontext und Problembeschreibung: Eine Hochfrequenzhandelsfirma führt eine execution-Tabelle, die Aktienkäufe im Mikrosekundenbereich erfasst. Aufgrund der Berichterstattungsprotokolle der Börse trifft der endgültige "konsolidierte Preis" für jede gegebene Minute – verifiziert durch die Clearingstelle – 30 Sekunden nach Ende der Minute ein und wird nur an der Grenze (z.B. 14:30:00.000) abgestempelt. Für regulatorische TWAP (Time-Weighted Average Price)-Berechnungen muss jede Millisekunde dieser Minute den endgültigen konsolidierten Preis widerspiegeln, was eine Rückbefüllung aller vorhergehenden Datensätze von 14:29:00.000 - 14:29:59.999 erforderlich macht. Das tägliche Volumen übersteigt 50 Millionen Zeilen und das Batch-Fenster beträgt 10 Minuten.

Lösung 1: Korrelierte Skalar-Unterabfrage. Dieser Ansatz verwendet eine Skalare Unterabfrage für jede Zeile, um den MIN(event_time) zukünftiger Zeilen zu suchen, bei denen consolidated_price IS NOT NULL, und verbindet sich dann zurück, um diesen Preis abzurufen.

Vorteile: Konzeptuell einfach für Entwickler mit prozeduralen Hintergründen.

Nachteile: Führt O(n²) Vergleiche aus. Bei Produktionsdaten überschritt die Abfragelaufzeit 45 Minuten, was das Batch-Fenster verletzte. Die Handhabung mehrerer aufeinanderfolgender NULL-Werte erfordert zusätzliche Logik, um vorwärts zu springen, was die Komplexität und Fehlerquoten erhöht.

Lösung 2: Rekursive CTE-Durchquerung. Eine rekursive CTE iteriert rückwärts zeilenweise und überträgt den nicht-NULL-Preis rückwärts, bis ein weiterer nicht-NULL gefunden wird.

Vorteile: Garantiert, dass es auf jeder ANSI SQL-kompatiblen Datenbank funktioniert.

Nachteile: Rekursive CTEs verarbeiten Zeilen in vielen Engines (z.B. PostgreSQL) sequentiell, was zu einer einsträngigen Ausführung und potenziellem Stacküberlauf bei tiefen Partitionen führt. Benchmarks zeigten eine Laufzeit von 20 Minuten mit hohem Speicherverbrauch, was es für Produktions-SLAs ungeeignet machte.

Lösung 3: Fensterfunktions-Bucketisierung (Ausgewählt). Implementieren Sie das COUNT- und MAX-Muster. Das rückblickende COUNT erstellt identische Buckets für alle Zeilen, die den gleichen zukünftigen Wert benötigen, während MAX diesen Wert innerhalb des Buckets propagiert.

Vorteile: Vollständig set-basiert, parallelisierbar und wird in O(n log n) Zeit aufgrund der Sortieroperation ausgeführt. Es skaliert linear mit dem Volumen und verwendet standardmäßig ANSI SQL, das auf PostgreSQL, SQL Server, Oracle und DB2 portabel ist.

Nachteile: Erfordert zwei Durchläufe über die Daten (die CTE und die äußere Abfrage), obwohl moderne Optimierer diese oft fusionieren. Es erfordert eine totale Ordnung; doppelte Zeitstempel erfordern eine Tie-Breaker-Spalte, um Determinismus sicherzustellen.

Ergebnis: Die Pipeline-Laufzeit sank von 45 Minuten auf 8 Sekunden bei einem Dataset mit 50 Millionen Zeilen. Das Unternehmen beseitigte ein fragiles Python-Rückfüll-Skript, reduzierte die Infrastrukturkomplexität und stellte sicher, dass regulatorische Berichte innerhalb des Compliance-Fensters generiert wurden.

Was Bewerber oft übersehen

Warum muss COUNT(column) anstelle von COUNT(*) oder ROW_NUMBER() verwendet werden, wenn der Gruppierungsschlüssel konstruiert wird?

Viele Bewerber verwenden intuitiv COUNT(*) oder ROW_NUMBER(), in der Annahme, dass diese die Daten segmentieren können. COUNT(*) zählt jede Zeile unabhängig von NULLs und produziert einen einzigartigen, monotonen Wert für jede Zeile im rückblickenden Rahmen, was die Bildung stabiler Gruppen verhindert. ROW_NUMBER() weist jeder Zeile eine einzigartige Kennung zu, was ebenfalls die Gruppierung zerstört. Nur COUNT(column) wird ausschließlich erhöht, wenn nicht-NULL-Werte auftreten, und weist allen vorhergehenden NULLs bis zur nächsten nicht-NULL-Grenze die gleiche "Bucket-ID" zu. Diese Unterscheidung ist entscheidend, da sie die NULL-ignorierende Semantik von aggregierten Fensterfunktionen nutzt, um ein "Vorausschauen" ohne prozedurale Logik zu simulieren.

Wie verhält sich die Abfrage, wenn die Partition mit nachfolgenden NULL-Werten endet, und welche Modifikation stellt sicher, dass deterministische Behandlung erfolgt, wenn keine zukünftige Beobachtung vorhanden ist?

Wenn die letzten Zeilen in der geordneten Partition NULL sind, bewertet COUNT(status_code) diese Zeilen zu null. Folglich gibt MAX(status_code) NULL zurück, was logisch korrekt ist – keine zukünftige Beobachtung existiert, die rückwärts getragen werden könnte. Bewerber vergessen oft, dies in der nachgelagerten Geschäftslogik zu handhaben. Um einen Standardwert bereitzustellen (z.B. einen statischen Platzhalter oder einen Wert aus einer externen Abgleichung), muss das Ergebnis in COALESCE gewickelt werden. Darüber hinaus sollte man, um zwischen "gefüllten NULL" und "nicht gefüllten NULL" für die Datenqualitätsüberwachung zu unterscheiden, die ursprünglichen und gefüllten Werte vergleichen: CASE WHEN status_code IS NULL AND bucket_id = 0 THEN 'UNBESTÄTIGT' END.

Welches Determinismusproblem ergibt sich, wenn die ORDER BY-Klausel doppelte Werte enthält, und warum verschärft der Wechsel von ROWS zu RANGE das Problem?

Wenn die Sortierschlüssel Duplikate (Bindungen) enthalten, wird die Definition des Fensterrahmens mehrdeutig. Die Verwendung von ROWS (physikalische Offsets) weist Gruppen basierend auf der physischen Tabellenreihenfolge zu, die willkürlich ist, es sei denn, eine einzigartige sekundäre Sortierschlüssel wird angegeben. Der Wechsel zu RANGE (logische Wertebereiche) behandelt alle Zeilen mit dem gleichen Sortierwert als Peers, wodurch sie denselben Rahmen teilen. In dieser Lösung, wenn mehrere Zeilen den gleichen event_time teilen, könnte RANGE irrtümlich NULL-Zeilen mit nicht-NULL-Zeilen von demselben Zeitstempel gruppieren oder Gruppen unvorhersehbar aufteilen. Bewerber müssen eine totale Ordnung sicherstellen, indem sie einen einzigartigen Schlüssel (z.B. record_id) zur ORDER BY-Klausel hinzufügen: ORDER BY event_time, record_id, um eine deterministische Bucket-Zuweisung über alle ANSI SQL-Implementierungen hinweg zu garantieren.