Join, funzioni finestra e CTE per i flussi

Le query su una singola tabella rispondono alla domanda "che cosa è accaduto". L'analisi dei flussi richiede di più: chi fosse la controparte, quale fosse il valore del trasferimento in quel momento, quale fosse il saldo prima e dopo e se i fondi siano stati trasferiti rapidamente altrove. Queste risposte richiedono join, funzioni finestra ed espressioni di tabella comuni (CTE), i tre strumenti SQL trattati in questa lezione, insieme agli errori che più spesso ne distorcono i risultati.

Le query utilizzano lo schema generico della lezione Lo stack di analisi e SQL per le tabelle blockchain: token_transfers (una riga per ogni trasferimento di token fungibili, con amount_raw in unità base), tokens (decimals), labels (address, entity, category, source, grade, updated_at, dove grade è il grado del collegamento da A a D, con A come grado più forte) e prices (asset, hour, price_usd).

Join e la trappola del fan-out

Un join combina righe di due tabelle che corrispondono in base a una chiave. Un inner join (JOIN) mantiene solo le righe che trovano corrispondenza su entrambi i lati; un left join (LEFT JOIN) mantiene tutte le righe della tabella di sinistra e inserisce valori NULL sul lato destro quando non vi è alcuna corrispondenza. Per l'arricchimento (l'aggiunta di etichette o prezzi ai trasferimenti), il left join è quasi sempre la scelta corretta, perché un trasferimento privo di etichetta o di prezzo rimane comunque un trasferimento.

L'errore di join più dannoso è il fan-out. Se la tabella di destra contiene più righe per la stessa chiave, ogni riga di sinistra viene ripetuta una volta per ciascuna corrispondenza. Le tabelle delle etichette sono il caso classico: un indirizzo di deposito di un exchange può avere un'etichetta derivante dall'etichettatura di un servizio e un'altra derivante da una comunicazione dell'exchange stesso. Nei dati di esempio, un deposito di 100 USDC verso un indirizzo di questo tipo compare due volte dopo un semplice join, e la somma delle righe risultanti dal join riporta 200 USDC. Non viene segnalato alcun errore; il numero è semplicemente sbagliato.

Due abitudini prevengono questo problema. Prima del join, rendere univoco il lato destro rispetto alla chiave di join. Dopo il join, confrontare il numero di righe con quello della tabella originale: un join di arricchimento non deve mai aggiungere righe.

Unire i trasferimenti alle etichette

La soluzione per le etichette duplicate consiste nell'ordinarle per priorità e mantenerne una sola per indirizzo.

