面向量化研究的关系型 SQL

某个周二早晨，沪深 300 量化私募的基金经理走过来：『把过去两周 510050、510500、510300 的日 VWAP 拉给我，按当日收益做横截面排名，只要 close 非空的行』。数据存在研究数据仓库里——一台部署在内网的 Postgres / PolarDB-O 上，bars_1m 1 分钟 K 线表和 instrument 维度表通过外键连接。你只有十分钟。你不会写一个把整张表拉到内存里的 pandas 脚本——那张表有上百亿行。你要写的是直接在数据库里完成计算、只把四百行结果传回来的 SQL。本课教的就是让这种十分钟交付成为日常的 SQL 词汇。

什么是关系型数据库，什么不是

关系型数据库存储的是带类型的二维表：每张表由若干行组成，每一行是一条观测，每一列是一个固定类型的属性；不同表之间通过键列做连接，用 SQL 查询。这就是全部心智模型。表是单位，行是记录，列是带类型的属性。它​​不是​​键值存储，不是文档数据库，也不是流式系统。本课讲两种参考方言：Postgres（Linux 上量化研究仓库的标准选择）和 SQLite（『手头有个文件、想像数据库一样查询它、又不想专门部署一套』时通过 sqlite3 CLI 或 DuckDB 用的轻量方案）。Snowflake、BigQuery、DuckDB、ClickHouse 上 下面 这些 SQL 都能直接跑，仅时间戳类型 的 拼写 略有不同——记下 这一点，向下走。

SELECT 流水线按逻辑顺序执行，不是按书写顺序

基础 SELECT 的七个关键字书写顺序是一种，执行顺序是另一种。记住七阶段的逻辑执行顺序：

1. FROM 选定表或连接。
2. WHERE 过滤行（此时无法引用 SELECT 别名，因为别名 这一阶段 还不存在）。
3. GROUP BY 把行折叠成组。
4. HAVING 过滤组（可以引用聚合别名，因为聚合已经算完）。
5. SELECT 投影出要的列或聚合。
6. ORDER BY 排序。
7. LIMIT 取前 N 条。

这套执行顺序解释了为什么 SELECT AVG(close) AS avg_close FROM bars_1m WHERE avg_close > 100 是语法错误：别名 avg_close 在 WHERE 运行那一刻还不存在。改写成 SELECT symbol, AVG(close) AS avg_close FROM bars_1m GROUP BY symbol HAVING AVG(close) > 100，同样的意图就是合法的。

标准的 bar 表 DDL

下面所有例子都用这张表。习惯它。

CREATE TABLE bars_1m (
  symbol  TEXT       NOT NULL,
  ts      TIMESTAMPTZ NOT NULL,
  open    NUMERIC(18,6),
  high    NUMERIC(18,6),
  low     NUMERIC(18,6),
  close   NUMERIC(18,6),
  volume  BIGINT,
  PRIMARY KEY (symbol, ts)
);

研究仓库里价格列用 NUMERIC(18,6) 不容商量：FLOAT / REAL / DOUBLE PRECISION 在几百万行 SUM(close * volume) 后会累积浮点误差，算出来的 VWAP 在第五位小数上就漂了。另一种 选择——用 BIGINT 存『分/最小报价单位』整数——专属 超低延迟 系统，留给 第 4 课。TIMESTAMPTZ 底层 存 UTC，给 分析师 报告 时 用 AT TIME ZONE 'Asia/Shanghai' 渲染 成 本地 时间。PRIMARY KEY (symbol, ts) 是 bar 表 的 自然 复合 键——第 2 课 会 论证 这个 选择；现在 先 接受 它。

JOIN 类型，一段一种

INNER JOIN 只保留两边都匹配的行——把 bars_1m 连到『当前在册』的标的列表时用它。LEFT JOIN 保留左侧每一行，右侧 没匹配 的 列 置 NULL——量化里最常用，因为你想保留每根 bar，即使小的维度表有缺口。FULL OUTER JOIN 保留两边所有行——少见；用于两条数据源对账时。CROSS JOIN 是笛卡尔积（左边每行配右边每行）——几乎从不是你想要的；点名 就走。LATERAL 是 FROM 子句 里 可以 引用 前面 行 的 子查询——这里 点名 备录，详讲 推迟。CTE 与 子查询 的 规则：一次性 子查询 行内 写 没问题；只要 被 引用 两次 或 嵌套 超过 一层，就 重构 成 WITH cte AS (...) SELECT ... FROM cte——可读性 随 reviewer 重新 解析 查询 的 次数 而 放大。

