SQL pour les entrepôts de données financières

Chaque trade, chaque tick, chaque mise à jour de carnet d'ordres — les marchés financiers produisent des volumes de données structurées vertigineux. Et depuis plus de quatre décennies, SQL (Structured Query Language) est l'outil dominant pour requêter, transformer et analyser ces données. Ce n'est pas glamour. Ce n'est pas à la mode. Mais quand un gérant de portefeuille demande « Quel était mon prix d'exécution moyen sur les trades ETH mardi dernier entre 14 h et 15 h UTC ? », la réponse vient de SQL.

Les données financières sont intrinsèquement relationnelles. Un trade référence un ordre, qui référence un compte, qui appartient à un utilisateur. Les positions se rapportent à des instruments ; les instruments se rapportent à des marchés. Le modèle relationnel — des tables avec des lignes et des colonnes, reliées par des clés étrangères — correspond naturellement à ces relations. C'est pourquoi les bases de données relationnelles comme PostgreSQL, MySQL et SQL Server restent l'épine dorsale de l'infrastructure financière, de Goldman Sachs à GaiaEx.

La puissance de SQL réside dans sa nature déclarative : vous décrivez ce que vous voulez, pas comment l'obtenir. Le moteur de base de données détermine le plan d'exécution optimal. C'est énormément important quand votre table d'historique de trades compte 500 millions de lignes et que vous avez besoin des résultats en moins d'une seconde. Écrivez la bonne requête, construisez le bon index, et SQL délivre — que vous l'exécutiez contre une instance PostgreSQL locale ou un entrepôt de données à l'échelle du cloud.

Commençons par un schéma concret. Imaginez une table trades sur une plateforme comme 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' or 'sell'
    price       NUMERIC(18,8) NOT NULL,
    quantity    NUMERIC(18,8) NOT NULL,
    fee         NUMERIC(18,8) DEFAULT 0,
    executed_at TIMESTAMPTZ NOT NULL
);

L'opération la plus fondamentale est l'instruction SELECT avec filtrage. Pour trouver tous les trades d'achat ETH-USD au-dessus de 3 000 $ dans les dernières 24 heures :

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;

Les JOIN relient des tables liées. Supposons que vous ayez une table accounts et que vous vouliez voir le volume d'échange par palier de compte :

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;

Ces requêtes sont le pain quotidien des analystes financiers, des équipes de risque et des responsables de conformité. Maîtrisez-les et vous pourrez répondre à n'importe quelle question que vos données contiennent.

Les fonctions de fenêtrage (window functions) sont ce qui distingue les débutants en SQL des praticiens en finance. Elles vous permettent d'effectuer des calculs sur un ensemble de lignes liées à la ligne courante — sans réduire le résultat à une seule valeur agrégée. Pensez-y comme à des « calculs glissants » sur vos données.

Les fonctions de fenêtrage les plus utilisées en analyse financière :

• ROW_NUMBER() — attribue un entier séquentiel à chaque ligne au sein d'une partition. Utile pour la déduplication ou pour sélectionner le Nième trade par symbole.
• LAG() et LEAD() — accèdent à la valeur de la ligne précédente ou suivante. Essentielles pour calculer les rendements trade à trade ou détecter des trous dans des données en série temporelle.
• SUM() OVER () — totaux glissants. Calculez un volume cumulé, un P&L cumulé, ou une taille de position glissante.
• AVG() OVER (ROWS BETWEEN) — des moyennes mobiles directement en SQL. Pas besoin de Python.

Voici un exemple pratique — calculer la variation de prix trade à trade et une position glissante pour 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;

Pour l'agrégation OHLCV — le fondement des graphiques en chandeliers japonais — vous pouvez combiner GROUP BY avec un découpage par intervalles de temps et des agrégats standards :

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;

Les expressions de table communes (CTE) vous permettent de découper une requête complexe en étapes nommées et lisibles — comme des fonctions en programmation. Elles s'introduisent avec le mot-clé WITH et peuvent se référencer les unes les autres de façon séquentielle.

Supposons que vous vouliez identifier vos 10 journées de trading les plus profitables, mais que la profitabilité exige de calculer le P&L net par jour en incluant les frais. Avec les CTE, vous pouvez construire cela étape par étape :

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;

