Jointures, fonctions de fenêtre et CTE pour l’analyse des flux

Les requêtes sur une seule table répondent à la question « que s’est-il passé ? ». L’analyse des flux va plus loin : qui était la contrepartie, quelle était la valeur du transfert au moment où il a eu lieu, quel était le solde avant et après, et si les fonds ont rapidement été transférés ailleurs. Pour obtenir ces réponses, il faut utiliser des jointures, des fonctions de fenêtre et des expressions de table communes (CTE), les trois outils SQL abordés dans cette leçon, ainsi que les erreurs qui faussent le plus souvent leurs résultats.

Les requêtes utilisent le schéma générique de la leçon Fondamentaux SQL pour les tables de blockchain : token_transfers (une ligne par transfert de jeton fongible, avec amount_raw en unités de base), tokens (décimales), labels (adresse, entité, catégorie, source, niveau de confiance, date de mise à jour) et prices (actif, heure, prix en USD).

Jointures et piège de la multiplication des lignes

Une jointure combine les lignes de deux tables qui correspondent à une clé. Une jointure interne (JOIN) ne conserve que les lignes correspondantes des deux côtés ; une jointure gauche (LEFT JOIN) conserve chaque ligne de la table de gauche et remplit le côté droit avec des NULL lorsqu’il n’y a aucune correspondance. Pour enrichir des données (ajouter des étiquettes ou des prix aux transferts), la jointure gauche est presque toujours le bon choix, car un transfert sans étiquette ou sans prix reste un transfert.

L’erreur de jointure la plus préjudiciable est la multiplication des lignes. Si la table de droite contient plusieurs lignes pour une même clé, chaque ligne de gauche est répétée une fois pour chaque correspondance. Les tables d’étiquettes en sont l’exemple classique : une adresse de dépôt d’une plateforme d’échange peut avoir une étiquette issue d’un service de marquage et une autre provenant de la divulgation de la plateforme d’échange elle-même. Dans les données d’exemple, un dépôt de 100 USDC à une telle adresse apparaît deux fois après une jointure simple, et la somme des lignes jointes indique 200 USDC. Aucune erreur ne survient ; le résultat est simplement faux.

Deux habitudes permettent d’éviter ce problème. Avant la jointure, assurez-vous que le côté droit ne contient qu’une ligne par clé de jointure. Après la jointure, comparez le nombre de lignes à celui de la table d’origine : une jointure d’enrichissement ne doit jamais ajouter de lignes.

Joindre les transferts aux étiquettes

Pour corriger les étiquettes en double, classez-les et conservez-en une par adresse.

-- Une étiquette par adresse (confiance la plus élevée, puis la plus récente)
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                       -- conserver une étiquette par adresse
WHERE tt.from_address = '0x5e1f…c0de';

La CTE numérote les étiquettes de chaque adresse de la meilleure à la moins bonne ; la jointure ne conserve que le numéro 1. COALESCE remplace une étiquette manquante par le mot « unlabeled », qui est plus lisible dans le résultat que NULL.

Remarquez où se trouve l.rn = 1 : dans la clause ON, et non dans WHERE. Dans une jointure gauche, une condition portant sur la table de droite et placée dans WHERE est appliquée après la jointure ; elle supprime toutes les lignes dont le côté droit est NULL et transforme la jointure gauche en jointure interne. Toutes les contreparties sans étiquette disparaîtraient du résultat, sans que rien dans celui-ci ne vous en avertisse.

Pour étiqueter les deux extrémités de chaque transfert, joignez deux fois la même CTE avec des alias différents, par exemple LEFT JOIN best_label AS lf ON lf.address = tt.from_address AND lf.rn = 1 et LEFT JOIN best_label AS lt ON lt.address = tt.to_address AND lt.rn = 1. Le regroupement du résultat par les deux entités transforme des milliers de transferts entre adresses en un tableau synthétique de flux d’une entité à une autre, souvent la première vue dont le lecteur du rapport a besoin. Assurez-vous que les adresses sont stockées au même format des deux côtés : une adresse avec somme de contrôle et casse mixte dans la table d’étiquettes ne correspondra à aucune adresse dans une table de transferts en minuscules.

Choisir « la confiance la plus élevée l’emporte » est en soi une décision analytique. Consignez-la et incluez la source et le niveau de confiance de l’étiquette dans le rapport (voir la leçon Attribution et étiquetage). Si les étiquettes évoluent au fil du temps, joignez l’étiquette qui était valide au moment du transfert, et non celle d’aujourd’hui.

Joindre les prix

Pour évaluer un transfert, il faut lui associer un prix au bon moment. Avec une table de prix horaires, tronquez l’horodatage du transfert à l’heure et effectuez la jointure sur l’actif et l’heure.

-- Valeur en USD à l’heure de chaque transfert
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 : conserver les lignes sans prix
  ON p.asset = tt.token_address
 AND p.hour  = DATE_TRUNC('hour', tt.block_timestamp)
WHERE tt.from_address = '0x5e1f…c0de';

Dans l’exemple, un transfert de 500 USDC à un prix de 0,9998 vaut 499,90 USD, et 2 WETH à 2 500 valent 5 000 USD. Un transfert du jeton d’imitation « USDC » renvoie un prix NULL et une valeur NULL. C’est la bonne réponse : il n’existe pas de marché pour ce jeton, et la valeur NULL l’indique au lecteur. Ne remplacez jamais un prix manquant par zéro et n’utilisez jamais le prix d’un autre jeton portant le même symbole.

