UTXO-Daten abfragen

Bei Bitcoin und anderen UTXO-Blockchains gibt es keine Kontostände, die man einfach abfragen könnte: Jeder Kontostand, jede Gebühr und jeder Geldfluss muss rekonstruiert werden, indem man Outputs mit den Inputs verknüpft, die sie später ausgeben. Diese Verknüpfung ist ein Join, und ihn in SQL korrekt umzusetzen, ist die Grundlage für Kontostände, Nachverfolgung und Clustering in großem Maßstab. In dieser Lektion erfahren Sie, wie UTXO-Daten in Tabellen angeordnet sind, wie sich ausgegebene Outputs mit den entsprechenden Inputs verknüpfen lassen und wie sich die Heuristiken aus früheren Modulen in Abfragen umsetzen lassen.

Die Tabellen für Inputs und Outputs

UTXO-Datensätze teilen jede Transaktion auf zwei Granularitätsebenen in Zeilen auf. Das generische Schema dieses Moduls (Werte in Satoshis):

Ein Output wird durch seinen Outpoint identifiziert: den Transaktionshash zusammen mit dem Output-Index. Ein Input gibt über spent_transaction_hash und spent_output_index an, welchen Outpoint er ausgibt. In diesem Schema haben Coinbase-Transaktionen keine Zeilen in inputs; einige Datensätze enthalten stattdessen eine Coinbase-Input-Zeile mit einem leeren Outpoint. Prüfen Sie das, bevor Sie Einträge zählen.

Ein roher Bitcoin-Input enthält nur den Outpoint, ein Skript und eine Sequenznummer; er gibt weder eine Adresse noch einen Betrag an (siehe die Lektion Rohe Bitcoin-Transaktionen). Die Spalten address, value und script_type in inputs sind vorhanden, weil die ETL-Pipeline den ausgegebenen Output nachgeschlagen und dessen Felder übernommen hat – ein Schritt, den die Tools von blockchain-etl Enrichment nennen. Das ist praktisch, aber auch eine Abhängigkeit: Wenn das Enrichment für einige Blöcke fehlgeschlagen ist, haben die Inputs dort NULL-Werte, und jede darauf basierende Gebühr und jeder darauf basierende Kontostand ist falsch. Eine schnelle Prüfung ist, die Inputs mit NULL in value innerhalb Ihres Bereichs zu zählen.

Zwei weitere Details zur Datenanordnung sorgen bei Analystinnen und Analysten oft für Verwirrung:

• Adressarrays. Einige Datensätze, darunter der Bitcoin-Datensatz von BigQuery, speichern addresses als Array, weil einige Skripttypen (Bare-Multisig) mehrere Schlüssel enthalten und nicht standardisierte Skripte gar keine. Um eine Zeile pro Adresse zu erhalten, entpacken Sie das Array: CROSS JOIN UNNEST(addresses) AS address in BigQuery, CROSS JOIN UNNEST(addresses) AS t(address) in Trino und DuckDB, unnest() in PostgreSQL, LATERAL FLATTEN in Snowflake und explode() in Spark SQL. Dieselbe Syntax entpackt Inputs und Outputs, die als verschachtelte Datensätze in einer Transaktionstabelle gespeichert sind. Outputs ohne Adresse, etwa OP_RETURN-Datenausgaben, behalten einen NULL-Wert oder ein leeres Array.
• Doppelte Txids. Zwei Coinbase-Transaktionen aus dem Jahr 2010 haben denselben Txid wie frühere Coinbase-Transaktionen – ein Problem, das BIP 30 später untersagte. Ein Join allein auf Basis des Transaktionshashs ordnet diese wenigen Zeilen falsch zu. Wenn Genauigkeit wichtig ist, verwenden Sie den Hash und die Blocknummer als Schlüssel.

Ausgaben mit Inputs verknüpfen

Der Join von einem Input zu dem Output, den er ausgibt, bildet die Kante des UTXO-Ausgabegrafen (siehe die Lektion Das UTXO-Modell für Analystinnen und Analysten):

