Junções, funções de janela e CTEs para fluxos

Consultas a uma única tabela respondem "o que aconteceu". A análise de fluxos exige mais: quem era a contraparte, quanto a transferência valia naquele momento, qual era o saldo antes e depois e se os fundos foram movimentados rapidamente em seguida. Essas respostas exigem junções, funções de janela e expressões de tabela comuns (CTEs), as três ferramentas SQL abordadas nesta aula, juntamente com os erros que mais frequentemente distorcem seus resultados.

As consultas usam o esquema genérico da aula A pilha de análise e SQL para tabelas de blockchain: token_transfers (uma linha por transferência de token fungível, com amount_raw em unidades básicas), tokens (casas decimais), labels (endereço, entidade, categoria, fonte, grau, updated_at, em que grade é o grau de vínculo de A a D, sendo A o mais forte) e prices (ativo, hora, price_usd).

Junções e a armadilha da multiplicação de linhas

Uma junção combina linhas de duas tabelas que correspondem em uma chave. Uma junção interna (JOIN) mantém apenas as linhas que correspondem em ambos os lados; uma junção à esquerda (LEFT JOIN) mantém todas as linhas da tabela à esquerda e preenche o lado direito com NULLs quando não há correspondência. Para enriquecimento (adição de rótulos ou preços às transferências), a junção à esquerda quase sempre é a opção correta, porque uma transferência sem rótulo ou sem preço continua sendo uma transferência.

O erro de junção mais prejudicial é a multiplicação de linhas. Se a tabela à direita tiver várias linhas para a mesma chave, cada linha à esquerda será repetida uma vez por correspondência. As tabelas de rótulos são o caso clássico: um endereço de depósito de uma exchange pode ter um rótulo atribuído por um serviço de rotulagem e outro proveniente de uma divulgação da própria exchange. Nos dados de exemplo, um depósito de 100 USDC em um endereço desse tipo aparece duas vezes após uma junção simples, e uma soma das linhas resultantes da junção informa 200 USDC. Nenhum erro é gerado; o número simplesmente está errado.

Dois hábitos evitam isso. Antes da junção, garanta que o lado direito tenha uma única linha por chave de junção. Depois da junção, compare a contagem de linhas com a da tabela original: uma junção de enriquecimento nunca deve adicionar linhas.

Junção de transferências com rótulos

A solução para rótulos duplicados é classificá-los e manter um por endereço.