Les jointures par égalité nécessitent une ligne de prix pour chaque heure. Lorsque les prix sont peu nombreux, utilisez le prix antérieur le plus récent. DuckDB, ClickHouse et Snowflake proposent une clause ASOF JOIN précisément à cette fin ; sur les autres moteurs, une fonction de fenêtre ou une condition de plage permet d’obtenir le même résultat, mais plus lentement. La source du prix compte également : indiquez de quelle plateforme ou de quel indice il provient, et considérez les prix des jetons peu négociés comme des estimations susceptibles d’être manipulées.

Fonctions de fenêtre : soldes cumulés, première et dernière activité

Une fonction de fenêtre calcule une valeur pour chaque ligne à partir d’un ensemble de lignes associées, sans les regrouper comme le ferait GROUP BY. La syntaxe est function() OVER (PARTITION BY … ORDER BY … frame) : la partition définit le groupe, l’ordre définit la séquence et le cadre détermine quelles lignes sont prises en compte.

-- Solde cumulé d’un jeton pour une adresse, événement par événement
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;

La CTE transforme les transferts entrants en deltas positifs et les transferts sortants en deltas négatifs ; UNION ALL les empile (un UNION simple supprimerait les lignes identiques et ferait perdre de vrais transferts). La somme de fenêtre additionne ensuite les deltas dans l’ordre de la chaîne. Dans l’exemple, l’adresse reçoit 2 000 USDC et en envoie 500, 1 400 et 100 ; le solde passe donc à 2 000, puis 1 500, puis 100, puis 0 (en unités brutes, 2 000 000 000, et ainsi de suite). MIN et MAX sur une fenêtre vide OVER () donnent les heures de première et de dernière activité sur chaque ligne.

Trois détails comptent :

• Triez par numéro de bloc et index de journal, qui donnent ensemble la position exacte d’un événement dans la chaîne. Les horodatages sont identiques au sein d’un même bloc.
• Définissez explicitement le cadre. Avec ORDER BY et sans cadre, la valeur par défaut en SQL standard est RANGE … CURRENT ROW ; les lignes ayant les mêmes clés de tri sont alors traitées comme un seul groupe et reçoivent le même total.
• Vérifiez par rapport à l’état. Un solde calculé à partir des événements suppose que le jeton se comporte normalement. Les jetons à frais de transfert et les jetons à rebase invalident cette hypothèse ; comparez le résultat final à balanceOf au même bloc (voir la leçon Reconstituer les soldes et les historiques).

D’autres fonctions de fenêtre sont utiles : LAG et LEAD (la valeur de la ligne précédente ou suivante, par exemple le temps écoulé depuis le dernier transfert), ROW_NUMBER (utilisé plus haut pour les étiquettes) et FIRST_VALUE. BigQuery, Snowflake et DuckDB permettent de filtrer sur le résultat d’une fonction de fenêtre avec une clause QUALIFY ; sur les autres moteurs, enveloppez la requête dans une sous-requête et appliquez le filtre à l’extérieur.

CTE pour une logique en plusieurs étapes

Une expression de table commune nomme un résultat intermédiaire avec WITH name AS (…) afin que l’étape suivante puisse l’utiliser. Les questions d’enquête comportent naturellement plusieurs étapes, et les CTE rendent chacune d’elles lisible et vérifiable. Prenons une hypothèse classique : quelles adresses ont reçu un jeton d’une adresse d’exploitation étiquetée et en ont transféré au moins 90 % dans les 24 heures ?

WITH hop1 AS (            -- étape 1 : reçus d’une adresse d’exploitation étiquetée
  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 (                  -- étape 2 : montant retransféré dans les 24 heures suivant le premier transfert reçu
  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;      -- au moins 90 % retransférés

L’étape 1 trouve les destinataires du premier saut. Elle utilise IN (subquery) plutôt qu’une jointure avec labels, afin qu’une adresse d’exploitation ayant deux étiquettes ne puisse pas doubler les montants. L’étape 2 additionne les montants que chaque destinataire a envoyés sous la forme du même jeton dans les 24 heures suivant sa première réception. La dernière étape conserve les destinataires qui en ont retransféré au moins 90 %. Vous pouvez exécuter chaque CTE séparément pour vérifier son nombre de lignes avant de passer à la suite.

La syntaxe des intervalles varie : INTERVAL '24' HOUR est du SQL standard et fonctionne dans PostgreSQL, DuckDB et Trino ; BigQuery utilise TIMESTAMP_ADD(first_in, INTERVAL 24 HOUR). Considérez le résultat comme une piste, et non comme une conclusion : les sorties de fonds peuvent inclure des fonds que l’adresse détenait déjà, ce qui est précisément la question méthodologique du traçage abordée dans la leçon Suivre les fonds en amont et en aval. Le traçage de plusieurs sauts peut s’écrire avec WITH RECURSIVE, mais le nombre de chemins augmente rapidement à chaque saut ; limitez donc la profondeur ou exportez les arêtes vers un outil de graphes.

Points clés pour l’examen : différence entre les jointures internes et gauches, et raison pour laquelle les jointures d’enrichissement doivent être des jointures gauches ; multiplication des lignes due aux étiquettes en double et méthode pour la détecter en comparant les nombres de lignes ; conservation d’une étiquette par adresse avec ROW_NUMBER ; raison pour laquelle une condition portant sur la table de droite dans WHERE transforme une jointure gauche en jointure interne ; jointure des prix par heure tronquée, jointures ASOF et conservation des prix manquants sous forme de NULL ; soldes cumulés avec SUM OVER, UNION ALL de deltas signés, tri par numéro de bloc et index de journal, et cadres ROWS explicites ; première et dernière activité avec les fenêtres MIN et MAX, LAG et QUALIFY ; structuration d’hypothèses en plusieurs étapes avec des CTE et des semi-jointures.