inputs.spent_transaction_hash = outputs.transaction_hash AND inputs.spent_output_index = outputs.output_index

Beide Bedingungen sind erforderlich. Ein Join nur über den Hash ordnet jeden Input jedem Output der früheren Transaktion zu.

Ein Output, den kein Input ausgibt, ist ein nicht ausgegebener Transaktionsoutput. UTXOs zu finden ist daher ein Anti-Join: Outputs werden mit Inputs links verknüpft, und es bleiben die Zeilen erhalten, für die kein Input gefunden wurde.

-- Nicht ausgegebene Outputs einer Adresse bis einschließlich Block 870.000
SELECT o.transaction_hash, o.output_index, o.value / 1e8 AS btc
FROM outputs AS o
LEFT JOIN inputs AS i
  ON  i.spent_transaction_hash = o.transaction_hash
  AND i.spent_output_index     = o.output_index
  AND i.block_number          <= 870000     -- nur Ausgaben bis zum Snapshot
WHERE o.address = 'bc1q…7k3f'
  AND o.block_number <= 870000
  AND i.transaction_hash IS NULL;           -- kein ausgebender Input gefunden

Die Abfrage berücksichtigt Outputs an die Adresse, die in Block 870.000 oder davor erstellt und bis zu diesem Block nicht ausgegeben wurden. Die Summe von value ergibt den Kontostand der Adresse auf dieser Blockhöhe. Im Beispiel erhielt die Adresse 0,05 und 0,03 BTC; der erste Output wurde in Block 870.000 ausgegeben und der zweite in Block 870.005. Der Kontostand in Block 870.000 beträgt daher 0,03 BTC.

Die Snapshot-Bedingung für i.block_number muss in der ON-Klausel stehen. Wird sie in die WHERE-Klausel verschoben, wird sie nach dem Join ausgewertet: Für nicht zugeordnete Outputs ist i.block_number NULL, die Bedingung ist nicht erfüllt, und die Abfrage gibt überhaupt nichts zurück. Auf das Beispiel angewendet, gibt sie null Zeilen statt der korrekten 0,03 BTC zurück. NOT EXISTS (SELECT 1 FROM inputs …) ist eine gleichwertige Möglichkeit, den Anti-Join zu formulieren, mit der sich diese Falle vermeiden lässt.

Kontostände und Gebühren berechnen

Eine Kontostandshistorie folgt dem Muster aus der Lektion Joins, Fensterfunktionen und CTEs für Geldflüsse: Outputs an die Adresse werden als positive Beträge und Inputs von der Adresse als negative Beträge behandelt, mit UNION ALL zusammengeführt und anschließend mit einer Fenstersumme nach Blockreihenfolge ausgewertet. Da die angereicherte Tabelle inputs die Adresse des ausgegebenen Outputs enthält, ist dafür kein Join erforderlich.

Die Gebühr einer Transaktion entspricht der Summe ihrer Inputs minus der Summe ihrer Outputs. Die naheliegende Abfrage, Inputs und Outputs über den Transaktionshash zu verknüpfen und beide zu summieren, ist falsch: Eine Transaktion mit 3 Inputs und 4 Outputs erzeugt 12 verknüpfte Zeilen, wodurch beide Summen vervielfacht werden. Aggregieren Sie jede Seite zuerst und verknüpfen Sie anschließend die Summen.

-- Gebühr pro Transaktion: jede Seite aggregieren und anschließend verknüpfen
WITH vin AS (
  SELECT transaction_hash, COUNT(*) AS n_in, SUM(value) AS in_sat
  FROM inputs WHERE block_number = 870000
  GROUP BY transaction_hash
),
vout AS (
  SELECT transaction_hash, COUNT(*) AS n_out, SUM(value) AS out_sat
  FROM outputs WHERE block_number = 870000
  GROUP BY transaction_hash
)
SELECT vout.transaction_hash, n_in, n_out,
       in_sat - out_sat AS fee_sat          -- Coinbase hat keine Inputs: durch den Join ausgeschlossen