-- Una sola etichetta per indirizzo (grado migliore, poi la più recente)
WITH best_label AS (
  SELECT address, entity, category,
         ROW_NUMBER() OVER (PARTITION BY address
                            ORDER BY grade ASC, updated_at DESC) AS rn  -- Prima A
  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                       -- mantenere una sola etichetta per indirizzo
WHERE tt.from_address = '0x5e1f…c0de';

La CTE numera le etichette di ciascun indirizzo dalla migliore alla peggiore; il join mantiene solo la numero 1. COALESCE sostituisce un'etichetta mancante con la parola "unlabeled", cioè "senza etichetta", che risulta più leggibile di NULL nell'output.

Si noti dove si trova l.rn = 1: nella clausola ON, non in WHERE. In un left join, una condizione sulla tabella di destra inserita in WHERE viene eseguita dopo il join, elimina tutte le righe il cui lato destro è NULL e trasforma nuovamente il left join in un inner join. Tutte le controparti prive di etichetta scomparirebbero dal risultato, senza alcun avvertimento nell'output.

Per etichettare entrambe le estremità di ogni trasferimento, unire la stessa CTE due volte con alias diversi, ad esempio LEFT JOIN best_label AS lf ON lf.address = tt.from_address AND lf.rn = 1 e LEFT JOIN best_label AS lt ON lt.address = tt.to_address AND lt.rn = 1. Raggruppando poi il risultato per le due entità, si trasformano migliaia di trasferimenti a livello di indirizzo in una breve tabella di flussi tra entità, che spesso rappresenta la prima vista necessaria al lettore della relazione. Assicurarsi che entrambi i lati memorizzino gli indirizzi nello stesso modo: un indirizzo con checksum e lettere maiuscole e minuscole nella tabella delle etichette non trova alcuna corrispondenza in una tabella dei trasferimenti con indirizzi tutti in minuscolo.

La scelta di far prevalere il "grado migliore" è di per sé una decisione analitica. Documentarla e riportare nella relazione la fonte e il grado dell'etichetta (si veda la lezione Dall'indirizzo all'entità alla persona: attribuzione ed etichettatura). Se le etichette cambiano nel tempo, effettuare il join con l'etichetta valida al momento del trasferimento, non con quella attuale.

Unire i trasferimenti ai prezzi

Valutare un trasferimento significa associarlo a un prezzo riferito al momento corretto. Con una tabella di prezzi orari, troncare il timestamp del trasferimento all'ora ed effettuare il join in base all'asset e all'ora.

-- Valore in USD all'ora di ciascun trasferimento
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: mantenere le righe prive di prezzo
  ON p.asset = tt.token_address
 AND p.hour  = DATE_TRUNC('hour', tt.block_timestamp)
WHERE tt.from_address = '0x5e1f…c0de';

Nei dati di esempio, un trasferimento di 500 USDC a un prezzo di 0,9998 vale 499,90 USD, e 2 WETH a 2.500 valgono 5.000 USD. Un trasferimento del token contraffatto "USDC" restituisce un prezzo NULL e un valore NULL. Questa è la risposta corretta: non esiste un mercato per quel token, e NULL lo segnala al lettore. Non sostituire mai un prezzo mancante con zero e non ripiegare mai sul prezzo di un altro token con lo stesso simbolo.

I join per uguaglianza richiedono una riga di prezzo per ogni ora. Quando i prezzi sono disponibili solo in modo sporadico, utilizzare il prezzo precedente più recente. DuckDB, ClickHouse e Snowflake offrono un ASOF JOIN proprio a questo scopo; su altri motori, una funzione finestra o una condizione su un intervallo svolge lo stesso compito più lentamente. Anche la fonte del prezzo è importante: specificare da quale sede di negoziazione o indice provenga e trattare i prezzi dei token poco scambiati come stime suscettibili di manipolazione.

Funzioni finestra: saldi progressivi, prima e ultima osservazione

Una funzione finestra calcola un valore per ciascuna riga a partire da un insieme di righe correlate senza accorparle, come farebbe GROUP BY. La sintassi è function() OVER (PARTITION BY … ORDER BY … frame): la partizione definisce il gruppo, l'ordinamento la sequenza e il frame quali righe vengono considerate.

-- Saldo progressivo di un token per un indirizzo, evento per evento
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 trasforma i trasferimenti in entrata in variazioni positive e quelli in uscita in variazioni negative; UNION ALL li accoda (un semplice UNION eliminerebbe le righe identiche, facendo perdere trasferimenti reali). La somma nella finestra aggiunge quindi le variazioni nell'ordine della blockchain. Nei dati di esempio, l'indirizzo riceve 2.000 USDC e ne invia 500, 1.400 e 100, quindi il saldo passa da 2.000 a 1.500, poi a 100 e infine a 0 (in unità grezze, 2.000.000.000 e così via). MIN e MAX su una finestra vuota OVER () restituiscono i tempi della prima e dell'ultima osservazione su ogni riga.

Tre dettagli sono importanti:

• Ordinare per numero di blocco e indice del log, che insieme forniscono la posizione esatta di un evento nella blockchain. I timestamp sono uguali all'interno di un blocco.
• Scrivere esplicitamente il frame. Con ORDER BY e senza frame, il valore predefinito nello standard SQL è RANGE … CURRENT ROW, che tratta le righe con chiavi di ordinamento uguali come un'unica riga e assegna loro lo stesso totale.
• Verificare rispetto allo stato. Un saldo ricostruito dagli eventi presuppone che il token si comporti normalmente. I token con commissioni sul trasferimento e quelli con rebase invalidano questa ipotesi; confrontare il valore finale con balanceOf allo stesso blocco (si veda la lezione Il modello ad account e la ricostruzione dei saldi).

Altre funzioni finestra utili sono LAG e LEAD (il valore della riga precedente o successiva, ad esempio il tempo trascorso dall'ultimo trasferimento), ROW_NUMBER (utilizzata sopra per le etichette) e FIRST_VALUE. BigQuery, Snowflake e DuckDB consentono di filtrare in base al risultato di una funzione finestra con una clausola QUALIFY; altrove, racchiudere la query in una sottoquery e applicare il filtro all'esterno.

CTE per la logica in più passaggi

Un'espressione di tabella comune assegna un nome a un risultato intermedio con WITH name AS (…), affinché il passaggio successivo possa utilizzarlo. Le domande investigative si articolano naturalmente in più passaggi, e le CTE rendono ogni passaggio leggibile e verificabile. Si consideri un'ipotesi tipica: quali indirizzi hanno ricevuto un token da un indirizzo etichettato come coinvolto in un exploit e ne hanno inoltrato almeno il 90% entro 24 ore?

WITH hop1 AS (            -- passaggio 1: ricevuto da un indirizzo etichettato come coinvolto in un exploit
  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 (                  -- passaggio 2: importo inoltrato entro 24 ore dalla prima ricezione
  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;      -- inoltrato almeno il 90%

Il passaggio 1 individua i destinatari del primo salto. Utilizza IN (subquery) anziché un join con labels, così un indirizzo coinvolto in un exploit con due etichette non può raddoppiare gli importi. Il passaggio 2 somma quanto ciascun destinatario ha inviato dello stesso token nelle 24 ore successive alla prima ricezione. Il passaggio finale mantiene i destinatari che ne hanno inoltrato almeno il 90%. È possibile eseguire ogni CTE separatamente per verificarne il numero di righe prima di proseguire.

La sintassi degli intervalli varia: INTERVAL '24' HOUR è SQL standard e funziona in PostgreSQL, DuckDB e Trino; BigQuery utilizza TIMESTAMP_ADD(first_in, INTERVAL 24 HOUR). Trattare il risultato come una pista investigativa, non come una conclusione: il deflusso può includere fondi che l'indirizzo deteneva già in precedenza, ed è proprio questa la questione relativa al metodo di tracciamento discussa nella lezione Metodi di tracciamento e relative conseguenze. Il tracciamento su più salti può essere scritto con WITH RECURSIVE, ma il numero di percorsi cresce rapidamente a ogni salto, quindi limitare la profondità o esportare gli archi in uno strumento per grafi.

Focus sull'esame: inner join e left join a confronto e perché i join di arricchimento dovrebbero essere left join; fan-out dei join dovuto a etichette duplicate e come rilevarlo tramite il conteggio delle righe; mantenere una sola etichetta per indirizzo con ROW_NUMBER; perché una condizione sulla tabella di destra in WHERE trasforma un left join in un inner join; unire i prezzi in base all'ora troncata, join ASOF e mantenimento dei prezzi mancanti come NULL; saldi progressivi con SUM OVER, UNION ALL di variazioni con segno, ordinamento per numero di blocco e indice del log e frame ROWS espliciti; prima e ultima osservazione con finestre MIN e MAX, LAG e QUALIFY; strutturazione di ipotesi in più passaggi con CTE e semi-join.