金融データウェアハウスのための SQL

すべての取引、すべてのティック、すべてのオーダーブックの更新——金融市場は驚異的な量の構造化データを生み出します。そして40年以上にわたり、SQL（Structured Query Language）はそのデータを検索・変換・分析するための主流のツールであり続けています。派手ではありません。流行っているわけでもありません。しかし、ポートフォリオマネージャーが「先週の火曜日、UTC の午後2時から3時の間の ETH 取引の平均フィル価格はいくつだったか？」と尋ねたとき、その答えは SQL から返ってきます。

金融データは本質的にリレーショナル（関係的）です。取引は注文を参照し、注文はアカウントを参照し、アカウントはユーザーに属します。ポジションは金融商品に関連し、金融商品はマーケットに関連します。行と列を持つテーブルが外部キーで結ばれるというリレーショナルモデルは、こうした関係性に自然に対応します。だからこそ、PostgreSQL、MySQL、SQL Server のようなリレーショナルデータベースは、Goldman Sachs から GaiaEx に至るまで、金融インフラの背骨であり続けています。

SQL の力は、その宣言的な性質にあります。あなたは何を求めているかを記述するだけで、どうやってそれを得るかは記述しません。データベースエンジンが最適な実行計画を導き出します。これは、取引履歴テーブルが5億行あり、1秒未満で結果を得る必要がある場合に、極めて重要になります。正しいクエリを書き、正しいインデックスを構築すれば、ローカルの PostgreSQL インスタンスであろうと、クラウドスケールのデータウェアハウスであろうと、SQL は結果を出してくれます。

具体的なスキーマから始めましょう。GaiaEx のようなプラットフォーム上の trades テーブルを想像してください。

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

最も基本的な操作は、フィルタリング付きの SELECT 文です。過去24時間で $3,000 を超える ETH-USD の買い取引をすべて見つけるには：

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;

JOIN は関連するテーブルをつなぎます。accounts テーブルがあり、アカウントティア別の取引量を見たい場合：

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;

これらのクエリは、金融アナリスト、リスクチーム、コンプライアンス担当者の日々の主食です。これらをマスターすれば、あなたのデータが含むどんな問いにも答えられるようになります。

ウィンドウ関数は、金融分野において SQL の初心者と実務家を分けるものです。ウィンドウ関数を使うと、結果を単一の集計値に折り畳むことなく、現在の行に関連する一連の行にわたる計算を行うことができます。データに対する「実行中の計算」だと考えてください。

金融分析で最もよく使われるウィンドウ関数：

• ROW_NUMBER()——パーティション内の各行に連番を割り当てます。重複排除や、シンボルごとのN番目の取引を選択する際に便利です。
• LAG() と LEAD()——前の行または次の行の値にアクセスします。取引間のリターンを計算したり、時系列データのギャップを検出したりする上で必須です。
• SUM() OVER ()——ランニングトータル。累積出来高、累積損益、あるいは積み上がっていくポジションサイズを計算します。
• AVG() OVER (ROWS BETWEEN)——SQL の中で直接移動平均を計算できます。Python は不要です。

実践的な例として、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;

ローソク足チャートの基礎である OHLCV 集計については、GROUP BY を時間バケット化と標準的な集計関数と組み合わせることができます。

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;

共通テーブル式（CTE）を使うと、プログラミングにおける関数のように、複雑なクエリを名前付きの読みやすいステージに分割できます。WITH キーワードで導入され、互いに順番に参照できます。

手数料を含めた実質損益を計算した上で、最も収益性の高かった上位10取引日を特定したいとしましょう。CTE を使えば、これをステップごとに構築できます。

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;

それぞれの CTE は、一つの段落のように読めます。daily_trades は生の取引を日次サマリーに集計します。daily_pnl は実質損益と累積合計を計算します。最後の SELECT が上位10日を選び出します。これを一つの巨大なサブクエリと比較してみてください——CTE 版は保守しやすく、テストしやすく、自己文書化されています。

