GaiaEx AcademyGaiaEx Academy
面向金融資料倉儲的 SQL
開發者程式設計9 min read

面向金融資料倉儲的 SQL

查詢、聚合並分析結構化金融資料

分享文章

為什麼說 SQL 是金融資料的語言

每一筆成交、每一個 tick、每一次訂單簿更新——金融市場源源不斷地產生著海量結構化資料。而四十多年來，SQL（結構化查詢語言）一直是查詢、轉換和分析這些資料的主流工具。它不光鮮，也不時髦。但當一位投資組合經理問出「上週二 UTC 時間下午 2 點到 3 點之間，我的 ETH 成交均價是多少？」時，答案就來自 SQL。

金融資料天然是關係型的。一筆成交關聯著一張訂單，訂單關聯著一個帳戶，帳戶又歸屬於某個使用者。持倉關聯著標的；標的關聯著市場。關係模型——由行和列組成、並透過外來鍵相互連線的表——能自然地對映這些關係。正因如此，PostgreSQL、MySQL、SQL Server 這類關係型資料庫至今仍是金融基礎設施的骨幹，從 Goldman Sachs 到 GaiaEx 概莫能外。

SQL 的威力在於它的宣告式特性：你只需描述你想要什麼，而不必說明如何去拿到它。資料庫引擎會自行算出最優的執行計劃。當你的成交歷史表有 5 億行、卻需要在一秒內拿到結果時，這一點至關重要。寫對查詢、建對索引，SQL 就能交付結果——無論你是在本地的 PostgreSQL 例項上跑，還是在雲端規模的資料倉儲上跑。

關係模型（簡化） users user_id PK … accounts user_id FK → users tier, limits … trades user_id FK → accounts symbol, price, time … JOIN 沿著外來鍵展開——金融資料的形態本就是關係型的。
成交掛在帳戶和使用者之下：這正是你的 SQL JOIN 所遍歷的那些連結。

SELECT、JOIN 與 WHERE：查詢成交資料

我們先從一個具體的 schema 開始。設想 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 新手和 SQL 實戰派的分水嶺。它們讓你能在與當前行相關的一組行上進行計算——而不會把結果坍縮成單一的聚合值。可以把它們想象成在資料上「滾動運算」。

金融分析中最常用的視窗函式：

  • ROW_NUMBER() —— 在一個分割槽內為每一行分配一個遞增的整數。常用於去重，或選出每個 symbol 的第 N 筆成交。
  • LAG() 與 LEAD() —— 訪問上一行或下一行的值。計算逐筆成交收益率，或檢測時間序列資料中的缺口時不可或缺。
  • SUM() OVER () —— 累計求和。計算累計成交量、累計盈虧（P&L），或滾動的持倉規模。
  • 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;

視窗函式不會減少你的行數——和 GROUP BY 不同，每一個輸入行都會產出一個輸出行。這使得它們非常適合在詳盡的成交日誌上增加分析列，又不損失粒度。可以把它們看作是在「豐富」你的資料，而不是在「彙總」資料。

對於 OHLCV 聚合——也就是 K 線圖的基礎——你可以把 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：組織複雜的分析型查詢

公共表表示式（CTE）讓你能把一個複雜查詢拆分成一個個命名清晰、可讀性強的階段——就像程式設計裡的函式一樣。它們用 WITH 關鍵字引入，並且可以按順序相互引用。

假設你想找出自己最賺錢的 10 個交易日，但盈利能力需要計算每天扣除手續費後的淨盈虧（P&L）。藉助 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 也會逼出順序掃描（sequential scan）——把表裡每一行都讀一遍。在 5 億行的規模下，這意味著以分鐘計、而不是以毫秒計。

對於金融時間序列資料，最關鍵的索引模式是建在 (symbol, executed_at) 上的複合 B-tree 索引：

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

這一個索引就能加速大多數分析型查詢：「過去一小時所有 ETH 成交」「兩個時間戳之間的 BTC 成交」或「每個 symbol 的最新一筆成交」。列的順序很重要——symbol 放在前面以支援等值過濾，隨後 executed_at 在索引的該分段內支援高效的範圍掃描。

對於增長到數億行以上的表，表分割槽（table partitioning）必不可少。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。這一技術叫做分割槽裁剪（partition pruning），能在大型歷史資料集上帶來數量級的提速。

時間範圍分割槽裁剪 Q1 2026 1–3 月的行 Q2 2026 跳過 Q3 2026 Q4 2026 查詢：WHERE executed_at 落在 3 月 → 規劃器只讀 Q1 複合索引 (symbol, executed_at) 在每個分割槽內部仍然有用
分割槽界定了時間範圍；裁剪會跳過整塊不可能匹配的資料。

PostgreSQL vs ClickHouse vs BigQuery：選對引擎

並不是所有資料庫在處理金融分析時都旗鼓相當。如何選擇，取決於你的查詢模式、資料量和延遲要求。

PostgreSQL 是主力。它擅長事務型負載（實時記錄成交），支援 ACID 保證，能從容處理複雜的 JOIN，並在配以恰當的索引和分割槽後擴充套件到數億行。它是運營型資料庫——也就是「記錄系統」——的正確選擇。舉例來說，GaiaEx 就依託相容 PostgreSQL 的基礎設施承載其核心交易資料，並具備金融系統所要求的可靠性保證。

ClickHouse 是一種列式資料庫，專為在數十億行上做分析型查詢而設計。PostgreSQL 按行儲存資料（很適合插入單筆成交），而 ClickHouse 按列儲存資料（很適合在數百萬行上聚合某一列）。像「BTC 三年間的小時平均成交量」這樣一條查詢，在 PostgreSQL 上可能要 30 秒，在 ClickHouse 上也許 200 毫秒就跑完了。代價是：ClickHouse 並不能高效地支援 UPDATE 或 DELETE——它在設計上是僅追加（append-only）的。

BigQuery（Google Cloud）是一種無伺服器的列式資料倉儲。無需管理基礎設施，按查詢計費，具備 PB 級容量。它非常適合臨時分析、研究和探索——但查詢延遲以秒計、而非以毫秒計，因此不適合實時應用。

  • 實時成交記錄與訂單管理 → PostgreSQL
  • 在數十億行上做歷史分析與回測 → ClickHouse
  • 臨時探索與跨團隊資料共享 → BigQuery

許多專業交易公司會在一套分層架構裡同時使用這三者：PostgreSQL 作為熱的運營儲存，ClickHouse 作為溫的分析層，BigQuery 作為冷的歸檔。寫好 SQL 的能力可以在三者之間通用——語法有 90% 是一致的，而分析的思路則是 100% 相同。