Joins, Fensterfunktionen und CTEs für Zahlungsströme

Abfragen über einzelne Tabellen beantworten die Frage „Was ist passiert?“. Die Analyse von Zahlungsströmen fragt nach mehr: Wer war die Gegenpartei, welchen Wert hatte die Übertragung zum jeweiligen Zeitpunkt, wie hoch war der Kontostand davor und danach, und wurden die Mittel schnell weiterbewegt? Für diese Antworten braucht man Joins, Fensterfunktionen und Common Table Expressions (CTEs) – die drei SQL-Werkzeuge, um die es in dieser Lektion geht – sowie Kenntnisse über die Fehler, die ihre Ergebnisse am häufigsten verfälschen.

Die Abfragen verwenden das generische Schema aus der Lektion SQL-Grundlagen für Blockchain-Tabellen: token_transfers (eine Zeile pro fungibler Token-Übertragung, mit amount_raw in Basiseinheiten), tokens (Dezimalstellen), labels (Adresse, Entität, Kategorie, Quelle, Konfidenz, updated_at) und prices (Vermögenswert, Stunde, price_usd).

Joins und die Fan-out-Falle

Ein Join kombiniert Zeilen aus zwei Tabellen, die bei einem Schlüssel übereinstimmen. Ein Inner Join (JOIN) behält nur Zeilen, die auf beiden Seiten übereinstimmen; ein Left Join (LEFT JOIN) behält jede Zeile der linken Tabelle und füllt die rechte Seite mit NULL-Werten, wenn es keine Übereinstimmung gibt. Für die Anreicherung (Hinzufügen von Labels oder Preisen zu Übertragungen) ist der Left Join fast immer die richtige Wahl, denn eine Übertragung ohne Label oder Preis ist trotzdem eine Übertragung.

Der schädlichste Join-Fehler ist Fan-out. Wenn die rechte Tabelle mehrere Zeilen für denselben Schlüssel enthält, wird jede linke Zeile für jede Übereinstimmung wiederholt. Label-Tabellen sind der klassische Fall: Eine Einzahlungsadresse einer Börse kann ein Label aus einem Dienst zur Adresskennzeichnung und ein weiteres aus der eigenen Offenlegung der Börse haben. In den Beispieldaten erscheint eine Einzahlung von 100 USDC an eine solche Adresse nach einem einfachen Join zweimal, und eine Summe über die gejointen Zeilen ergibt 200 USDC. Es tritt kein Fehler auf; die Zahl ist einfach falsch.

Zwei Gewohnheiten verhindern das. Stelle vor dem Join sicher, dass die rechte Seite beim Join-Schlüssel eindeutig ist. Vergleiche nach dem Join die Zeilenanzahl mit der ursprünglichen Tabelle: Ein Join zur Anreicherung darf niemals zusätzliche Zeilen erzeugen.

Übertragungen mit Labels verknüpfen

Das Problem doppelter Labels lässt sich beheben, indem man sie sortiert und pro Adresse nur ein Label beibehält.

-- Ein Label pro Adresse (höchste Konfidenz, dann das jüngste)
WITH best_label AS (
  SELECT address, entity, category,
         ROW_NUMBER() OVER (PARTITION BY address
                            ORDER BY confidence DESC, updated_at DESC) AS rn
  FROM labels
)
SELECT tt.transaction_hash,
       tt.to_address,
       COALESCE(l.entity, 'unlabeled') AS counterparty,
       l.category
FROM token_transfers AS tt
LEFT JOIN best_label AS l
  ON l.address = tt.to_address
 AND l.rn = 1                       -- ein Label pro Adresse beibehalten
WHERE tt.from_address = '0x5e1f…c0de';

Die CTE nummeriert die Labels jeder Adresse vom besten bis zum schlechtesten; der Join behält nur Nummer 1. COALESCE ersetzt ein fehlendes Label durch das Wort „unlabeled“, was in einer Ausgabe lesbarer ist als NULL.

