开发者编程9 min read

面向金融数据仓库的 SQL

查询、聚合并分析结构化金融数据

分享文章

为什么说 SQL 是金融数据的语言

每一笔成交、每一个 tick、每一次订单簿更新——金融市场源源不断地产生着海量结构化数据。而四十多年来，SQL（结构化查询语言）一直是查询、转换和分析这些数据的主流工具。它不光鲜，也不时髦。但当一位投资组合经理问出「上周二 UTC 时间下午 2 点到 3 点之间，我的 ETH 成交均价是多少？」时，答案就来自 SQL。

金融数据天然是关系型的。一笔成交关联着一张订单，订单关联着一个账户，账户又归属于某个用户。持仓关联着标的；标的关联着市场。关系模型——由行和列组成、并通过外键相互连接的表——能自然地映射这些关系。正因如此，PostgreSQL、MySQL、SQL Server 这类关系型数据库至今仍是金融基础设施的骨干，从 Goldman Sachs 到 GaiaEx 概莫能外。

SQL 的威力在于它的声明式特性：你只需描述你想要什么，而不必说明如何去拿到它。数据库引擎会自行算出最优的执行计划。当你的成交历史表有 5 亿行、却需要在一秒内拿到结果时，这一点至关重要。写对查询、建对索引，SQL 就能交付结果——无论你是在本地的 PostgreSQL 实例上跑，还是在云端规模的数据仓库上跑。

成交挂在账户和用户之下：这正是你的 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），能在大型历史数据集上带来数量级的提速。

分区界定了时间范围；裁剪会跳过整块不可能匹配的数据。

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% 相同。

Type	Taker Fee	Maker Fee
Perpetuals	0.0675%	0.0300%
Spot	0.0675%	0.0300%