Joins, funciones de ventana y CTE para flujos

Las consultas sobre una sola tabla responden a «qué ocurrió». El análisis de flujos plantea más preguntas: quién era la contraparte, cuánto valía la transferencia en ese momento, cuál era el saldo antes y después, y si los fondos se transfirieron rápidamente a otro destino. Para responderlas se necesitan joins, funciones de ventana y expresiones de tabla comunes (CTE), las tres herramientas de SQL que aborda esta lección, junto con los errores que distorsionan sus resultados con mayor frecuencia.

Las consultas utilizan el esquema genérico de la lección El conjunto de herramientas de análisis y SQL para tablas de blockchain: token_transfers (una fila por transferencia de tokens fungibles, con amount_raw en unidades base), tokens (decimales), labels (address, entity, category, source, grade, updated_at, donde grade es el grado de vinculación de A a D, siendo A el más sólido) y prices (asset, hour, price_usd).

Joins y la trampa de la multiplicación de filas

Un join combina filas de dos tablas que coinciden en una clave. Un inner join (JOIN) conserva solo las filas que coinciden en ambos lados; un left join (LEFT JOIN) conserva todas las filas de la tabla izquierda y completa el lado derecho con valores NULL cuando no hay coincidencias. Para el enriquecimiento (añadir etiquetas o precios a las transferencias), el left join es casi siempre la opción correcta, porque una transferencia sin etiqueta o sin precio sigue siendo una transferencia.

El error más perjudicial de los joins es la multiplicación de filas. Si la tabla derecha tiene varias filas para la misma clave, cada fila de la izquierda se repite una vez por cada coincidencia. Las tablas de etiquetas son el caso clásico: una dirección de depósito de un exchange puede tener una etiqueta procedente del etiquetado de servicios y otra procedente de una divulgación del propio exchange. En los datos de ejemplo, un depósito de 100 USDC en una dirección de este tipo aparece dos veces tras un join simple, y una suma de las filas combinadas arroja 200 USDC. No se produce ningún error; la cifra simplemente es incorrecta.

Dos hábitos evitan este problema. Antes de realizar el join, asegúrese de que el lado derecho tenga una sola fila por clave de unión. Después del join, compare el número de filas con el de la tabla original: un join de enriquecimiento nunca debe añadir filas.

Combinar transferencias con etiquetas

La solución para las etiquetas duplicadas consiste en clasificarlas y conservar una por dirección.

