Fundamentos de SQL para tablas de blockchain

SQL es el lenguaje común de la analítica de blockchain: los conjuntos de datos públicos, las plataformas alojadas y los almacenes de datos internos usan este lenguaje. Un puñado de cláusulas basta para responder la mayoría de las preguntas de un analista, pero las tablas de blockchain añaden sus propios obstáculos, desde enteros de 18 decimales hasta transacciones fallidas que aun así aparecen en los datos. Esta lección presenta el esquema genérico que se usa en todo el módulo y desarrolla las primeras consultas prácticas con él.

Un esquema genérico para tablas de blockchain

Cada conjunto de datos nombra sus tablas y columnas de una manera un poco diferente, así que este módulo usa un esquema genérico neutral, basado en las herramientas ETL de código abierto que sustentan los principales conjuntos de datos públicos. Adaptarlo a un conjunto de datos real suele consistir en cambiar los nombres.

Convenciones: las direcciones y los hashes son cadenas hexadecimales en minúsculas con el prefijo 0x; los montos (value, amount_raw, precios del gas) son enteros sin procesar en unidades base, almacenados como DECIMAL(38,0); las marcas de tiempo están en UTC; status vale 1 si la transacción tuvo éxito y 0 si falló. En transactions, gas_used y effective_gas_price provienen del recibo. Las tablas inputs y outputs, al estilo de Bitcoin, se presentan en la lección Consulta de datos UTXO.

Los conjuntos de datos reales difieren en algunos detalles que debes verificar. Por ejemplo, el conjunto de datos de Ethereum de BigQuery agrega el prefijo receipt_ a los campos del recibo (receipt_status, receipt_gas_used), llama address al contrato del registro y almacena los temas como una matriz. Algunos conjuntos de datos almacenan los enteros grandes como cadenas.

SELECT, WHERE y ORDER BY

En la mayoría de los casos, la primera pregunta es sencilla: ¿qué llegó a esta dirección?

-- Transferencias de ETH exitosas enviadas a una dirección dentro de un rango de bloques
SELECT block_number,
       block_timestamp,
       hash,
       from_address,
       value / 1e18 AS value_eth                -- de wei a ETH, para mostrar
FROM transactions
WHERE to_address = '0x5e1f…c0de'               -- en minúsculas, como está almacenada
  AND block_number BETWEEN 21000000 AND 21050000
  AND status = 1                                -- excluir transacciones fallidas
  AND value > 0
ORDER BY block_number, transaction_index;

SELECT nombra las columnas devueltas, FROM indica la tabla, WHERE define los filtros y ORDER BY establece el orden. Cada filtro importa:

• Mayúsculas y minúsculas. Las direcciones se comparan como cadenas. Las direcciones con suma de verificación combinan mayúsculas y minúsculas, así que normaliza el valor literal con LOWER() o escríbelo en minúsculas.
• Alcance. Un rango de bloques limita el análisis y hace que la consulta sea reproducible: el mismo rango devuelve las mismas filas el próximo año.
• Estado. Las transacciones fallidas se registran en la cadena y pagan comisiones, pero la transferencia de valor se revierte. Olvidar status = 1 es la forma más común de sobrestimar las entradas.
• Valor. Muchas transacciones dirigidas a una dirección no transfieren ETH porque son llamadas a contratos.

Ten en cuenta los valores NULL. Una transacción que despliega un contrato no tiene destinatario, por lo que su to_address es NULL. Las comparaciones con NULL no son ni verdaderas ni falsas; esto significa que un filtro como to_address <> '0x5e1f…c0de' excluye silenciosamente toda creación de contratos. Escribe to_address IS NULL OR to_address <> … cuando quieras decir «todo lo demás». Del mismo modo, COUNT(to_address) omite los valores NULL, mientras que COUNT(*) no lo hace, así que los resultados pueden diferir para las mismas filas.

Ordena por block_number, transaction_index para obtener el orden de la cadena. Ordenar por marca de tiempo no basta: todas las transacciones de un bloque comparten la misma marca de tiempo. Ten en cuenta también que esta consulta solo contempla las transferencias de ETH de nivel superior; el ETH enviado por contratos aparece en las trazas, que se tratan en la lección Consulta de registros y trazas de EVM.

Unidades y tipos numéricos

Los montos en la cadena son enteros expresados en la unidad más pequeña:

Estos enteros son grandes. Un entero con signo de 64 bits llega solo hasta aproximadamente 9,22 × 10^18, lo que equivale a apenas 9,22 ETH expresados en wei. Almacena y suma los montos como DECIMAL(38,0) o NUMERIC (o con el tipo de número grande del conjunto de datos); primero suma los enteros sin procesar y divide una sola vez al final. Dividir entre 1e18 produce un número de punto flotante: es adecuado para mostrar, pero no para conciliaciones, donde 0,1 + 0,2 debe ser exactamente igual a 0,3.

