Geschichte der Frage. Die Notwendigkeit für laufende eindeutige Zählungen entstand aus analytischen Arbeitslasten, die Metriken wie kumulative einzigartige Kundenakquisitionen oder eingebrachte eindeutige SKUs im Laufe der Zeit nachverfolgen. Vor den Erweiterungen der Fensterfunktionen in ANSI SQL:2003 verließen sich Analysten auf Selbstverknüpfungen oder korrelierte Unterabfragen, was zu einer quadratischen Zeitkomplexität führte, die für moderne Datenvolumina inakzeptabel ist. Die Standardisierung von Fensterfunktionen bot einen linearzeitlichen, satzbasierten Mechanismus zur Aufrechterhaltung der laufenden Kardinalität ohne prozedurale Schleifen.
Das Problem. ANSI SQL verbietet ausdrücklich das DISTINCT-Schlüsselwort innerhalb von aggregierten Fensterfunktionen (z. B. COUNT(DISTINCT col) OVER (...)). Diese Einschränkung verhindert die direkte Berechnung eindeutiger Werte innerhalb eines kumulativen oder gleitenden Rahmens. Die zentrale Herausforderung besteht darin, das erstmalige Auftreten jeder Entität innerhalb der Sortierreihenfolge der Partition zu identifizieren und diese binären Flags (erstes Auftreten = 1, sonst = 0) schrittweise zu summieren.
Die Lösung. Der kanonische Ansatz kombiniert ROW_NUMBER() zur Kennzeichnung erster Vorkommen mit einer bedingten SUM()-Fensterfunktion. Durch Partitionierung von ROW_NUMBER() nach der Entitätskennung erhält das chronologisch erste Vorkommen den Wert 1; nachfolgende Vorkommen erhalten inkrementierende Ganzzahlen. Eine äußere Abfrage summiert dann einen Fallausdruck, der nur dann 1 ausgibt, wenn die Zeilennummer 1 entspricht, ausgewertet über einen unbegrenzten vorangehenden Rahmen.
SELECT event_date, region_id, user_id, SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY region_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_unique_users FROM ( SELECT event_date, region_id, user_id, ROW_NUMBER() OVER ( PARTITION BY region_id, user_id ORDER BY event_date, event_id -- event_id als Tie-Breaker ) AS rn FROM user_activity ) flagged;
Problembeschreibung. Ein Fintech-Startup musste die regulatorische Compliance überwachen, indem es die kumulativen einzigartigen Händler, die je Verkaufsregion im Geschäftsjahr eingestiegen sind, nachverfolgte. Ihre Tabelle merchant_signups enthielt 120 Millionen Zeilen mit region_code, merchant_id und signup_timestamp. Vorhandene Python-Batch-Jobs benötigten 35 Minuten, um diese Metriken nachts zu berechnen, was zu Berichtsverzögerungen und veralteten Dashboard-Daten führte. Die Anforderung war, Echtzeit-kumulative Zählungen in striktem ANSI SQL zu erzeugen, um Portabilität über Cloud-Datenlager zu gewährleisten.
Lösung A: Der Selbstverknüpfungsansatz. Diese Methode verbindet die Tabelle mit sich selbst basierend auf übereinstimmenden Regionen und früheren Zeitstempeln und zählt eindeutige Händler pro äußerer Zeile. Vorteile: Sie benötigt keine Unterstützung für Fensterfunktionen und funktioniert auf Legacy SQL-92-Engines. Nachteile: Der Algorithmus zeigt eine Komplexität von O(n²); für Millionen von Zeilen erzeugt dies zwischenzeitliche kartesische Produkte, die Terabytes an temporärem Speicher verbrauchen und nicht innerhalb von Stunden abgeschlossen werden, was es betrieblich unpraktisch macht.
Lösung B: Die korrelierte skalare Unterabfrage. Hier ist die SELECT-Klausel eingebettet innerhalb einer Unterabfrage: (SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp). Vorteile: Es ist deklarativ und logisch transparent zu lesen. Nachteile: Die Unterabfrage wird einmal pro Zeile (120 Millionen Mal) ausgeführt, was Predicate Pushdown verhindert und massive zufällige E/A verursacht; Datenbankoptimierer können distinct aggregates über unterschiedliche zeitliche Bereiche nicht dekorellieren, was zu geschätzten Ausführungszeiten von mehr als 90 Minuten führt.
Lösung C: Die Technik der ANSI SQL Fensterfunktion. Nutzung von ROW_NUMBER() zur Identifizierung erster Vorkommen gefolgt von einer laufenden SUM(), wie im obigen Codebeispiel gezeigt. Vorteile: Dies führt zu einem einzigen Tabellenscan mit Sortierung und nutzt die Spooling-Fähigkeiten des Optimierers für O(n log n) Komplexität und begrenzte Speichernutzung. Nachteile: Es erfordert sorgfältige Handhabung zeitlicher Bindungen; wenn zwei Anmeldungen identische Zeitstempel teilen, könnte die nichtdeterministische Reihenfolge zu einer doppelten Zählung führen, es sei denn, ein einzigartiger Tie-Breaker (wie event_id) wird zur ORDER BY-Klausel hinzugefügt.
Ausgewählte Lösung und Ergebnis. Lösung C wurde implementiert. Durch die Einbeziehung von event_id in die ORDER BY-Klausel zur Gewährleistung einer deterministischen Erkennung des Erstauftretens wurde die Abfrage in 4 Minuten auf dem bestehenden Cluster ausgeführt – eine Verbesserung um das 9-fache. Das Ergebnis ermöglichte Echtzeit-Compliance-Dashboards, die es Risiko-Beauftragten ermöglichten, die Vielfalt der Anmeldungen ohne ETL-Verzögerungen zu überwachen, und die Abfrage war vollständig portabel zu PostgreSQL, Snowflake und BigQuery ohne Modifikationen.
Warum wirft COUNT(DISTINCT column) OVER (ORDER BY ...) einen Syntaxfehler in striktem ANSI SQL auf?
Der SQL-Standard verbietet ausdrücklich das DISTINCT-Schlüsselwort innerhalb des Arguments einer aggregierten Fensterfunktion wie COUNT, SUM oder AVG. Während spezifische Anbieter (z. B. PostgreSQL 16+, Oracle) dies als proprietäre Erweiterung anbieten, beschränken ANSI SQL:2011 und frühere Versionen aggregierte Fensterfunktionen auf die Bearbeitung aller Zeilen innerhalb des definierten Rahmens. Diese Einschränkung besteht, weil die Aufrechterhaltung einer distinct-set-Hash-Tabelle für jeden möglichen Fensterrahmen während der Streaming-Bewertung nicht durch die Standardgrammatik gefordert wird. Kandidaten müssen erkennen, dass DISTINCT nur in Standardaggregatfunktionen ohne OVER-Klauseln oder in inversen Verteilungsfunktionen wie PERCENTILE_CONT zulässig ist, aber niemals als Fenstered distinct count.
Wie gehen Sie mit doppelten Zeitstempeln um, wenn Sie das "erste" Auftreten einer Entität bestimmen?
ROW_NUMBER() weist bei Bindungen willkürliche Werte zu, es sei denn, die ORDER BY-Klausel gibt eine gesamte Ordnung an. Wenn ein Händler zwei Einträge mit identischen Zeitstempeln hat, könnten beide Zeilen potenziell rn = 1 erhalten, wenn die Reihenfolge nichtdeterministisch ist, was dazu führen würde, dass die kumulative Zählung zweimal irrtümlich erhöht wird. Die Lösung besteht darin, einen einzigartigen Primärschlüssel oder eine automatisch inkrementierende ID zur ORDER BY-Klausel hinzuzufügen: ORDER BY signup_timestamp, merchant_signup_id. Dies gewährleistet eine deterministische Reihenfolge, bei der die zuerst zugewiesene ID als erstes Vorkommen betrachtet wird, wodurch die mathematische Integrität der laufenden eindeutigen Zählung erhalten bleibt.
Kann diese Technik für eine bewegliche eindeutige Zählung über ein festes Zeilenanzahlfenster (z. B. die letzten 100 Transaktionen) anstelle unbegrenzter vorheriger angewendet werden?
Nein, nicht effizient mit reinem ANSI SQL. Die Methode ohne unbegrenzte Vorgänge funktioniert, da die Eindeutigkeit monoton ist – sobald eine Entität erscheint, bleibt sie „gezählt“ für immer. In einem gleitenden Fenster (z. B. ROWS BETWEEN 100 PRECEDING AND CURRENT ROW) muss eine entstehende Entität die Zählung reduzieren, was Wissen darüber erfordert, ob die ausgehende Zeile die einzige Instanz dieser Entität innerhalb des aktuellen Rahmens darstellt. ANSI SQL hat keine Array-Aggregation oder Set-Differenzoperatoren innerhalb von Fensterrahmen, um solche Ausgänge effizient zu verfolgen. Die Implementierung erfordert entweder rekursive CTEs (die sich für dieses Szenario auf O(n²) verschlechtern) oder proprietäre Erweiterungen wie ARRAY_AGG in Kombination mit Mengenoperationen, die beide die strikte ANSI-Konformität verletzen.