NULL 是三值逻辑的地雷

NULL 表示『值未知』，不是『值为零』，也不是『值等于 NULL』。这个区分有三个操作层面的后果。第一，​**WHERE x IS NULL 是对的；WHERE x = NULL 永远返回 UNKNOWN​**​，因此过滤掉每一行——一个静默的 bug。第二，对 NULL 做算术得到 NULL：只要 close 或 volume 之一是 NULL，close * volume 就是 NULL。第三，大多数聚合忽略 NULL；唯一的例外是 COUNT(*)，它把每一行都算进来，包括所有列都是 NULL 的行。三件套：

SELECT
  COUNT(*)                  AS total_rows,
  COUNT(close)              AS rows_with_close,
  COUNT(DISTINCT symbol)    AS unique_symbols
FROM bars_1m;

SELECT COUNT(*) FROM bars_1m WHERE close IS NULL;  -- correct
SELECT COUNT(*) FROM bars_1m WHERE close = NULL;   -- always returns 0

COUNT(*) 是每一行，COUNT(close) 是 close 非空的行，COUNT(DISTINCT symbol) 是不同标的数。IS NULL 与 = NULL 的对比，在代码评审里抓出的 bug 比任何其他单一 SQL 习惯都多——看到 就 提示。

时间与时区：存 UTC，渲染本地

Postgres 有两种时间戳类型：TIMESTAMP WITHOUT TIME ZONE（一个不带时区的墙钟字符串——几乎从来不是你想要的）和 TIMESTAMPTZ（底层 UTC，按会话时区渲染）。永远存 UTC，用 AT TIME ZONE 'Asia/Shanghai' 渲染。典型地雷：vendor 的 CSV 时间戳已经是本地时间，分析师 join 到 UTC 的 tick 流时 偏 八 小时。补救 是 在 ingest 阶段 转 UTC：WHERE ts::timestamptz AT TIME ZONE 'Asia/Shanghai' BETWEEN '09:30' AND '11:30' 过滤 A 股 上午 连续 竞价。Tushare / 万得 的 trade_date 是 YYYYMMDD 整数 字符串——这个 约定 跨日 join 时 是错的；在 ingest 一次 把 它 转 成 TIMESTAMPTZ，仓库 内 之后 全 当 TIMESTAMPTZ 用。

窗口函数是量化 SQL 的日常面包

OVER (PARTITION BY symbol ORDER BY ts ROWS BETWEEN N PRECEDING AND CURRENT ROW) 在每个标的的按时间排序的行内、按滚动窗口算聚合——你读过的每一个回测里的滚动均值与滚动波动率都在用它。bars_1m 上的四个标准模式：