Presta atención a la división entera. En PostgreSQL, Trino y SQLite, dividir un entero entre otro trunca el resultado (5 / 2 = 2); en BigQuery y DuckDB, / devuelve un resultado fraccionario. Multiplica por 1.0 o convierte el tipo al calcular proporciones.

Agrupación temporal

Los analistas preguntan constantemente «por día» o «por hora». Trunca la marca de tiempo al intervalo deseado y agrupa por ese valor.

-- Entradas diarias a la dirección, agrupadas por día UTC
SELECT DATE_TRUNC('day', block_timestamp)  AS day_utc,
       COUNT(*)                            AS tx_count,
       COUNT(DISTINCT from_address)        AS senders,
       SUM(value) / 1e18                   AS eth_received
FROM transactions
WHERE to_address = '0x5e1f…c0de'
  AND status = 1
  AND block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'
  AND block_timestamp <  TIMESTAMP '2024-12-01 00:00:00'
GROUP BY DATE_TRUNC('day', block_timestamp)
ORDER BY day_utc;

En una muestra pequeña, esto devuelve:

La muestra también contiene una transferencia fallida de 2 ETH el 1 de noviembre; sin status = 1, ese día mostraría 4,25 ETH. El filtro temporal es un intervalo semiabierto (>= inicio, < fin), que nunca cuenta dos veces una fila que cae exactamente a medianoche.

La función de truncamiento presenta una de las diferencias más comunes entre dialectos:

Mantén todo en UTC y convierte los valores solo para presentarlos, indicando la zona horaria en el informe. Recuerda que las marcas de tiempo de los bloques las establece quien produce el bloque, tienen una resolución de un segundo y, en Bitcoin, pueden diferir de la hora real en más de una hora (consulta la lección Bloques y encabezados como datos).

Agregación con GROUP BY

GROUP BY combina filas en una fila por grupo, y las funciones de agregación resumen cada grupo: COUNT(*) cuenta filas, COUNT(DISTINCT x) cuenta valores únicos, y SUM, MIN, MAX y AVG hacen lo que indican sus nombres. HAVING filtra grupos después de la agregación, del mismo modo que WHERE filtra filas antes de ella. Los montos de tokens requieren un paso más: unir los datos con los decimales del token.

-- Transferencias de tokens desde la dirección, convertidas según los decimales de cada token
SELECT tt.token_address,
       t.symbol,
       COUNT(*)                                     AS transfers,
       SUM(tt.amount_raw) / POWER(10, t.decimals)   AS amount
FROM token_transfers AS tt
JOIN tokens AS t
  ON t.token_address = tt.token_address
WHERE tt.from_address = '0x5e1f…c0de'
  AND tt.block_number BETWEEN 21000000 AND 21050000
GROUP BY tt.token_address, t.symbol, t.decimals   -- nunca agrupar solo por símbolo
ORDER BY transfers DESC;

La consulta suma los montos sin procesar por contrato de token y luego divide entre 10 elevado a la potencia de los decimales de ese token. La clave de agrupación es la dirección del token, no el símbolo. Cualquiera puede desplegar un contrato llamado «USDC»; en los datos de muestra, un token imitador con 18 decimales aparece junto al token real, que tiene 6. Si se agrupara por símbolo, se sumarían 1.000 tokens sin valor a 2.000 tokens auténticos y se dividiría la mezcla entre una única cantidad de decimales incorrecta. Aquí, JOIN es una unión interna, por lo que las transferencias de tokens ausentes de tokens desaparecen silenciosamente; la siguiente lección muestra cómo conservarlas con un LEFT JOIN.

Algunos motores ofrecen funciones aproximadas, como APPROX_COUNT_DISTINCT, que resultan mucho más económicas con miles de millones de filas. Son adecuadas para paneles, pero no deberían usarse para presentar cifras en un informe a menos que se indique que son aproximadas.

Lista de errores comunes

Enfoque del examen: el nivel de detalle y las columnas clave de blocks, transactions, logs, traces y token_transfers; por qué deben excluirse las transacciones fallidas al medir el valor transferido; las unidades base (wei 10^18, gwei 10^9, satoshi 10^8 y los decimales de los tokens) y por qué los enteros de 64 bits se desbordan; sumar los enteros sin procesar antes de convertirlos; agrupar el tiempo mediante intervalos semiabiertos en UTC y las principales diferencias entre dialectos para truncar fechas; el orden de la cadena según el número de bloque y el índice de transacción; agrupar los tokens por dirección del contrato, nunca por símbolo.