Joins, Fensterfunktionen und CTEs für Mittelflüsse

Abfragen einzelner Tabellen beantworten die Frage „Was ist passiert?“. Die Analyse von Mittelflüssen fragt darüber hinaus: Wer war die Gegenpartei, welchen Wert hatte die Übertragung zum damaligen Zeitpunkt, wie hoch war der Saldo davor und danach, und wurden die Mittel schnell weitergeleitet? Diese Antworten erfordern Joins, Fensterfunktionen und Common Table Expressions (CTEs) – die drei SQL-Werkzeuge, die diese Lektion behandelt, zusammen mit den Fehlern, die ihre Ergebnisse am häufigsten verzerren.

Die Abfragen verwenden das generische Schema aus der Lektion Der Analysestack und SQL für Blockchain-Tabellen: token_transfers (eine Zeile pro Übertragung fungibler Token, mit amount_raw in Basiseinheiten), tokens (Dezimalstellen), labels (address, entity, category, source, grade, updated_at, wobei grade den Zuordnungsgrad A–D bezeichnet und A der stärkste ist) und prices (asset, hour, price_usd).

Joins und die Fan-out-Falle

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

Der folgenschwerste Join-Fehler ist Fan-out. Enthält die rechte Tabelle mehrere Zeilen für denselben Schlüssel, wird jede linke Zeile für jeden Treffer einmal wiederholt. Label-Tabellen sind der klassische Fall: Eine Einzahlungsadresse einer Börse kann ein Label aus der Kennzeichnung durch einen Dienst und ein weiteres aus der Offenlegung durch die Börse selbst tragen. In den Beispieldaten erscheint eine Einzahlung von 100 USDC an eine solche Adresse nach einem einfachen Join zweimal, und eine Summe über die verknüpften Zeilen ergibt 200 USDC. Es tritt kein Fehler auf; die Zahl ist schlicht falsch.

Zwei Gewohnheiten verhindern dies. Sorgen Sie vor dem Join dafür, dass die rechte Seite bezüglich des Join-Schlüssels eindeutig ist. Vergleichen Sie nach dem Join die Zeilenanzahl mit der ursprünglichen Tabelle: Ein Join zur Anreicherung darf niemals Zeilen hinzufügen.

Übertragungen mit Labels verknüpfen

Die Lösung für doppelte Labels besteht darin, sie nach Rang zu ordnen und pro Adresse eines beizubehalten.