Achte darauf, wo l.rn = 1 steht: in der ON-Klausel, nicht in WHERE. Bei einem Left Join wird eine Bedingung für die rechte Tabelle, die in WHERE steht, erst nach dem Join angewendet. Sie verwirft jede Zeile, deren rechte Seite NULL ist, und macht aus dem Left Join wieder einen Inner Join. Alle Gegenparteien ohne Label würden aus dem Ergebnis verschwinden, ohne dass die Ausgabe darauf hinweisen würde.

Um beide Seiten jeder Übertragung zu labeln, verknüpfe dieselbe CTE unter verschiedenen Aliasnamen zweimal, zum Beispiel mit LEFT JOIN best_label AS lf ON lf.address = tt.from_address AND lf.rn = 1 und LEFT JOIN best_label AS lt ON lt.address = tt.to_address AND lt.rn = 1. Gruppiert man das Ergebnis nach den beiden Entitäten, werden Tausende adressbezogene Übertragungen zu einer kurzen Tabelle mit Zahlungsströmen zwischen Entitäten. Das ist oft die erste Ansicht, die Leserinnen und Leser eines Berichts benötigen. Achte darauf, dass Adressen auf beiden Seiten gleich gespeichert sind: Eine Prüfsummenadresse mit gemischter Groß- und Kleinschreibung in der Label-Tabelle stimmt mit keiner Adresse in einer Transfer-Tabelle überein, die nur Kleinbuchstaben verwendet.

Die Auswahl „höchste Konfidenz gewinnt“ ist selbst eine analytische Entscheidung. Halte sie fest und übernimm die Quelle und Konfidenz des Labels in den Bericht (siehe die Lektion Attribution und Kennzeichnung). Wenn sich Labels im Laufe der Zeit ändern, verknüpfe das Label, das zum Zeitpunkt der Übertragung gültig war, nicht das heutige.

Mit Preisen verknüpfen

Um eine Übertragung zu bewerten, muss man ihr zum richtigen Zeitpunkt einen Preis zuordnen. Bei einer Tabelle mit stündlichen Preisen wird der Zeitstempel der Übertragung auf die Stunde gekürzt und der Join nach Vermögenswert und Stunde ausgeführt.

-- USD-Wert zum Zeitpunkt der Stunde jeder Übertragung
SELECT tt.transaction_hash,
       tt.amount_raw / POWER(10, t.decimals)               AS amount,
       p.price_usd,
       tt.amount_raw / POWER(10, t.decimals) * p.price_usd AS value_usd
FROM token_transfers AS tt
JOIN tokens AS t
  ON t.token_address = tt.token_address
LEFT JOIN prices AS p                -- LEFT: Zeilen ohne Preis beibehalten
  ON p.asset = tt.token_address
 AND p.hour  = DATE_TRUNC('hour', tt.block_timestamp)
WHERE tt.from_address = '0x5e1f…c0de';

Im Beispieldatensatz ist eine Übertragung von 500 USDC zu einem Preis von 0,9998 USD 499,90 USD wert, und 2 WETH zu einem Preis von 2.500 USD sind 5.000 USD wert. Für eine Übertragung des nachgeahmten Tokens „USDC“ werden NULL als Preis und NULL als Wert zurückgegeben. Das ist die richtige Antwort: Für diesen Token gibt es keinen Markt, und NULL macht das für die Lesenden deutlich. Ersetze einen fehlenden Preis niemals durch null und verwende niemals ersatzweise den Preis eines anderen Tokens mit demselben Symbol.

Bei Gleichheits-Joins muss es für jede Stunde eine Preiszeile geben. Wenn Preisangaben lückenhaft sind, verwende den letzten früheren Preis. DuckDB, ClickHouse und Snowflake bieten genau dafür einen ASOF JOIN; in anderen Engines erledigt eine Fensterfunktion oder eine Bereichsbedingung dieselbe Aufgabe, aber langsamer. Auch die Preisquelle ist wichtig: Gib an, von welcher Börse oder welchem Index der Preis stammt, und betrachte Preise wenig gehandelter Token als Schätzwerte, die manipuliert werden können.

