Jointures, fonctions de fenêtrage et CTE pour les 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 à ce moment-là, quel était le solde avant et après, et les fonds ont-ils été rapidement transférés ailleurs ? Ces réponses nécessitent des jointures, des fonctions de fenêtrage 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 La pile analytique et SQL pour les tables de blockchain : token_transfers (une ligne par transfert de jetons fongibles, avec amount_raw en unités de base), tokens (décimales), labels (address, entity, category, source, grade, updated_at, où grade correspond au niveau de confiance du lien, de A à D, A étant le plus élevé) et prices (asset, hour, price_usd).

Les jointures et le piège de la multiplication des lignes

Une jointure combine les lignes de deux tables qui correspondent sur une clé. Une jointure interne (JOIN) ne conserve que les lignes qui correspondent des deux côtés ; une jointure gauche (LEFT JOIN) conserve toutes les lignes de la table de gauche et remplit le côté droit avec des NULL lorsqu’aucune correspondance n’est trouvée. Pour l’enrichissement (ajout d’étiquettes ou de 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 dommageable est la multiplication des lignes. Si la table de droite contient plusieurs lignes pour la même clé, chaque ligne de gauche est répétée une fois par correspondance. Les tables d’étiquettes en sont le cas classique : une adresse de dépôt de plateforme d'échange peut porter une étiquette issue de l’étiquetage d’un service et une autre provenant d’une divulgation de la plateforme d'échange elle-même. Dans les données d’exemple, un dépôt de 100 USDC vers une telle adresse apparaît deux fois après une simple jointure, et la somme des lignes jointes indique 200 USDC. Aucune erreur n’est signalée ; le nombre est simplement faux.

Deux habitudes permettent d’éviter cela. Avant la jointure, rendez les valeurs de la clé de jointure uniques du côté droit. 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 doublons d’étiquettes, il faut les classer et n’en conserver qu’une par adresse.

-- Une étiquette par adresse (meilleur niveau, puis la plus récente)
WITH best_label AS (
  SELECT address, entity, category,
         ROW_NUMBER() OVER (PARTITION BY address
                            ORDER BY grade ASC, updated_at DESC) AS rn  -- A en premier
  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 » (« sans étiquette »), plus lisible dans un 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 placée dans WHERE s’exécute après la jointure, élimine 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 qu’aucun élément de la sortie ne vous en avertisse.

Pour étiqueter les deux extrémités de chaque transfert, joignez deux fois la même CTE sous 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 alors des milliers de transferts entre adresses en un tableau synthétique des flux entre entités, ce qui constitue souvent la première vue dont le lecteur du rapport a besoin. Assurez-vous que les deux côtés stockent les adresses de la même manière : une adresse avec somme de contrôle, mêlant majuscules et minuscules dans la table d’étiquettes, ne correspondra à rien dans une table de transferts où les adresses sont en minuscules.

Choisir que « le meilleur niveau de confiance l’emporte » est en soi une décision analytique. Consignez-la et intégrez la source et le niveau de confiance de l’étiquette au rapport (voir la leçon De l’adresse à l’entité, puis à la personne : attribution et étiquetage). Si les étiquettes évoluent au fil du temps, effectuez la jointure avec l’étiquette qui était valide au moment du transfert, et non celle d’aujourd’hui.

Joindre les prix

Valoriser un transfert consiste à 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 aucun marché pour ce jeton, et NULL l’indique au lecteur. Ne remplacez jamais un prix manquant par zéro et n’utilisez jamais à défaut 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 fréquents, utilisez le prix antérieur le plus récent. DuckDB, ClickHouse et Snowflake proposent une jointure ASOF JOIN précisément à cet effet ; sur les autres moteurs, une fonction de fenêtrage ou une condition de plage accomplit la même tâche, mais plus lentement. La source du prix compte également : indiquez de quelle place de marché ou de quel indice il provient, et considérez les prix des jetons peu échangés comme des estimations susceptibles d’être manipulées.

Fonctions de fenêtrage : soldes cumulés, première et dernière observations

Une fonction de fenêtrage calcule une valeur pour chaque ligne à partir d’un ensemble de lignes lié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éfinit les lignes 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 variations positives et les transferts sortants en variations négatives ; UNION ALL les empile (un simple UNION supprimerait les lignes identiques et ferait perdre des transferts réels). La somme sur la fenêtre additionne ensuite les variations dans l’ordre de la chaîne. Dans l’exemple, l’adresse reçoit 2 000 USDC et envoie 500, 1 400 et 100 ; le solde passe donc de 2 000 à 1 500, puis à 100 et enfin à 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 observations sur chaque ligne.

Trois détails sont importants :

• Triez par numéro de bloc et par 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.
• Écrivez explicitement le cadre. Avec ORDER BY et sans cadre, la valeur par défaut en SQL standard est RANGE … CURRENT ROW, qui traite comme un ensemble les lignes ayant les mêmes clés de tri et leur attribue le même total.
• Vérifiez par rapport à l’état. Un solde reconstruit à partir des événements suppose que le jeton se comporte normalement. Les jetons avec frais de transfert et ceux à réajustement de l’offre invalident cette hypothèse ; comparez le résultat final à balanceOf au même bloc (voir la leçon Le modèle de comptes et la reconstruction des soldes).

D’autres fonctions de fenêtrage utiles sont 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ée ci-dessus pour les étiquettes) et FIRST_VALUE. BigQuery, Snowflake et DuckDB vous permettent de filtrer le résultat d’une fonction de fenêtrage avec une clause QUALIFY ; ailleurs, encapsulez la requête dans une sous-requête et filtrez à l’extérieur.

Les CTE pour une logique en plusieurs étapes

Une expression de table commune donne un nom à 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 permettent de garder chaque étape lisible et testable. Prenons une hypothèse typique : quelles adresses ont reçu un jeton d’une adresse étiquetée comme liée à une exploitation de vulnérabilité et en ont retransféré au moins 90 % dans les 24 heures ?

WITH hop1 AS (            -- étape 1 : réception depuis une adresse étiquetée comme liée à une exploitation de vulnérabilité
  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 la première réception
  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, de sorte qu’une adresse liée à une exploitation de vulnérabilité portant deux étiquettes ne puisse pas doubler les montants. L’étape 2 additionne les montants du même jeton envoyés par chaque destinataire dans les 24 heures suivant sa première réception. L’étape finale conserve les destinataires qui ont retransféré au moins 90 %. Vous pouvez exécuter chaque CTE séparément pour vérifier son nombre de lignes avant de poursuivre.

La syntaxe des intervalles varie : INTERVAL '24' HOUR relève 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 : le flux sortant peut inclure des fonds que l’adresse détenait auparavant, ce qui est précisément la question de méthode de traçage abordée dans la leçon Les méthodes de traçage et leurs conséquences. Le traçage à 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 d’analyse de graphes.

Points clés pour l'examen : jointures internes et jointures gauches, et raisons pour lesquelles les jointures d’enrichissement doivent être des jointures gauches ; multiplication des lignes due aux étiquettes en double et détection par le nombre de lignes ; conservation d’une seule étiquette par adresse avec ROW_NUMBER ; raison pour laquelle une condition sur la table de droite dans WHERE transforme une jointure gauche en jointure interne ; jointure des prix sur l’heure tronquée, jointures ASOF et maintien des prix manquants à NULL ; soldes cumulés avec SUM OVER, UNION ALL des variations signées, tri par numéro de bloc et index de journal, et cadres ROWS explicites ; première et dernière observations avec les fenêtres MIN et MAX, LAG et QUALIFY ; structuration des hypothèses en plusieurs étapes avec des CTE et des semi-jointures.