-- Ein Label pro Adresse (bester Grad, dann das aktuellste)
WITH best_label AS (
  SELECT address, entity, category,
         ROW_NUMBER() OVER (PARTITION BY address
                            ORDER BY grade ASC, updated_at DESC) AS rn  -- A zuerst
  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 bei. COALESCE ersetzt ein fehlendes Label durch das Wort „unlabeled“ („ohne Label“), das in einer Ausgabe besser lesbar ist als NULL.

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

Um beide Seiten jeder Übertragung mit Labels zu versehen, verknüpfen Sie dieselbe CTE zweimal unter verschiedenen Aliasnamen, beispielsweise 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. Wenn Sie das Ergebnis anschließend nach den beiden Entitäten gruppieren, werden Tausende von Übertragungen auf Adressebene zu einer kurzen Tabelle von Mittelflüssen zwischen Entitäten zusammengefasst – häufig die erste Ansicht, die Leser des Berichts benötigen. Stellen Sie sicher, dass Adressen auf beiden Seiten auf dieselbe Weise gespeichert werden: Eine Adresse mit Prüfsumme und gemischter Groß- und Kleinschreibung in der Label-Tabelle findet keine Übereinstimmung in einer Übertragungstabelle mit ausschließlich kleingeschriebenen Adressen.

Die Entscheidung „Der beste Grad gewinnt“ ist selbst eine analytische Entscheidung. Dokumentieren Sie sie und übernehmen Sie Quelle und Grad des Labels in den Bericht (siehe die Lektion Von der Adresse über die Entität zur Person: Attribution und Labeling). Wenn sich Labels im Laufe der Zeit ändern, verknüpfen Sie mit dem Label, das zum Zeitpunkt der Übertragung gültig war, nicht mit dem heutigen.

Mit Preisen verknüpfen

Eine Übertragung zu bewerten bedeutet, ihr einen Preis zum richtigen Zeitpunkt zuzuordnen. Bei einer Tabelle mit stündlichen Preisen schneiden Sie den Zeitstempel der Übertragung auf die volle Stunde ab und verknüpfen über Vermögenswert und Stunde.

-- USD-Wert zur Stunde der jeweiligen Ü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 Beispiel ist eine Übertragung von 500 USDC bei einem Preis von 0,9998 genau 499,90 USD wert, und 2 WETH bei 2.500 sind 5.000 USD wert. Eine Übertragung des nachgeahmten „USDC“-Tokens liefert einen NULL-Preis und einen NULL-Wert. Das ist die richtige Antwort: Dafür gibt es keinen Markt, und ein NULL-Wert macht dies den Lesern deutlich. Ersetzen Sie einen fehlenden Preis niemals durch null, und greifen Sie niemals auf den Preis eines anderen Tokens mit demselben Symbol zurück.

Gleichheits-Joins benötigen für jede Stunde eine Preiszeile. Wenn Preise nur lückenhaft vorliegen, verwenden Sie den letzten davor verfügbaren Preis. DuckDB, ClickHouse und Snowflake bieten genau dafür einen ASOF JOIN; bei anderen Datenbank-Engines erfüllt eine Fensterfunktion oder eine Bereichsbedingung dieselbe Aufgabe, allerdings langsamer. Auch die Preisquelle ist wichtig: Geben Sie an, von welchem Handelsplatz oder Index der Preis stammt, und behandeln Sie Preise wenig gehandelter Token als Schätzwerte, die manipuliert werden können.

Fensterfunktionen: laufende Salden, erstes und letztes Auftreten

Eine Fensterfunktion berechnet für jede Zeile einen Wert aus einer Menge zusammengehöriger Zeilen, ohne diese zusammenzufassen, wie es GROUP BY tun würde. Die Syntax lautet function() OVER (PARTITION BY … ORDER BY … frame): Die Partition definiert die Gruppe, die Sortierung die Reihenfolge und der Fensterrahmen die berücksichtigten Zeilen.

-- Laufender Saldo 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 und ausgehende in negative Deltas um; UNION ALL führt sie untereinander zusammen (ein einfaches UNION würde identische Zeilen entfernen und tatsächliche Übertragungen verlieren). Die Fenstersumme addiert anschließend die Deltas in der Reihenfolge der Blockchain. Im Beispiel erhält die Adresse 2.000 USDC und sendet 500, 1.400 und 100, sodass der Saldo zunächst 2.000, dann 1.500, dann 100 und schließlich 0 beträgt (in Basiseinheiten 2.000.000.000 und so weiter). MIN und MAX über ein leeres Fenster OVER () liefern in jeder Zeile die Zeitpunkte des ersten und letzten Auftretens.

Drei Details sind wichtig:

• Sortieren Sie nach Blocknummer und Log-Index, die zusammen die genaue Position eines Ereignisses in der Blockchain bestimmen. Zeitstempel sind innerhalb eines Blocks gleich.
• Geben Sie den Fensterrahmen ausdrücklich an. Mit ORDER BY und ohne Fensterrahmen ist der Standard in Standard-SQL RANGE … CURRENT ROW; dabei werden Zeilen mit gleichen Sortierschlüsseln als Einheit behandelt und erhalten dieselbe Summe.
• Prüfen Sie das Ergebnis anhand des Zustands. Ein aus Ereignissen aufgebauter Saldo setzt voraus, dass sich der Token normal verhält. Token mit Übertragungsgebühren und Rebasing-Token verletzen diese Annahme; vergleichen Sie den Endwert mit balanceOf am selben Block (siehe die Lektion Das Kontomodell und die Rekonstruktion von Salden).

Weitere nützliche Fensterfunktionen sind LAG und LEAD (der Wert der vorherigen oder nächsten Zeile, beispielsweise die Zeit seit der letzten Übertragung), ROW_NUMBER (oben für Labels verwendet) und FIRST_VALUE. BigQuery, Snowflake und DuckDB ermöglichen es Ihnen, mit einer QUALIFY-Klausel nach dem Ergebnis einer Fensterfunktion zu filtern; bei anderen Systemen betten Sie die Abfrage in eine Unterabfrage ein und filtern außerhalb.

CTEs für mehrstufige Logik

Eine Common Table Expression benennt ein Zwischenergebnis mit WITH name AS (…), damit der nächste Schritt es verwenden kann. Ermittlungsfragen sind naturgemäß mehrstufig, und CTEs halten jeden Schritt lesbar und überprüfbar. Betrachten Sie eine typische Hypothese: Welche Adressen erhielten einen Token von einer als Exploit-Adresse gekennzeichneten Adresse und leiteten innerhalb von 24 Stunden mindestens 90 % davon weiter?

WITH hop1 AS (            -- Schritt 1: Empfang von einer als Exploit-Adresse gekennzeichneten Adresse
  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 weitergesendete Menge
  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. Er verwendet IN (subquery) statt eines Joins mit labels, sodass eine Exploit-Adresse mit zwei Labels die Mengen nicht verdoppeln kann. Schritt 2 summiert, welche Menge desselben Tokens jeder Empfänger in den 24 Stunden nach seinem ersten Empfang versendet hat. Der letzte Schritt behält Empfänger bei, die mindestens 90 % weitergeleitet haben. Sie können jede CTE einzeln ausführen, um ihre Zeilenanzahl zu prüfen, bevor Sie fortfahren.

Die Intervallsyntax variiert: INTERVAL '24' HOUR ist Standard-SQL und funktioniert in PostgreSQL, DuckDB und Trino; BigQuery verwendet TIMESTAMP_ADD(first_in, INTERVAL 24 HOUR). Behandeln Sie das Ergebnis als Ermittlungsansatz, nicht als Schlussfolgerung: Der Abfluss kann Mittel enthalten, die die Adresse bereits zuvor hielt – genau die Frage der Nachverfolgungsmethode, die in der Lektion Nachverfolgungsmethoden und ihre Konsequenzen behandelt wird. Die Nachverfolgung über mehrere Hops lässt sich mit WITH RECURSIVE formulieren, aber die Anzahl der Pfade wächst mit jedem Hop schnell an. Begrenzen Sie daher die Tiefe oder exportieren Sie die Kanten in ein Graphwerkzeug.

Prüfungsschwerpunkt: Inner Joins im Vergleich zu Left Joins und warum Joins zur Anreicherung Left Joins sein sollten; Join-Fan-out durch doppelte Labels und dessen Erkennung anhand der Zeilenanzahl; Beibehalten eines Labels pro Adresse mit ROW_NUMBER; warum eine Bedingung für die rechte Tabelle in WHERE einen Left Join in einen Inner Join verwandelt; Verknüpfen von Preisen über die auf die volle Stunde abgeschnittene Zeit, ASOF-Joins und Beibehalten von NULL bei fehlenden Preisen; laufende Salden mit SUM OVER, UNION ALL vorzeichenbehafteter Deltas, Sortierung nach Blocknummer und Log-Index sowie expliziten ROWS-Fensterrahmen; erstes und letztes Auftreten mit MIN- und MAX-Fenstern, LAG und QUALIFY; Strukturierung mehrstufiger Hypothesen mit CTEs und Semi-Joins.