-- Um rótulo por endereço (melhor grau, depois o mais recente)
WITH best_label AS (
  SELECT address, entity, category,
         ROW_NUMBER() OVER (PARTITION BY address
                            ORDER BY grade ASC, updated_at DESC) AS rn  -- A primeiro
  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                       -- manter um rótulo por endereço
WHERE tt.from_address = '0x5e1f…c0de';

A CTE numera os rótulos de cada endereço do melhor para o pior; a junção mantém apenas o número 1. COALESCE substitui um rótulo ausente pela palavra "sem rótulo", que fica mais clara na saída do que NULL.

Observe onde está l.rn = 1: na cláusula ON, não em WHERE. Em uma junção à esquerda, uma condição sobre a tabela à direita colocada em WHERE é executada após a junção, descarta todas as linhas cujo lado direito é NULL e transforma a junção à esquerda novamente em uma junção interna. Todas as contrapartes sem rótulo desapareceriam do resultado, e nada na saída avisaria você.

Para rotular as duas pontas de cada transferência, faça a junção com a mesma CTE duas vezes, usando aliases diferentes, por exemplo 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. Agrupar o resultado pelas duas entidades transforma milhares de transferências no nível de endereço em uma tabela curta de fluxos entre entidades, que frequentemente é a primeira visão de que o leitor do relatório precisa. Garanta que ambos os lados armazenem endereços da mesma forma: um endereço com checksum e letras maiúsculas e minúsculas na tabela de rótulos não corresponde a nada em uma tabela de transferências com endereços em minúsculas.

Escolher "o melhor grau prevalece" já é, por si só, uma decisão analítica. Registre-a e inclua a fonte e o grau do rótulo no relatório (consulte a aula Do endereço à entidade e à pessoa: atribuição e rotulagem). Se os rótulos mudarem ao longo do tempo, faça a junção com o rótulo que era válido no momento da transferência, não com o de hoje.

Junção com preços

Determinar o valor de uma transferência significa associá-la a um preço no momento correto. Com uma tabela de preços horários, trunque o timestamp da transferência para a hora e faça a junção por ativo e hora.

-- Valor em USD na hora de cada transferência
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: manter linhas sem preço
  ON p.asset = tt.token_address
 AND p.hour  = DATE_TRUNC('hour', tt.block_timestamp)
WHERE tt.from_address = '0x5e1f…c0de';

No exemplo, uma transferência de 500 USDC a um preço de 0,9998 vale 499,90 USD, e 2 WETH a 2.500 valem 5.000 USD. Uma transferência do token de imitação "USDC" retorna preço NULL e valor NULL. Essa é a resposta correta: não há mercado para ele, e um NULL informa isso ao leitor. Nunca substitua um preço ausente por zero e nunca recorra ao preço de outro token com o mesmo símbolo.

Junções por igualdade precisam de uma linha de preço para cada hora. Quando os preços são esparsos, use o preço anterior mais recente. DuckDB, ClickHouse e Snowflake oferecem um ASOF JOIN exatamente para isso; em outros mecanismos, uma função de janela ou uma condição de intervalo faz o mesmo trabalho mais lentamente. A fonte do preço também importa: informe de qual plataforma ou índice ele vem e trate os preços de tokens pouco negociados como estimativas que podem ser manipuladas.

Funções de janela: saldos acumulados, primeira e última ocorrência

Uma função de janela calcula um valor para cada linha a partir de um conjunto de linhas relacionadas, sem agrupá-las em uma única linha, como faria GROUP BY. A sintaxe é function() OVER (PARTITION BY … ORDER BY … frame): a partição define o grupo, a ordenação define a sequência e a moldura define quais linhas entram no cálculo.

-- Saldo acumulado de um token para um endereço, evento por 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;

A CTE transforma transferências recebidas em deltas positivos e transferências enviadas em deltas negativos; UNION ALL as empilha (UNION simples removeria linhas idênticas e perderia transferências reais). A soma de janela então adiciona os deltas na ordem da blockchain. No exemplo, o endereço recebe 2.000 USDC e envia 500, 1.400 e 100, de modo que o saldo passa por 2.000, depois 1.500, depois 100 e, por fim, 0 (em unidades básicas, 2.000.000.000 e assim por diante). MIN e MAX sobre uma janela vazia OVER () fornecem os horários da primeira e da última ocorrência em cada linha.

Três detalhes são importantes:

• Ordene por número do bloco e índice do log, que juntos fornecem a posição exata de um evento na blockchain. Os timestamps são iguais dentro de um bloco.
• Escreva a moldura explicitamente. Com ORDER BY e sem uma moldura, o padrão no SQL padrão é RANGE … CURRENT ROW, que trata linhas com chaves de ordenação iguais como uma só e lhes atribui o mesmo total.
• Confira com o estado. Um saldo construído a partir de eventos pressupõe que o token se comporte normalmente. Tokens com taxa na transferência e tokens com reajuste de saldo quebram essa premissa; compare o valor final com balanceOf no mesmo bloco (consulte a aula O modelo de contas e a reconstrução de saldos).

Outras funções de janela úteis são LAG e LEAD (o valor da linha anterior ou seguinte, por exemplo, o tempo desde a última transferência), ROW_NUMBER (usada acima para rótulos) e FIRST_VALUE. BigQuery, Snowflake e DuckDB permitem filtrar um resultado de janela com uma cláusula QUALIFY; nos demais mecanismos, envolva a consulta em uma subconsulta e filtre externamente.

CTEs para lógica em várias etapas

Uma expressão de tabela comum nomeia um resultado intermediário com WITH name AS (…) para que a próxima etapa possa usá-lo. Perguntas investigativas naturalmente envolvem várias etapas, e as CTEs mantêm cada etapa legível e testável. Considere uma hipótese típica: quais endereços receberam um token de um endereço rotulado como envolvido em uma exploração e encaminharam pelo menos 90% dele em até 24 horas?

WITH hop1 AS (            -- etapa 1: recebido de um endereço rotulado como envolvido em uma exploração
  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 (                  -- etapa 2: quantia encaminhada em até 24 horas após o primeiro recebimento
  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;      -- encaminhou pelo menos 90%

A etapa 1 encontra os destinatários do primeiro salto. Ela usa IN (subquery) em vez de uma junção com labels, de modo que um endereço envolvido em uma exploração com dois rótulos não possa duplicar as quantias. A etapa 2 soma o que cada destinatário enviou do mesmo token nas 24 horas após seu primeiro recebimento. A etapa final mantém os destinatários que encaminharam pelo menos 90%. Você pode executar cada CTE isoladamente para conferir sua contagem de linhas antes de prosseguir.

A sintaxe de intervalo varia: INTERVAL '24' HOUR é SQL padrão e funciona em PostgreSQL, DuckDB e Trino; BigQuery usa TIMESTAMP_ADD(first_in, INTERVAL 24 HOUR). Trate o resultado como uma pista, não como uma conclusão: a saída pode incluir fundos que o endereço já detinha, o que é exatamente a questão de método de rastreamento discutida na aula Métodos de rastreamento e suas consequências. O rastreamento de múltiplos saltos pode ser escrito com WITH RECURSIVE, mas o número de caminhos cresce rapidamente a cada salto; portanto, limite a profundidade ou exporte as arestas para uma ferramenta de grafos.

Foco no exame: junções internas versus junções à esquerda e por que junções de enriquecimento devem ser junções à esquerda; multiplicação de linhas em junções causada por rótulos duplicados e como detectá-la com contagens de linhas; manutenção de um rótulo por endereço com ROW_NUMBER; por que uma condição sobre a tabela à direita em WHERE transforma uma junção à esquerda em uma junção interna; junção de preços pela hora truncada, junções ASOF e manutenção de preços ausentes como NULL; saldos acumulados com SUM OVER, UNION ALL de deltas com sinal, ordenação por número do bloco e índice do log e molduras ROWS explícitas; primeira e última ocorrência com janelas MIN e MAX, LAG e QUALIFY; estruturação de hipóteses em várias etapas com CTEs e semijunções.