04 Ejecutar las consultas de mercado
Siete consultas sobre 26,5 millones de ticks, cada una capaz de sustituir una página de SQL: recuentos aproximados, topK, velas OHLC, percentiles, combinadores -If, argMax y el desafío final de la clave de ordenación.
Estas son las «funciones que sustituyen una página de SQL» de ClickHouse. Pega y ejecuta cada
bloque, y lee el recuadro verde para saber qué esperar. Todas se ejecutan sobre la tabla forex
que acabas de cargar.
4.1 Aproximado frente a exacto — el gran truco
¿Cuántas marcas de tiempo de cotización distintas hay en 26,5 millones de ticks? Tres funciones responden a la misma pregunta con diferentes compromisos. Ejecútalas una a una y observa el temporizador de las estadísticas; ejecutarlas por separado es fundamental para compararlas.
-- 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;Deberías ver
Unos 24,6 millones de timestamps distintos con las tres funciones. Sin embargo, uniq
responde en unos 0,2 s; uniqExact tarda unos 3 s (casi 17 veces más) para obtener el número
preciso; y uniqCombined queda a un 0,4 % en bastante menos de un segundo. Con miles de millones
de filas, esa diferencia separa una respuesta instantánea de una pausa para el café.
4.2 Top-N con una sola función
Los pares cotizados con mayor actividad, sin 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;Deberías ver
Una sola celda con un array de 8 pares, del más cotizado al menos cotizado (el oro,
XAU/USD, ocupa el primer lugar). topK reduce toda la tabla a esa lista ordenada en una pasada;
sustituye a GROUP BY … ORDER BY count() DESC LIMIT 8.
4.3 Serie temporal en una pasada — velas OHLC
Apertura, máximo, mínimo y cierre diarios del oro: la consulta esencial de los mercados.
-- 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;Deberías ver
Una fila por día de enero de 2020. argMin(bid, datetime) es el bid del primer tick del día
(la apertura), mientras que argMax es el último (el cierre): sin funciones de ventana ni
autojoins. Observa cómo el oro sube de unos 1.520 a unos 1.610 durante el mes.
4.4 Percentiles sin malabarismos con ventanas
El spread entre bid y ask mide la liquidez, y las medias ocultan la cola; utiliza 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;Deberías ver
Una fila por par, empezando por el spread más estrecho. EUR/USD resulta ser el más líquido
(unos 0,00002, menos de un pip), y el oro presenta el más amplio. quantile se calcula de forma
aproximada en una sola pasada, sin ordenar toda la columna.
4.5 Combinadores — una exploración, dos respuestas
Compara, uno junto a otro y con una única exploración, el spread medio durante las horas activas y las de menor actividad:
-- 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;Deberías ver
Los spreads de las horas activas y tranquilas en un mismo resultado. El combinador -If añade
una condición a cualquier agregado: avgIf(x, cond) calcula la media de x solo cuando cond
es verdadero, sin consultas en dos pasadas ni CASE WHEN. Casi todos los agregados lo admiten
(countIf, sumIf, quantileIf, …).
4.6 El valor más reciente — argMax
La cotización más reciente de cada par, en una sola pasada:
-- 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;Deberías ver
El bid más reciente de cada par. argMax(bid, datetime) devuelve el bid de la fila que contiene
el mayor datetime: la consulta habitual de «último precio / estado más reciente», sin función
de ventana ni autojoin.
4.7 El desafío final — con y sin clave de ordenación
Las dos consultas tienen la misma forma —contar los ticks que cumplen una condición—, pero un filtro usa la clave de ordenación y el otro no. Primero, ejecuta ambos recuentos:
-- 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;¿Cuesta ver la diferencia?
Ambos recuentos vuelven en pocos milisegundos, por lo que el tiempo apenas cambia: count() es
rápido en los dos casos. La diferencia real es cuántos datos debe tocar cada consulta. Para
verla, pide a ClickHouse el plan con EXPLAIN indexes = 1, que indica cuántos gránulos
(bloques de unas 8.192 filas) leerá.
-- 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;Deberías ver
En el plan optimizado, la sección Indexes → PrimaryKey muestra solo una parte de los
gránulos seleccionada: unos 492 / 3.234 (aproximadamente 16.000 filas). En el plan no
optimizado, no hay un índice aplicable y se leen 3.234 / 3.234 gránulos: todas las filas.
Misma forma de consulta, una cantidad de trabajo radicalmente distinta. La lección: coloca las
columnas que más filtras al principio de tu ORDER BY.
Guarda esta consulta a mano
Entregarás la consulta no optimizada WHERE bid > 1.5 a la IA en el
siguiente módulo.
03 Cargar los datos — dos métodos
Los mismos 26,5 millones de ticks cargados dos veces: primero con ClickPipes, la canalización gestionada que usarías en producción, y después con una única llamada a s3(); aprende cuándo elegir cada método.
05 Pedir a la IA que corrija una consulta
Entrega a ClickHouse Assistant la consulta del módulo 04 que explora toda la tabla; observa cómo diagnostica la clave de ordenación ausente y propone un skip index y una projection con SQL listo para ejecutar.