-- Una etiqueta por dirección (mejor grado y luego la más reciente)
WITH best_label AS (
  SELECT address, entity, category,
         ROW_NUMBER() OVER (PARTITION BY address
                            ORDER BY grade ASC, updated_at DESC) AS rn  -- A primero
  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                       -- conservar una etiqueta por dirección
WHERE tt.from_address = '0x5e1f…c0de';

La CTE numera las etiquetas de cada dirección de la mejor a la peor; el join conserva solo la número 1. COALESCE sustituye una etiqueta ausente por la palabra "unlabeled" («sin etiqueta»), que resulta más clara en un resultado que NULL.

Observe dónde se encuentra l.rn = 1: en la cláusula ON, no en WHERE. En un left join, una condición sobre la tabla derecha situada en WHERE se ejecuta después del join, descarta todas las filas cuyo lado derecho es NULL y convierte el left join en un inner join. Todas las contrapartes sin etiqueta desaparecerían del resultado, y nada en él se lo advertiría.

Para etiquetar ambos extremos de cada transferencia, combine la misma CTE dos veces con alias diferentes, por ejemplo LEFT JOIN best_label AS lf ON lf.address = tt.from_address AND lf.rn = 1 y LEFT JOIN best_label AS lt ON lt.address = tt.to_address AND lt.rn = 1. Al agrupar el resultado por las dos entidades, miles de transferencias entre direcciones se convierten en una tabla breve de flujos entre entidades, que suele ser la primera vista que necesita quien lee el informe. Asegúrese de que ambos lados almacenen las direcciones de la misma manera: una dirección con suma de verificación y combinación de mayúsculas y minúsculas en la tabla de etiquetas no encontrará coincidencias en una tabla de transferencias que use minúsculas.

Elegir «gana el mejor grado» es, en sí mismo, una decisión analítica. Documéntela e incluya la fuente y el grado de la etiqueta en el informe (consulte la lección De la dirección a la entidad y a la persona: atribución y etiquetado). Si las etiquetas cambian con el tiempo, realice el join con la etiqueta que era válida en el momento de la transferencia, no con la actual.

Combinar con precios

Valorar una transferencia implica vincularla con un precio en el momento adecuado. Con una tabla de precios por hora, trunque la marca de tiempo de la transferencia a la hora y realice el join por activo y hora.

-- Valor en USD a la hora de cada transferencia
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: conservar filas sin precio
  ON p.asset = tt.token_address
 AND p.hour  = DATE_TRUNC('hour', tt.block_timestamp)
WHERE tt.from_address = '0x5e1f…c0de';

En el ejemplo, una transferencia de 500 USDC a un precio de 0,9998 vale 499,90 USD, y 2 WETH a 2.500 valen 5.000 USD. Una transferencia del token de imitación "USDC" devuelve un precio NULL y un valor NULL. Esa es la respuesta correcta: no existe un mercado para él, y NULL se lo indica al lector. Nunca sustituya un precio ausente por cero ni recurra al precio de otro token con el mismo símbolo.

Los joins por igualdad necesitan una fila de precio para cada hora. Cuando hay pocos datos de precios, utilice el precio anterior más reciente. DuckDB, ClickHouse y Snowflake ofrecen un ASOF JOIN precisamente para esto; en otros motores, una función de ventana o una condición de rango realiza la misma tarea con mayor lentitud. La fuente del precio también importa: indique de qué plataforma de negociación o índice procede y trate los precios de tokens con escaso volumen de negociación como estimaciones que pueden manipularse.

Funciones de ventana: saldos acumulados y primera y última observación

Una función de ventana calcula un valor para cada fila a partir de un conjunto de filas relacionadas sin agruparlas en una sola, como haría GROUP BY. La sintaxis es function() OVER (PARTITION BY … ORDER BY … frame): la partición define el grupo; el orden, la secuencia; y el marco, qué filas se tienen en cuenta.

-- Saldo acumulado de un token para una dirección, 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;

La CTE convierte las transferencias entrantes en variaciones positivas y las salientes en variaciones negativas; UNION ALL las apila (un UNION simple eliminaría filas idénticas y haría que se perdieran transferencias reales). A continuación, la suma de ventana añade las variaciones en el orden de la cadena. En el ejemplo, la dirección recibe 2.000 USDC y envía 500, 1.400 y 100, por lo que el saldo pasa por 2.000, luego 1.500, luego 100 y finalmente 0 (en unidades base, 2.000.000.000 y así sucesivamente). MIN y MAX sobre una ventana vacía OVER () proporcionan las horas de primera y última observación en cada fila.

Hay tres detalles importantes:

• Ordene por número de bloque e índice de registro, que juntos proporcionan la posición exacta de un evento en la cadena. Las marcas de tiempo coinciden dentro de un bloque.
• Escriba el marco explícitamente. Con ORDER BY y sin marco, el valor predeterminado en SQL estándar es RANGE … CURRENT ROW, que trata las filas con claves de ordenación iguales como una sola y les asigna el mismo total.
• Contraste con el estado. Un saldo calculado a partir de eventos presupone que el token se comporta de forma normal. Los tokens con comisión por transferencia y los de reajuste de saldo rompen este supuesto; compare la cifra final con balanceOf en el mismo bloque (consulte la lección El modelo de cuentas y la reconstrucción de saldos).

Otras funciones de ventana útiles son LAG y LEAD (el valor de la fila anterior o siguiente, por ejemplo, el tiempo transcurrido desde la última transferencia), ROW_NUMBER (utilizada antes para las etiquetas) y FIRST_VALUE. BigQuery, Snowflake y DuckDB permiten filtrar por el resultado de una ventana mediante una cláusula QUALIFY; en otros motores, incluya la consulta en una subconsulta y filtre fuera de ella.

CTE para lógica de varios pasos

Una expresión de tabla común asigna un nombre a un resultado intermedio mediante WITH name AS (…) para que el siguiente paso pueda utilizarlo. Las preguntas de investigación implican naturalmente varios pasos, y las CTE mantienen cada paso legible y verificable. Considere una hipótesis habitual: ¿qué direcciones recibieron un token de una dirección etiquetada como vinculada a un exploit y reenviaron al menos el 90% en un plazo de 24 horas?

WITH hop1 AS (            -- paso 1: recibido de una dirección etiquetada como vinculada a 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 (                  -- paso 2: importe reenviado en las 24 horas posteriores a la primera recepción
  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;      -- reenviado al menos el 90%

El paso 1 encuentra a los destinatarios del primer salto. Utiliza IN (subquery) en lugar de un join con labels, de modo que una dirección vinculada a un exploit con dos etiquetas no pueda duplicar los importes. El paso 2 suma lo que cada destinatario envió del mismo token en las 24 horas posteriores a su primera recepción. El paso final conserva a los destinatarios que reenviaron al menos el 90%. Puede ejecutar cada CTE por separado para comprobar su número de filas antes de continuar.

La sintaxis de los intervalos varía: INTERVAL '24' HOUR es SQL estándar y funciona en PostgreSQL, DuckDB y Trino; BigQuery utiliza TIMESTAMP_ADD(first_in, INTERVAL 24 HOUR). Trate el resultado como una pista, no como una conclusión: la salida puede incluir fondos que la dirección ya tenía, que es precisamente la cuestión sobre los métodos de rastreo tratada en la lección Métodos de rastreo y sus consecuencias. El rastreo de varios saltos puede escribirse con WITH RECURSIVE, pero el número de rutas crece rápidamente con cada salto, por lo que debe limitar la profundidad o exportar las aristas a una herramienta de grafos.

Enfoque del examen: inner joins frente a left joins y por qué los joins de enriquecimiento deben ser left joins; multiplicación de filas en los joins por etiquetas duplicadas y cómo detectarla mediante el número de filas; conservar una etiqueta por dirección con ROW_NUMBER; por qué una condición sobre la tabla derecha en WHERE convierte un left join en un inner join; combinar precios por hora truncada, joins ASOF y dejar los precios ausentes como NULL; saldos acumulados con SUM OVER, UNION ALL de variaciones con signo, ordenación por número de bloque e índice de registro y marcos ROWS explícitos; primera y última observación con ventanas MIN y MAX, LAG y QUALIFY; estructurar hipótesis de varios pasos con CTE y semi-joins.