FROM vout
JOIN vin ON vin.transaction_hash = vout.transaction_hash
ORDER BY fee_sat DESC;

Im Beispielblock gibt eine Transaktion 5.000.000 und 2.000.000 Satoshis aus und erstellt Outputs im Wert von 6.000.000 und 991.000 Satoshis. Ihre Gebühr beträgt somit 9.000 Satoshis. Die Coinbase-Transaktion hat keine Inputs und fällt beim Inner Join heraus – das ist korrekt, denn sie zahlt keine Gebühr. Für die Berechnung einer Gebührenrate teilen Sie durch die virtuelle Größe aus der Tabelle transactions. Viele Datensätze berechnen fee auch vorab. Eine Neuberechnung anhand eines Beispiels eignet sich gut, um ihr Enrichment zu überprüfen.

Change-Erkennung in SQL

Die Heuristiken aus der Lektion Heuristiken zur Change-Erkennung lassen sich in SQL als Kennzeichnungen umsetzen: als Spalten, die Kandidaten markieren, nicht als endgültige Schlussfolgerungen. Ein stärkeres Signal ist die Übereinstimmung des Skripttyps: Bei einer Transaktion mit zwei Outputs, deren Inputs alle denselben Skripttyp haben, ist ein Output dieses Typs ein Change-Kandidat, wenn der andere Output einen abweichenden Typ hat.

-- Change-Kandidaten: der einzige Output, dessen Skripttyp mit dem der Inputs übereinstimmt
WITH tx_in AS (
  SELECT transaction_hash, MIN(script_type) AS in_type
  FROM inputs WHERE block_number BETWEEN 870000 AND 870099
  GROUP BY transaction_hash
  HAVING COUNT(DISTINCT script_type) = 1      -- alle Inputs haben denselben Typ
),
outs AS (
  SELECT o.transaction_hash, o.output_index, o.address, o.value,
         CASE WHEN o.script_type = t.in_type THEN 1 ELSE 0 END AS type_match,
         COUNT(*) OVER (PARTITION BY o.transaction_hash) AS n_out
  FROM outputs AS o JOIN tx_in AS t ON t.transaction_hash = o.transaction_hash
  WHERE o.block_number BETWEEN 870000 AND 870099
)
SELECT transaction_hash, output_index, address, value,
       CASE WHEN MOD(value, 100000) <> 0 THEN 1 ELSE 0 END AS not_round
FROM (SELECT outs.*, SUM(type_match) OVER (PARTITION BY transaction_hash) AS n_match
      FROM outs) AS x
WHERE n_out = 2 AND n_match = 1 AND type_match = 1;

Der erste CTE behält Transaktionen, deren Inputs alle denselben Skripttyp haben. Der zweite kennzeichnet jeden Output, der diesem Typ entspricht, und zählt die Outputs pro Transaktion mithilfe einer Fensterfunktion. Die äußere Abfrage behält Transaktionen mit zwei Outputs, von denen genau einer dem Typ entspricht, und fügt ein schwächeres zweites Signal hinzu: ob der Betrag kein rundes Vielfaches von 100.000 Satoshis ist. Im Beispiel zahlen die Native-SegWit-Inputs 6.000.000 Satoshis an eine P2SH-Adresse und 991.000 an eine neue Native-SegWit-Adresse. Der Output über 991.000 Satoshis wird nach beiden Kriterien gekennzeichnet.

Speichern Sie Kennzeichnungen zusammen mit dem Namen und der Version der jeweiligen Heuristik in einer Tabelle und kombinieren Sie sie in der Analyse zu einem Konfidenzniveau – niemals in einem fest codierten Join. Die Übereinstimmung des Skripttyps versagt, wenn der Empfänger denselben Wallet-Typ verwendet, was mit der Verbreitung von Taproot immer häufiger vorkommt, oder wenn Wallets die Change-Typen gezielt variieren.

