04 Exécuter les requêtes de marché
Sept requêtes sur 26,5 millions de ticks, chacune remplaçant une page de SQL : comptages approximatifs, topK, chandeliers OHLC, percentiles, combinateurs -If, argMax et défi final de la clé de tri.
Voici les « fonctions qui remplacent une page de SQL » de ClickHouse. Collez et exécutez chaque
bloc, puis lisez l'encadré vert pour connaître le résultat attendu. Toutes les requêtes utilisent
la table forex que vous venez de charger.
4.1 Approximation ou exactitude — l'astuce spectaculaire
Combien de timestamps de cotation distincts trouve-t-on dans 26,5 millions de ticks ? Trois fonctions répondent à la même question avec des compromis différents. Exécutez-les une par une et observez le chronomètre dans les statistiques : les séparer est essentiel à la comparaison.
-- Approximate count of distinct timestamps (HyperLogLog)
SELECT uniq(datetime) AS distinct_ts FROM forex;-- Exact count — precise, but scans every value
SELECT uniqExact(datetime) AS distinct_ts FROM forex;-- Adaptive + tunable accuracy vs memory
SELECT uniqCombined(datetime) AS distinct_ts FROM forex;Résultat attendu
Environ 24,6 millions de timestamps distincts avec les trois fonctions. Mais uniq répond en
environ 0,2 s, uniqExact met près de 3 s (environ 17 fois plus lent) pour obtenir le nombre
exact, et uniqCombined s'en approche à 0,4 % en nettement moins d'une seconde. Sur des milliards
de lignes, cet écart sépare une réponse instantanée d'une pause-café.
4.2 Top-N avec une seule fonction
Les paires les plus activement cotées — sans GROUP BY / ORDER BY / LIMIT :
-- One row: an array of the 8 most actively quoted pairs (by tick count)
SELECT topK(8)(pair) AS most_active FROM forex;Résultat attendu
Une seule cellule contenant un array de 8 paires, de la plus cotée à la moins cotée (l'or,
XAU/USD, arrive en tête). topK condense toute la table en cette liste classée en une passe ;
il remplace GROUP BY … ORDER BY count() DESC LIMIT 8.
4.3 Série temporelle en une passe — chandeliers OHLC
Ouverture, plus haut, plus bas et clôture quotidiens de l'or : la requête fondamentale du marché.
-- One row per day: gold (XAU/USD) open, high, low, close — a candlestick chart
SELECT
toDate(datetime) AS day,
argMin(bid, datetime) AS open, -- bid at the day's first tick
max(bid) AS high,
min(bid) AS low,
argMax(bid, datetime) AS close -- bid at the day's last tick
FROM forex
WHERE base = 'XAU' AND quote = 'USD'
GROUP BY day
ORDER BY day;Résultat attendu
Une ligne par jour de janvier 2020. argMin(bid, datetime) fournit le bid du premier tick de la
journée (l'ouverture), tandis que argMax fournit le dernier (la clôture) — sans fonction de
fenêtre ni autojointure. Observez l'or progresser d'environ 1 520 à 1 610 pendant le mois.
4.4 Des percentiles sans acrobaties de fenêtres
Le spread entre bid et ask mesure la liquidité, et les moyennes masquent la traîne ; utilisez des percentiles :
-- One row per pair: median and 99th-percentile bid/ask spread (tighter = more liquid)
SELECT
pair,
round(quantile(0.5)(ask - bid), 6) AS median_spread,
round(quantile(0.99)(ask - bid), 6) AS p99_spread
FROM forex
GROUP BY pair
ORDER BY median_spread ASC;Résultat attendu
Une ligne par paire, du spread le plus serré au plus large. EUR/USD ressort comme la plus
liquide (environ 0,00002, moins d'un pip), tandis que l'or présente le spread le plus large.
quantile est calculé approximativement en une passe, sans trier toute la colonne.
4.5 Combinateurs — une lecture, deux réponses
Comparez côte à côte le spread moyen pendant les heures actives et les heures calmes, en une seule lecture :
-- One row per pair: avg spread during the active window vs quiet hours
SELECT
pair,
count() AS ticks,
round(avgIf(ask - bid, toHour(datetime) BETWEEN 7 AND 20), 6) AS spread_active,
round(avgIf(ask - bid, toHour(datetime) NOT BETWEEN 7 AND 20), 6) AS spread_quiet
FROM forex
GROUP BY pair
ORDER BY ticks DESC;Résultat attendu
Les spreads des heures actives et calmes apparaissent dans le même résultat. Le combinateur
-If ajoute une condition à n'importe quel agrégat : avgIf(x, cond) calcule la moyenne de x
uniquement lorsque cond est vrai — sans requête en deux passes ni CASE WHEN. Presque tous les
agrégats l'acceptent (countIf, sumIf, quantileIf, …).
4.6 La valeur la plus récente — argMax
La cotation la plus récente de chaque paire, en une passe :
-- One row per pair: the latest quoted bid and the timestamp it was seen
SELECT
pair,
argMax(bid, datetime) AS last_bid,
max(datetime) AS as_of
FROM forex
GROUP BY pair
ORDER BY pair;Résultat attendu
Le bid le plus récent de chaque paire. argMax(bid, datetime) renvoie le bid de la ligne dont
le datetime est le plus grand : la requête courante « dernier cours / dernier état », sans
fonction de fenêtre ni autojointure.
4.7 Le défi final — avec et sans clé de tri
Les deux requêtes ont la même forme — compter les ticks qui satisfont une condition — mais un filtre utilise la clé de tri et l'autre non. Exécutez d'abord les deux comptages :
-- Optimized: base+quote ARE the leading sort key — the index skips to that pair
SELECT count() FROM forex WHERE base = 'XAU' AND quote = 'USD';
-- Non-optimized: bid is NOT in the sort key — no index, scans all 26.5M ticks.
-- (cache off so the full scan shows on every run)
SELECT count() FROM forex WHERE bid > 1.5
SETTINGS use_query_condition_cache = 0;La différence est difficile à voir ?
Les deux comptages reviennent en quelques millisecondes ; le temps seul varie à peine, car
count() est rapide dans les deux cas. La vraie différence tient à la quantité de données que
chaque requête doit toucher. Pour la voir, demandez à ClickHouse d'afficher son plan avec
EXPLAIN indexes = 1, qui indique le nombre de granules (blocs d'environ 8 192 lignes) lus.
-- Optimized: the primary index skips straight to the XAU/USD rows
EXPLAIN indexes = 1
SELECT count() FROM forex WHERE base = 'XAU' AND quote = 'USD';
-- Non-optimized: bid isn't in the sort key, so nothing can be skipped
EXPLAIN indexes = 1
SELECT count() FROM forex WHERE bid > 1.5;Résultat attendu
Dans le plan optimisé, la section Indexes → PrimaryKey ne sélectionne qu'une partie des
granules — environ 492 / 3 234 (près de 16 000 lignes). Dans le plan non optimisé, aucun
index ne s'applique et il lit 3 234 / 3 234 granules, soit toutes les lignes. Même forme de
requête, quantité de travail radicalement différente. La leçon : placez les colonnes les plus
souvent filtrées au début de votre ORDER BY.
Gardez cette requête sous la main
Vous confierez la requête non optimisée WHERE bid > 1.5 à l'IA dans le
module suivant.
03 Charger les données — deux méthodes
Les mêmes 26,5 millions de ticks chargés deux fois : d'abord avec ClickPipes, le pipeline géré que vous utiliseriez en production, puis avec un simple appel à s3() — et quand choisir chaque méthode.
05 Demander à l'IA de corriger une requête
Confiez à ClickHouse Assistant la requête du module 04 qui parcourt toute la table ; observez-le diagnostiquer l'absence de clé de tri, puis proposer un skip index et une projection avec du SQL prêt à exécuter.