本番の金融システムでは、CTE は規制報告（アカウント全体のマージン要件の集計）から、リアルタイムダッシュボードのクエリ（GaiaEx 上での24時間出来高のローリング計算）まで、あらゆる場面で使われています。それらはよく整理されたコードが構成されるのと同じ方法で構成されます——各レイヤーが前のレイヤーの上に積み上がっていきます。

クエリの速さは、それを支えるインデックスの速さでしか決まりません。適切なインデックスがなければ、単純な WHERE 句でさえシーケンシャルスキャンを強制されます——テーブルのすべての行を読むのです。5億行の規模になると、それはミリ秒ではなく分単位の時間になります。

金融の時系列データにとって最も重要なインデックスパターンは、(symbol, executed_at) の複合 B-tree インデックスです。

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

この一つのインデックスだけで、大部分の分析クエリが高速化されます。「過去1時間のすべての ETH 取引」「二つのタイムスタンプの間の BTC 取引」「シンボルごとの最新の取引」など。列の順序が重要です——symbol を先にすることで等価フィルタリングが可能になり、次に executed_at があることでインデックスのそのパーティション内で効率的な範囲スキャンが可能になります。

数億行を超えて成長するテーブルには、テーブルパーティショニングが不可欠です。PostgreSQL は、時系列データに最適な範囲による宣言的パーティショニングをサポートしています。

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

パーティショニングを使うと、2026年3月のデータに対するクエリは Q1 パーティションだけをスキャンし、エンジンは Q2、Q3、Q4 を完全にスキップします。この手法はパーティションプルーニングと呼ばれ、大規模な履歴データセットに対して桁違いの高速化をもたらします。

すべてのデータベースが金融分析を同じように処理するわけではありません。選択はクエリのパターン、データ量、レイテンシ要件によって決まります。

PostgreSQL は主力です。トランザクション処理のワークロード（リアルタイムでの取引記録）に優れ、ACID の保証をサポートし、複雑な JOIN を優雅に処理し、適切なインデックス化とパーティショニングによって数億行までスケールします。運用データベース——記録の基幹システム——にとって正しい選択です。例えば GaiaEx は、金融システムが求める信頼性の保証を持つ、PostgreSQL 互換のインフラにコアなトレーディングデータを依存させています。

ClickHouse は、数十億行に対する分析クエリのために設計されたカラムナ（列指向）データベースです。PostgreSQL が行ごとにデータを保存する（個々の取引を挿入するのに適している）のに対し、ClickHouse は列ごとにデータを保存します（数百万行にわたる単一の列を集計するのに適している）。PostgreSQL で30秒かかる「3年間の BTC の時間あたり平均出来高」のようなクエリが、ClickHouse では200ミリ秒で終わることもあります。トレードオフは、ClickHouse が UPDATE や DELETE を効率的にサポートしないことです——設計上、追記専用です。

BigQuery（Google Cloud）は、サーバーレスのカラムナウェアハウスです。管理すべきインフラはなく、クエリ単位の課金で、ペタバイト規模の容量を持ちます。アドホックな分析、リサーチ、探索には理想的ですが、クエリのレイテンシは秒単位で測られ、ミリ秒単位ではないため、リアルタイムアプリケーションには適していません。

• リアルタイムの取引記録と注文管理→ PostgreSQL
• 数十億行にわたる履歴分析とバックテスト→ ClickHouse
• アドホックな探索とチーム間でのデータ共有→ BigQuery

多くの専門トレーディング会社は、これら3つすべてを階層型アーキテクチャで使っています。PostgreSQL をホットな運用データストアとして、ClickHouse をウォームな分析レイヤーとして、そして BigQuery をコールドなアーカイブとして使うのです。良い SQL を書くスキルは、これらすべてに転用できます——構文は90%同一で、分析的な思考は100%同じです。