Cluster initialisieren

Die Heuristik gemeinsamer Input-Eigentümerschaft (siehe die Lektion Die Heuristik gemeinsamer Input-Eigentümerschaft) verknüpft alle Input-Adressen einer Transaktion. In SQL ergibt das eine Kantenliste: Paare von Adressen, die gemeinsam Inputs ausgegeben haben.

-- Kanten gemeinsamer Inputs; CoinJoin-ähnliche Transaktionen überspringen
WITH equal_outs AS (                    -- größte Gruppe von Outputs mit gleichem Wert
  SELECT transaction_hash, MAX(n) AS max_equal
  FROM (SELECT transaction_hash, value, COUNT(*) AS n
        FROM outputs WHERE block_number BETWEEN 870000 AND 870099
        GROUP BY transaction_hash, value) AS g
  GROUP BY transaction_hash
),
ins AS (
  SELECT i.transaction_hash, i.address,
         MIN(i.address) OVER (PARTITION BY i.transaction_hash) AS anchor
  FROM inputs AS i JOIN equal_outs AS e ON e.transaction_hash = i.transaction_hash
  WHERE i.block_number BETWEEN 870000 AND 870099
    AND e.max_equal < 3                 -- CoinJoin-ähnliche Transaktionen überspringen
)
SELECT DISTINCT anchor AS addr_a, address AS addr_b, transaction_hash
FROM ins
WHERE address <> anchor;                -- eine Kante pro zusätzlicher Input-Adresse

Der erste CTE ermittelt pro Transaktion die größte Anzahl von Outputs mit demselben Wert. Drei oder mehr gleichwertige Outputs sind ein grobes CoinJoin-Signal; solche Transaktionen werden ausgeschlossen. Der zweite verknüpft jede Input-Adresse mit einem Anker (der kleinsten Adresse der Transaktion). Dadurch ergeben sich bei einer Transaktion mit n Input-Adressen n − 1 Kanten statt aller möglichen Paare. Wenn der Transaktionshash an jeder Kante erhalten bleibt, ist die Beleggrundlage für den Bericht weiterhin vorhanden.

Um aus Kanten Cluster zu bilden, müssen zusammenhängende Komponenten gefunden werden. Das geschieht iterativ. Analystinnen und Analysten exportieren die Kantenliste üblicherweise in eine Graphbibliothek mit einem Union-Find-Algorithmus. Innerhalb von SQL funktioniert Label Propagation: Jede Adresse erhält ihre eigene ID; wiederholt wird sie durch die kleinste ID ihrer Nachbarn ersetzt, bis sich nichts mehr ändert. Das lässt sich mit WITH RECURSIVE ausdrücken, ist bei Daten für die gesamte Blockchain jedoch langsam. Unabhängig von der Methode sollten Sie auf Supercluster achten, Ausschlussregeln samt Version dokumentieren und das Ergebnis bewerten (siehe die Lektion Clusterqualität bewerten).

Prüfungsschwerpunkt: Der Outpoint (Transaktionshash plus Output-Index) als Join-Schlüssel zwischen Inputs und Outputs; das Enrichment von Inputs und seine Fehlerursache; das Entpacken von Adressarrays und die wichtigsten Syntaxunterschiede; das Auffinden von UTXOs und stichtagsbezogenen Kontoständen mit einem Anti-Join, wobei die Snapshot-Bedingung in der ON-Klausel stehen muss; die Berechnung von Gebühren durch getrennte Aggregation von Inputs und Outputs, um die Vervielfachung durch den Join zu vermeiden; Coinbase-Transaktionen und doppelte Txids gemäß BIP 30; Change-Erkennung als gespeicherte Kennzeichnungen mit Versionen; der Aufbau von Kantenlisten gemeinsamer Inputs mit CoinJoin-Ausschlüssen und die Berechnung von Clustern als zusammenhängende Komponenten.