SQL para almacenes de datos financieros

Cada operación, cada tick, cada actualización del libro de órdenes — los mercados financieros producen volúmenes asombrosos de datos estructurados. Y desde hace más de cuatro décadas, SQL (Structured Query Language, lenguaje de consulta estructurado) ha sido la herramienta dominante para consultar, transformar y analizar esos datos. No es glamuroso. No está de moda. Pero cuando un gestor de cartera pregunta «¿Cuál fue mi precio medio de ejecución en operaciones de ETH el martes pasado entre las 14:00 y las 15:00 UTC?», la respuesta viene de SQL.

Los datos financieros son inherentemente relacionales. Una operación hace referencia a una orden, que hace referencia a una cuenta, que pertenece a un usuario. Las posiciones se relacionan con instrumentos; los instrumentos se relacionan con mercados. El modelo relacional — tablas con filas y columnas, conectadas por claves foráneas — se ajusta de forma natural a estas relaciones. Por eso las bases de datos relacionales como PostgreSQL, MySQL y SQL Server siguen siendo la columna vertebral de la infraestructura financiera, desde Goldman Sachs hasta GaiaEx.

El poder de SQL reside en su naturaleza declarativa: describes qué quieres, no cómo conseguirlo. El motor de la base de datos determina el plan de ejecución óptimo. Esto importa enormemente cuando tu tabla de historial de operaciones tiene 500 millones de filas y necesitas resultados en menos de un segundo. Escribe la consulta correcta, construye el índice correcto, y SQL cumple — sin importar si lo ejecutas contra una instancia local de PostgreSQL o un almacén de datos a escala de nube.

Empecemos con un esquema concreto. Imagina una tabla trades en una plataforma como GaiaEx:

CREATE TABLE trades (
    trade_id    BIGINT PRIMARY KEY,
    user_id     INT NOT NULL,
    symbol      VARCHAR(20) NOT NULL,
    side        VARCHAR(4) NOT NULL,  -- 'buy' o 'sell'
    price       NUMERIC(18,8) NOT NULL,
    quantity    NUMERIC(18,8) NOT NULL,
    fee         NUMERIC(18,8) DEFAULT 0,
    executed_at TIMESTAMPTZ NOT NULL
);

La operación más fundamental es la instrucción SELECT con filtrado. Para encontrar todas las operaciones de compra de ETH-USD por encima de $3.000 en las últimas 24 horas:

SELECT trade_id, price, quantity, executed_at
FROM trades
WHERE symbol = 'ETH-USD'
  AND side = 'buy'
  AND price > 3000
  AND executed_at > NOW() - INTERVAL '24 hours'
ORDER BY executed_at DESC;

Los JOIN conectan tablas relacionadas. Supongamos que tienes una tabla accounts y quieres ver el volumen de trading por nivel de cuenta:

SELECT a.tier, COUNT(*) AS trade_count,
       SUM(t.price * t.quantity) AS total_volume
FROM trades t
JOIN accounts a ON t.user_id = a.user_id
WHERE t.executed_at > NOW() - INTERVAL '30 days'
GROUP BY a.tier
ORDER BY total_volume DESC;

Estas consultas son el pan de cada día de los analistas financieros, los equipos de riesgo y los responsables de cumplimiento normativo. Dómalas y podrás responder a cualquier pregunta que contengan tus datos.

Las funciones de ventana (window functions) son lo que separa a los principiantes de SQL de los profesionales de SQL en finanzas. Te permiten realizar cálculos a través de un conjunto de filas relacionadas con la fila actual — sin colapsar el resultado en un único valor agregado. Piensa en ellas como «cálculos en marcha» sobre tus datos.

Las funciones de ventana más usadas en el análisis financiero:

• ROW_NUMBER() — Asigna un entero secuencial a cada fila dentro de una partición. Útil para deduplicación o para seleccionar la enésima operación por símbolo.
• LAG() y LEAD() — Acceden al valor de la fila anterior o posterior. Esenciales para calcular retornos operación a operación o detectar huecos en datos de series temporales.
• SUM() OVER () — Totales acumulados. Calcula volumen acumulado, PnL (ganancias y pérdidas) acumulado, o el tamaño de posición en marcha.
• AVG() OVER (ROWS BETWEEN) — Medias móviles directamente en SQL. Sin necesidad de Python.

Aquí tienes un ejemplo práctico — calcular el cambio de precio operación a operación y una posición en marcha para BTC:

SELECT executed_at, price, quantity, side,
       price - LAG(price) OVER (ORDER BY executed_at) AS price_change,
       SUM(CASE WHEN side = 'buy' THEN quantity ELSE -quantity END)
           OVER (ORDER BY executed_at) AS running_position
FROM trades
WHERE symbol = 'BTC-USD'
ORDER BY executed_at;

Para la agregación OHLCV — la base de los gráficos de velas — puedes combinar GROUP BY con agrupación temporal (time-bucketing) y agregados estándar:

SELECT date_trunc('hour', executed_at) AS bucket,
       (ARRAY_AGG(price ORDER BY executed_at))[1] AS open,
       MAX(price) AS high,
       MIN(price) AS low,
       (ARRAY_AGG(price ORDER BY executed_at DESC))[1] AS close,
       SUM(quantity) AS volume
FROM trades
WHERE symbol = 'ETH-USD'
GROUP BY bucket
ORDER BY bucket;

Las expresiones de tabla común (CTE, Common Table Expressions) te permiten dividir una consulta compleja en etapas nombradas y legibles — como funciones en programación. Se introducen con la palabra clave WITH y pueden hacer referencia entre sí de forma secuencial.