Fensterfunktionen: laufende Kontostände, erstes und letztes Auftreten

Eine Fensterfunktion berechnet für jede Zeile einen Wert aus einer Gruppe zusammengehöriger Zeilen, ohne sie wie GROUP BY zusammenzufassen. Die Syntax lautet function() OVER (PARTITION BY … ORDER BY … frame): Die Partition legt die Gruppe fest, die Sortierung die Reihenfolge und der Frame, welche Zeilen berücksichtigt werden.

-- Laufender Kontostand eines Tokens für eine Adresse, Ereignis für Ereignis
WITH flows AS (
  SELECT block_number, log_index, block_timestamp, amount_raw AS delta
  FROM token_transfers
  WHERE token_address = '0x7a11…0001' AND to_address = '0x5e1f…c0de'
  UNION ALL
  SELECT block_number, log_index, block_timestamp, -amount_raw
  FROM token_transfers
  WHERE token_address = '0x7a11…0001' AND from_address = '0x5e1f…c0de'
)
SELECT block_timestamp,
       delta,
       SUM(delta) OVER (ORDER BY block_number, log_index
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance_raw,
       MIN(block_timestamp) OVER () AS first_seen,
       MAX(block_timestamp) OVER () AS last_seen
FROM flows
ORDER BY block_number, log_index;

Die CTE wandelt eingehende Übertragungen in positive Deltas und ausgehende in negative um; UNION ALL fügt sie zusammen (ein einfaches UNION würde identische Zeilen entfernen und echte Übertragungen verlieren). Die Fenstersumme addiert die Deltas dann in der Reihenfolge der Blockchain. Im Beispieldatensatz erhält die Adresse 2.000 USDC und sendet 500, 1.400 und 100 weiter. Der Kontostand beträgt daher der Reihe nach 2.000, dann 1.500, dann 100 und schließlich 0 (in Basiseinheiten also 2.000.000.000 und so weiter). MIN und MAX über ein leeres Fenster OVER () liefern für jede Zeile den Zeitpunkt des ersten und des letzten Auftretens.

Drei Details sind wichtig:

• Nach Blocknummer und Log-Index sortieren, denn zusammen geben sie die genaue Position eines Ereignisses in der Blockchain an. Innerhalb eines Blocks sind die Zeitstempel identisch.
• Den Frame ausdrücklich angeben. Wenn ORDER BY verwendet wird, aber kein Frame angegeben ist, lautet der Standardwert in Standard-SQL RANGE … CURRENT ROW. Dabei werden Zeilen mit gleichen Sortierschlüsseln als eine Gruppe behandelt und erhalten dieselbe Summe.
• Mit dem Zustand abgleichen. Ein aus Ereignissen ermittelter Kontostand setzt voraus, dass sich der Token normal verhält. Bei Tokens mit Transfergebühr und Rebase-Tokens gilt diese Annahme nicht; vergleiche den Endwert mit balanceOf im selben Block (siehe die Lektion Kontostände und Verläufe rekonstruieren).

Weitere nützliche Fensterfunktionen sind LAG und LEAD (der Wert der vorherigen oder nächsten Zeile, zum Beispiel die Zeit seit der letzten Übertragung), ROW_NUMBER (oben für Labels verwendet) und FIRST_VALUE. BigQuery, Snowflake und DuckDB ermöglichen mit einer QUALIFY-Klausel das Filtern nach einem Fensterergebnis; andernorts kannst du die Abfrage in eine Unterabfrage einbetten und außen filtern.

CTEs für mehrstufige Logik

Eine Common Table Expression benennt mit WITH name AS (…) ein Zwischenergebnis, das im nächsten Schritt verwendet werden kann. Untersuchungsfragen bestehen naturgemäß aus mehreren Schritten, und CTEs sorgen dafür, dass jeder Schritt verständlich und überprüfbar bleibt. Nehmen wir eine typische Hypothese: Welche Adressen haben einen Token von einer mit einem Exploit verknüpften Adresse erhalten und innerhalb von 24 Stunden mindestens 90 % davon weitergeleitet?

WITH hop1 AS (            -- Schritt 1: von einer mit einem Exploit verknüpften Adresse empfangen
  SELECT tt.to_address AS addr, SUM(tt.amount_raw) AS amt_in,
         MIN(tt.block_timestamp) AS first_in
  FROM token_transfers AS tt
  WHERE tt.token_address = '0x7a11…0001'
    AND tt.from_address IN (SELECT address FROM labels WHERE category = 'exploit')
  GROUP BY tt.to_address
),
fwd AS (                  -- Schritt 2: innerhalb von 24 Stunden nach dem ersten Empfang weitergeleiteter Betrag
  SELECT h.addr, SUM(tt.amount_raw) AS amt_out
  FROM hop1 AS h
  JOIN token_transfers AS tt
    ON tt.from_address = h.addr AND tt.token_address = '0x7a11…0001'
   AND tt.block_timestamp >= h.first_in
   AND tt.block_timestamp <  h.first_in + INTERVAL '24' HOUR
  GROUP BY h.addr
)
SELECT h.addr, h.first_in, h.amt_in, f.amt_out
FROM hop1 AS h JOIN fwd AS f ON f.addr = h.addr
WHERE f.amt_out >= 0.9 * h.amt_in;      -- mindestens 90 % weitergeleitet

Schritt 1 ermittelt die Empfänger des ersten Hops. Dabei wird IN (subquery) statt eines Joins mit labels verwendet, damit eine Exploit-Adresse mit zwei Labels die Beträge nicht verdoppeln kann. Schritt 2 summiert, was jeder Empfänger in den 24 Stunden nach dem ersten Empfang vom selben Token weitergesendet hat. Im letzten Schritt bleiben die Empfänger übrig, die mindestens 90 % weitergeleitet haben. Du kannst jede CTE einzeln ausführen und ihre Zeilenanzahl überprüfen, bevor du mit dem nächsten Schritt fortfährst.

Die Syntax für Intervalle ist unterschiedlich: INTERVAL '24' HOUR ist Standard-SQL und funktioniert in PostgreSQL, DuckDB und Trino; BigQuery verwendet TIMESTAMP_ADD(first_in, INTERVAL 24 HOUR). Betrachte das Ergebnis als Ermittlungsansatz, nicht als Schlussfolgerung: Der Abfluss kann auch Mittel umfassen, die die Adresse bereits zuvor gehalten hat. Genau darum geht es bei der Nachverfolgungsmethode, die in der Lektion Zahlungsströme vorwärts und rückwärts nachverfolgen behandelt wird. Die Nachverfolgung über mehrere Hops lässt sich mit WITH RECURSIVE umsetzen, aber die Anzahl der Pfade wächst mit jedem Hop schnell. Begrenze daher die Tiefe oder exportiere die Kanten in ein Graph-Tool.

Prüfungsschwerpunkt: Inner Joins im Vergleich zu Left Joins und warum Joins zur Anreicherung Left Joins sein sollten; Fan-out durch doppelte Labels und wie es sich anhand der Zeilenanzahl erkennen lässt; pro Adresse mit ROW_NUMBER ein Label beibehalten; warum eine Bedingung für die rechte Tabelle in WHERE einen Left Join in einen Inner Join verwandelt; Verknüpfung von Preisen nach gekürzter Stunde, ASOF-Joins und fehlende Preise als NULL belassen; laufende Kontostände mit SUM OVER, UNION ALL signierter Deltas, Sortierung nach Blocknummer und Log-Index sowie ausdrückliche ROWS-Frames; erstes und letztes Auftreten mit MIN- und MAX-Fensterfunktionen, LAG und QUALIFY; mehrstufige Hypothesen mit CTEs und Semi-Joins strukturieren.