04 市場分析クエリを実行する
2,650万ティックに対する7本のクエリ。それぞれが1ページ分の SQL を置き換えます: 近似カウント、topK、OHLC ローソク足、パーセンタイル、-If コンビネータ、argMax、そして sort key のフィナーレ。
これらは ClickHouse の「1ページ分の SQL を置き換える関数」です。各ブロックを貼り付けて実行し、
緑のボックスで期待される結果を確認してください。すべて、いまロードした forex テーブルに対して
実行します。
4.1 近似 vs 厳密 — 一番の見どころ
2,650万ティックの中に 異なる 気配タイムスタンプはいくつあるでしょうか。3つの関数が同じ問いに、 異なるトレードオフで答えます。1本ずつ実行して、クエリ統計のタイマーを見てください。別々に 実行することがこの節の要点です。
-- 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;表示されるはずの結果
3つとも約 2,460万 個の異なるタイムスタンプを返します。ただし uniq は約0.2秒で返り、
uniqExact は厳密な値のために約3秒（約17倍遅い）かかり、uniqCombined は1秒を大きく下回る
時間で誤差約0.4%以内に収まります。数十億行規模になると、この差は「即時」と「コーヒー休憩」の差に
なります。
4.2 Top-N を1つの関数で
最も活発に気配が出ているペア。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;表示されるはずの結果
8ペアの配列 が入った1セル。気配数の多い順で（金、XAU/USD が首位）並びます。
topK はテーブル全体を1パスでそのランキングに畳み込みます。
GROUP BY … ORDER BY count() DESC LIMIT 8 の代わりになります。
4.3 時系列を1パスで — OHLC ローソク足
金の日次の始値 / 高値 / 安値 / 終値。市場分析の定番クエリです:
-- 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;表示されるはずの結果
2020年1月の1日あたり1行。argMin(bid, datetime) はその日の 最初 のティックの bid（始値）、
argMax は 最後 のティック（終値）です。ウィンドウ関数も自己結合もありません。金がその月の
あいだに約1,520から約1,610まで上がる様子が見えます。
4.4 ウィンドウ関数の曲芸なしでパーセンタイル
bid/ask スプレッドは流動性の指標です。平均値はテールを隠してしまうので、パーセンタイルを使います:
-- 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;表示されるはずの結果
ペアごとに1行、スプレッドが狭い順。EUR/USD が最も流動性が高い結果（約0.00002、サブピップ）で、
金が最も広くなります。quantile は1パスで近似計算されるため、カラム全体をソートしません。
4.5 コンビネータ — 1回のスキャンで2つの答え
活発な取引時間帯と静かな時間帯の平均スプレッドを、1回のスキャンで並べて出します:
-- 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;表示されるはずの結果
活発な時間帯と静かな時間帯のスプレッドが同じ結果に並びます。-If コンビネータは任意の集計関数に
条件を付け足します。avgIf(x, cond) は cond が真の箇所だけで x を平均します。2パスのクエリも
CASE WHEN も不要です。ほぼすべての集計関数が対応しています（countIf、sumIf、
quantileIf、…）。
4.6 最新値 — argMax
各ペアの最新の気配を1パスで:
-- 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;表示されるはずの結果
ペアごとの最新 bid。argMax(bid, datetime) は datetime が最大の行の bid を返します。
「最終価格 / 最新ステータス」という日常的なクエリを、ウィンドウ関数も自己結合もなしに書けます。
4.7 フィナーレ — sort key あり vs なし
クエリの形は同じ（条件に一致するティック数を数える）ですが、一方のフィルタは sort key に当たり、 もう一方は当たりません。まず両方のカウントを実行します:
-- 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;違いが分かりにくいですか？
どちらのカウントも数ミリ秒で返るため、時間だけを見てもほとんど差が出ません。count() はどちらでも
高速です。本当の違いは それぞれがどれだけのデータに触る必要があるか です。それを実際に見るには、
EXPLAIN indexes = 1 で ClickHouse に実行計画を出させます。読み取る
granule（約8,192行のブロック）の数が報告されます。
-- 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;表示されるはずの結果
最適化された 計画では、Indexes → PrimaryKey セクションに選択された granule がごく一部だけ、
約 492 / 3,234（約16K行）と表示されます。最適化されていない 計画では適用できるインデックスが
ないため、3,234 / 3,234 granule、つまり全行を読みます。クエリの形は同じでも作業量はまったく
違います。教訓は、最も頻繁に絞り込むカラムを ORDER BY の先頭に置く ことです。
これはクリップボードに残しておいてください
この最適化されていない WHERE bid > 1.5 のクエリを、
次のモジュール で AI に渡します。