WITH r AS (
  SELECT
    symbol,
    ts,
    close,
    LAG(close, 1) OVER (PARTITION BY symbol ORDER BY ts) AS prev_close,
    (close / LAG(close, 1) OVER (PARTITION BY symbol ORDER BY ts) - 1) AS ret,
    AVG(close) OVER (PARTITION BY symbol ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS sma_20
  FROM bars_1m
)
SELECT
  symbol, ts, close, prev_close, ret, sma_20,
  RANK() OVER (PARTITION BY DATE(ts) ORDER BY ret DESC) AS daily_rank
FROM r
WHERE ret IS NOT NULL;

逐 个 读：LAG(close, 1) 在 每个 symbol 时间 序列 里 回看 一行——上一根 bar 的 close。bar 收益 是 close / prev_close - 1。AVG ... ROWS BETWEEN 19 PRECEDING AND CURRENT ROW 是 20 周期 SMA。RANK ... PARTITION BY DATE(ts) 是 当日 横截面 收益 排名。另 有 ROW_NUMBER() OVER (PARTITION BY symbol, ts ORDER BY ingested_at DESC) = 1 去重 惯用 法。一 个 坑：LAST_VALUE 默认 框 是 UNBOUNDED PRECEDING AND CURRENT ROW，几乎 从 不 是 你 想要 的。

日 VWAP 汇总

历史上被问得最多的量化 SQL：

SELECT
  symbol,
  DATE(ts) AS d,
  SUM(close * volume) / NULLIF(SUM(volume), 0) AS vwap_proxy
FROM bars_1m
WHERE ts >= '2026-04-07' AND ts < '2026-04-21'
GROUP BY symbol, DATE(ts)
ORDER BY d, symbol;

NULLIF(SUM(volume), 0) 防止零成交量 bar 触发除零。真正的 VWAP 用 每笔 price * size 除以 SUM(size)——bar 收盘 × bar 成交量 是 教学 简化；L4 capstone 做 真正的 tick-级 版本。这条 查询 对 '510050'、'510500'、'510300' 两 周 大约 返回 三十 行。模式 可以 推广 到 任何 汇总：选 一个 桶 表达式（DATE(ts)、date_trunc('hour', ts)），GROUP BY 它，做 聚合。

子查询 vs CTE

一次性的内联 (SELECT ...) 没问题。被引用两次或嵌套两层，可读性悬崖就到了——把它重构成查询顶部的 CTE 链，正文像散文一样读下来。上面窗口函数例子里的 CTE 是标准形式：一个命名中间结果（r）持有行级收益，外层查询消费它。CTE 也是 封装『被 两个 后续 段落 都引用 的 join』的 合适 位置。

DDL 与 DML，带纪律

INSERT 一律 用 参数化 语句——psycopg 的 %s 占位符，不是 Python f-string：cur.execute("INSERT INTO bars_1m (symbol, ts, close, volume) VALUES (%s, %s, %s, %s)", (sym, ts, close, vol))。f-string 拼 WHERE 子句 即使 在 内网 仓库 上 也 是 SQL 注入 通道（而且 一旦 symbol 里 有 单引号 立刻 崩）。UPDATE bars_1m SET close = 0 WHERE symbol = '510050' 应当 先 把 它 当 SELECT * FROM bars_1m WHERE symbol = '510050' LIMIT 5 跑 一遍 预览——按 回车 之前 把 WHERE 子句 念 出来 给自己 听。DELETE 同 警告。TRUNCATE bars_1m 是 快速 清空（在 部分 方言 里 非事务性——读 手册）。

操作层面的接入点

psql -h host -U user -d db 是 Postgres CLI；凭据 放 ~/.pgpass，权限 0600（linux-and-shell 模块 已 讲），永远 不 在 连接 串 里、永远 不 在 代码 里。Python 侧 标准 桥梁 是 pd.read_sql_query(sql, conn, params=...)——params= 参数 是 参数化 的、安全 的；DataFrame API 本身 在 3.2（Python 数据 与 量化 库）里 讲。SQLite 上，下面 这些 SQL 不 改一行 都 能跑，唯一 差异 是 TIMESTAMPTZ 变 TEXT，出口 时 自己 解析。

纪律总结

每次量化 SQL 落笔结束都套上三条规则。每条查询都参数化——绝不用 f-string 拼 WHERE。每个 SELECT 写出列名——生产代码里永不 SELECT *，因为列序变更会静默地把调用方打挂。任何打到上亿行表的探索性查询都带 LIMIT 100——你的仓库和同事会感谢你。这一层学完，第 2 课在同一张 bars_1m 上加主键加索引、教你读 EXPLAIN (ANALYZE, BUFFERS)，让你判断一条回测查询慢是不是因为执行计划走错了。

​本课构件清单​​。Fenced ```sql 代码块：bar 表 DDL、VWAP 汇总、四 窗口 CTE、IS NULL 与 = NULL 对比 加 COUNT(*) / COUNT(close) / COUNT(DISTINCT symbol)。Inline-code：七 阶段 执行 顺序 FROM / WHERE / GROUP BY / HAVING / SELECT / ORDER BY / LIMIT。一 个 Exercise。Two Hints。市场 数据 锚 是 A 股 沪深 300 标的 '510050'、'510500'、'510300' 在 上证 / 深证 / CFFEX 受 监管 的 私募 / 量化 私募 体系，T+1 结算 与 涨跌停 制度，对应 50ETF 与 300ETF 这类 SSE / SZSE 流通 标的。

练习