Supongamos que quieres identificar tus 10 días de trading más rentables, pero la rentabilidad requiere calcular el PnL neto por día incluyendo comisiones. Con CTE, puedes construirlo paso a paso:

WITH daily_trades AS (
    SELECT DATE(executed_at) AS trade_date,
           SUM(CASE WHEN side = 'sell' THEN price * quantity
                    ELSE -price * quantity END) AS gross_pnl,
           SUM(fee) AS total_fees
    FROM trades
    WHERE user_id = 42
    GROUP BY DATE(executed_at)
),
daily_pnl AS (
    SELECT trade_date,
           gross_pnl - total_fees AS net_pnl,
           SUM(gross_pnl - total_fees) OVER (ORDER BY trade_date) AS cumulative_pnl
    FROM daily_trades
)
SELECT trade_date, net_pnl, cumulative_pnl
FROM daily_pnl
ORDER BY net_pnl DESC
LIMIT 10;

Cada CTE se lee como un párrafo. daily_trades agrega las operaciones en bruto en resúmenes diarios. daily_pnl calcula el PnL neto y un total acumulado. El SELECT final elige los 10 mejores días. Compara esto con una única subconsulta monolítica — la versión con CTE es mantenible, comprobable y se autodocumenta.

En sistemas financieros de producción, las CTE se usan para todo, desde informes regulatorios (agregar requisitos de margen entre cuentas) hasta consultas de paneles en tiempo real (calcular el volumen móvil de 24 horas en GaiaEx). Se componen del mismo modo en que se compone un código bien factorizado — cada capa se construye sobre la anterior.

Una consulta es tan rápida como el índice que la sostiene. Sin el índice adecuado, incluso una simple cláusula WHERE fuerza un escaneo secuencial — leyendo cada fila de la tabla. Con 500 millones de filas, eso son minutos en lugar de milisegundos.

Para datos de series temporales financieras, el patrón de índice más crítico es un índice B-tree compuesto sobre (symbol, executed_at):

CREATE INDEX idx_trades_symbol_time
ON trades (symbol, executed_at DESC);

Este único índice acelera la mayoría de las consultas analíticas: «todas las operaciones de ETH en la última hora», «operaciones de BTC entre dos marcas temporales», o «la última operación por símbolo». El orden de las columnas importa — symbol primero permite el filtrado por igualdad, y luego executed_at permite escaneos por rango eficientes dentro de esa partición del índice.

Para tablas que crecen más allá de cientos de millones de filas, el particionado de tablas es esencial. PostgreSQL admite particionado declarativo por rango — perfecto para series temporales:

CREATE TABLE trades (
    trade_id BIGINT, symbol VARCHAR(20),
    price NUMERIC(18,8), executed_at TIMESTAMPTZ
) PARTITION BY RANGE (executed_at);

CREATE TABLE trades_2026_q1 PARTITION OF trades
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE trades_2026_q2 PARTITION OF trades
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

Con particionado, una consulta para los datos de marzo de 2026 solo escanea la partición de Q1 — el motor se salta por completo Q2, Q3 y Q4. Esta técnica, llamada poda de particiones (partition pruning), ofrece mejoras de rendimiento de un orden de magnitud en grandes conjuntos de datos históricos.

No todas las bases de datos gestionan igual la analítica financiera. La elección depende de tus patrones de consulta, el volumen de datos y los requisitos de latencia.

PostgreSQL es el caballo de batalla. Destaca en cargas de trabajo transaccionales (registrar operaciones en tiempo real), admite garantías ACID, gestiona con soltura los JOIN complejos, y escala a cientos de millones de filas con la indexación y el particionado adecuados. Es la opción correcta para bases de datos operativas — el sistema de registro. GaiaEx, por ejemplo, se apoya en infraestructura compatible con PostgreSQL para sus datos de trading centrales, con las garantías de fiabilidad que exigen los sistemas financieros.

ClickHouse es una base de datos columnar diseñada para consultas analíticas sobre miles de millones de filas. Donde PostgreSQL almacena los datos fila por fila (excelente para insertar operaciones individuales), ClickHouse los almacena columna por columna (excelente para agregar una única columna en millones de filas). Una consulta como «volumen medio por hora de BTC en 3 años», que tarda 30 segundos en PostgreSQL, podría terminar en 200 milisegundos en ClickHouse. La contrapartida: ClickHouse no admite UPDATE ni DELETE de forma eficiente — es de solo anexado (append-only) por diseño.

BigQuery (Google Cloud) es un almacén columnar sin servidor. Sin infraestructura que gestionar, precios por consulta, y capacidad a escala de petabytes. Es ideal para analítica puntual, investigación y exploración — pero la latencia de las consultas se mide en segundos, no en milisegundos, lo que lo hace poco adecuado para aplicaciones en tiempo real.

• Registro de operaciones en tiempo real y gestión de órdenes → PostgreSQL
• Analítica histórica y backtesting sobre miles de millones de filas → ClickHouse
• Exploración puntual y compartición de datos entre equipos → BigQuery

Muchas firmas de trading profesionales usan las tres en una arquitectura escalonada: PostgreSQL como el almacén operativo caliente, ClickHouse como la capa analítica templada, y BigQuery como el archivo frío. La habilidad de escribir buen SQL se transfiere entre las tres — la sintaxis es un 90% idéntica, y el pensamiento analítico es un 100% el mismo.