Chaque CTE se lit comme un paragraphe. daily_trades agrège les trades bruts en résumés journaliers. daily_pnl calcule le P&L net et un total glissant. Le SELECT final choisit les 10 meilleures journées. Comparez cela à une sous-requête monolithique unique — la version en CTE est maintenable, testable et auto-documentée.

Dans les systèmes financiers en production, les CTE sont utilisées pour tout, du reporting réglementaire (agréger les exigences de marge à travers les comptes) aux requêtes de tableau de bord en temps réel (calculer le volume glissant sur 24 heures sur GaiaEx). Elles se composent de la même façon qu'un code bien factorisé se compose — chaque couche s'appuie sur la précédente.

Une requête n'est jamais plus rapide que l'index qui la supporte. Sans le bon index, même une simple clause WHERE force un balayage séquentiel (sequential scan) — la lecture de chaque ligne de la table. À 500 millions de lignes, cela se traduit en minutes plutôt qu'en millisecondes.

Pour les données financières en série temporelle, le pattern d'index le plus critique est un index B-tree composite sur (symbol, executed_at) :

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

Cet index unique accélère la majorité des requêtes analytiques : « tous les trades ETH de la dernière heure », « les trades BTC entre deux horodatages », ou « le dernier trade par symbole ». L'ordre des colonnes compte — symbol en premier permet un filtrage par égalité, puis executed_at permet des balayages par plage efficaces au sein de cette partition de l'index.

Pour les tables qui dépassent des centaines de millions de lignes, le partitionnement de table est essentiel. PostgreSQL prend en charge le partitionnement déclaratif par plage — parfait pour les séries temporelles :

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');

Avec le partitionnement, une requête pour les données de mars 2026 ne balaye que la partition Q1 — le moteur ignore entièrement Q2, Q3 et Q4. Cette technique, appelée élagage de partitions (partition pruning), offre des gains de vitesse d'un ordre de grandeur sur de grands jeux de données historiques.

Toutes les bases de données ne gèrent pas l'analytique financière de la même façon. Le choix dépend de vos patterns de requête, de votre volume de données et de vos exigences de latence.

PostgreSQL est le cheval de labour. Il excelle sur les charges transactionnelles (enregistrer les trades en temps réel), prend en charge les garanties ACID, gère les JOIN complexes avec aisance, et monte en charge jusqu'à des centaines de millions de lignes avec une indexation et un partitionnement adaptés. C'est le bon choix pour les bases de données opérationnelles — le système de référence (system of record). GaiaEx, par exemple, s'appuie sur une infrastructure compatible PostgreSQL pour ses données de trading centrales, avec les garanties de fiabilité qu'exigent les systèmes financiers.

ClickHouse est une base de données en colonnes conçue pour les requêtes analytiques sur des milliards de lignes. Là où PostgreSQL stocke les données ligne par ligne (idéal pour insérer des trades individuels), ClickHouse stocke les données colonne par colonne (idéal pour agréger une seule colonne à travers des millions de lignes). Une requête comme « volume horaire moyen pour BTC sur 3 ans », qui prend 30 secondes dans PostgreSQL, pourrait se terminer en 200 millisecondes dans ClickHouse. Le compromis : ClickHouse ne prend pas efficacement en charge UPDATE ni DELETE — il est conçu pour l'ajout uniquement (append-only).

BigQuery (Google Cloud) est un entrepôt en colonnes sans serveur (serverless). Aucune infrastructure à gérer, une tarification au paiement par requête, et une capacité à l'échelle du pétaoctet. Il est idéal pour l'analyse ponctuelle, la recherche et l'exploration — mais la latence des requêtes se mesure en secondes, pas en millisecondes, ce qui le rend inadapté aux applications en temps réel.

• Enregistrement de trades en temps réel et gestion des ordres → PostgreSQL
• Analytique historique et backtesting sur des milliards de lignes → ClickHouse
• Exploration ponctuelle et partage de données inter-équipes → BigQuery

De nombreuses sociétés de trading professionnelles utilisent les trois dans une architecture en niveaux : PostgreSQL comme magasin opérationnel chaud, ClickHouse comme couche analytique tiède, et BigQuery comme archive froide. La compétence d'écrire du bon SQL se transfère à travers les trois — la syntaxe est identique à 90 %, et la pensée analytique est identique à 100 %.