量化多表连接：关联股票信息与行情

小象实战讲义 · 量化交易Python入门：从咖啡馆到华尔街

在量化交易中，历史回测数据往往以多种异构本地文件（TXT、CSV、JSON、Excel）和关系数据库形式存在。本章你将编写数据接口，读取小象咖啡的门店营收数据并解析股票行情API响应，打通量化回测的数据通道！

💡 核心导读

本节将深入讲解数据库多表连接的核心技术与实战应用。通过学习，您将掌握：1) 四种基础连接类型（内连接、左连接、右连接、全连接）的原理与适用场景；2) 使用 Pandas 与标准库 sqlite3 实现多表连接操作；3) 复杂业务场景下的连接优化策略。重点培养跨数据源整合能力，实现高效的多维数据分析。

连接类型与原理

基础连接类型

数据库表连接是通过共享字段将多个表的数据关联起来的操作，主要分为：

1. 内连接(INNER JOIN)
   仅保留两表中匹配的记录

   A表：       B表：       结果：
   ┌───┐      ┌───┐      ┌───────┐
   │id │      │id │      │A.id│B.id│
   ├───┤      ├───┤      ├───────┤
   │ 1 │      │ 2 │      │ 2 │ 2 │
   │ 2 │      │ 3 │      │ 3 │ 3 │
   │ 3 │      │ 4 │      └───────┘
   └───┘      └───┘

2. 左连接(LEFT JOIN)
   保留左表所有记录，右表无匹配则填充NULL

连接条件设计

• 等值连接：ON A.key = B.key
• 非等值连接：ON A.value > B.value
• 复合条件连接：ON A.key1=B.key1 AND A.key2=B.key2

Pandas 实现方案

import pandas as pd

df_stock_info = pd.DataFrame({
    'stock_code': ['000001.SZ', '000002.SZ', '000003.SZ'],
    'stock_name': ['平安银行', '万科A', 'PT金田A'],
    'industry': ['银行', '房地产', '综合']
})

df_stock_price = pd.DataFrame({
    'stock_code': ['000001.SZ', '000002.SZ', '000004.SZ'],
    'trade_date': ['2024-01-15', '2024-01-15', '2024-01-15'],
    'close': [10.5, 8.2, 12.1]
})

result = pd.merge(
    left=df_stock_info,
    right=df_stock_price,
    how='inner',
    on='stock_code',
    suffixes=('_info', '_price')
)

print("股票信息与行情内连接结果：")
print(result.to_string(index=False))

执行结果：

股票信息与行情内连接结果：
stock_code stock_name industry trade_date  close
 000001.SZ       平安银行       银行 2024-01-15   10.5
 000002.SZ        万科A      房地产 2024-01-15    8.2

用 SQL 直接做连接（标准库 sqlite3）

import pandas as pd
import sqlite3

df_stock_info = pd.DataFrame({
    'stock_code': ['000001.SZ', '000002.SZ', '000003.SZ'],
    'stock_name': ['平安银行', '万科A', 'PT金田A'],
    'industry': ['银行', '房地产', '综合']
})

df_stock_price = pd.DataFrame({
    'stock_code': ['000001.SZ', '000002.SZ', '000004.SZ'],
    'trade_date': ['2024-01-15', '2024-01-15', '2024-01-15'],
    'close': [10.5, 8.2, 12.1]
})

conn = sqlite3.connect(':memory:')
df_stock_info.to_sql('stock_info', conn, index=False, if_exists='replace')
df_stock_price.to_sql('stock_price', conn, index=False, if_exists='replace')

query = """
SELECT i.stock_code, i.stock_name, p.close
FROM stock_info i
LEFT JOIN stock_price p ON i.stock_code = p.stock_code
"""

result_df = pd.read_sql_query(query, conn)
conn.close()

print("SQL左连接执行结果：")
print(result_df.to_string(index=False))

版本提示：DataFrame.read_sql_query 在 pandas 3.0 中已标记弃用。本讲义为保持与原始代码一致仍沿用它，功能正常；若你在新项目中写这类代码，推荐直接用标准库 sqlite3 执行 SQL 后用 pd.read_sql_query 之外的方式取数（例如 cursor.fetchall() 构造 DataFrame），或改用仍受支持的 pandas.read_sql（配合 SQLAlchemy 连接对象）。

输出示例：

SQL左连接执行结果：
stock_code stock_name  close
 000001.SZ       平安银行   10.5
 000002.SZ        万科A    8.2
 000003.SZ      PT金田A    NaN

性能优化策略

1. 索引优化

   CREATE INDEX idx_customer_id ON orders(customer_id);

2. 连接字段选择原则

   • 优先使用整型字段
   • 避免NULL值过多的字段
   • 字段长度尽量短

3. 大表连接技巧（伪代码示意，不作实跑）

   # 分块读取大表 → 逐块与小表 merge → 逐块落库
   for chunk in read_large_table_in_chunks():
       result = merge(chunk, small_table, on='key')
       write_to_db(result)

📝 动手练一练

第 1 题 · 概念应用

你正在构建一个多因子选股模型，需要将“因子暴露表”（factor_exposure，包含 stock_code 和 value_factor 等列）与“股票池表”（stock_universe，包含 stock_code 和 is_constituent 列）进行关联。股票池表定义了当前可投资的标的（is_constituent 为 True）。你希望最终的分析结果只包含股票池内的股票，即使某些股票在因子暴露表中没有数据（其因子值记为 NaN）。请问，为了实现这个目标，你应该使用哪种连接方式？

• A. 对 factor_exposure 和 stock_universe 进行 INNER JOIN，连接键为 stock_code。
• B. 以 factor_exposure 为主表，对 stock_universe 进行 LEFT JOIN，连接键为 stock_code。
• C. 以 stock_universe 为主表，对 factor_exposure 进行 LEFT JOIN，连接键为 stock_code。
• D. 对 factor_exposure 和 stock_universe 进行 FULL OUTER JOIN，连接键为 stock_code，不做任何筛选。

第 2 题 · 动手题（代码填空）

以下代码模拟了一个简单的回测数据准备场景：将持仓记录表（holdings）与行情价格表（prices）进行关联，以计算持仓市值。请补全 pd.merge 函数中的 how 参数，以实现 以持仓记录表为准，保留所有持仓记录，即使某只股票在指定日期没有行情数据（此时收盘价记为 NaN） 的效果。

import pandas as pd
import numpy as np

# 模拟持仓记录
df_holdings = pd.DataFrame({
    'date': ['2024-01-15', '2024-01-15', '2024-01-15'],
    'stock_code': ['AAPL', 'GOOGL', 'TSLA'],
    'shares': [100, 50, 200]
})

# 模拟行情数据（注意：缺少 TSLA 的数据）
df_prices = pd.DataFrame({
    'date': ['2024-01-15', '2024-01-15'],
    'stock_code': ['AAPL', 'GOOGL'],
    'close': [182.5, 142.0]
})

# 请补全下方 merge 函数的 how 参数
df_merged = pd.merge(
    left=df_holdings,
    right=df_prices,
    on=['date', 'stock_code'],
    how=______,  # 请在此处填空
    suffixes=('_holding', '_price')
)

# 计算持仓市值，缺失价格按0处理（仅为演示）
df_merged['market_value'] = df_merged['shares'] * df_merged['close'].fillna(0)
print(df_merged[['date', 'stock_code', 'shares', 'close', 'market_value']].to_string(index=False))

import pandas as pd
import numpy as np

# 模拟持仓记录
df_holdings = pd.DataFrame({
    'date': ['2024-01-15', '2024-01-15', '2024-01-15'],
    'stock_code': ['AAPL', 'GOOGL', 'TSLA'],
    'shares': [100, 50, 200]
})

# 模拟行情数据（注意：缺少 TSLA 的数据）
df_prices = pd.DataFrame({
    'date': ['2024-01-15', '2024-01-15'],
    'stock_code': ['AAPL', 'GOOGL'],
    'close': [182.5, 142.0]
})

# 补全后的 merge 函数调用
df_merged = pd.merge(
    left=df_holdings,
    right=df_prices,
    on=['date', 'stock_code'],
    how='left',  # 以持仓表为主表进行左连接
    suffixes=('_holding', '_price')
)

# 计算持仓市值，缺失价格按0处理（仅为演示）
df_merged['market_value'] = df_merged['shares'] * df_merged['close'].fillna(0)
print(df_merged[['date', 'stock_code', 'shares', 'close', 'market_value']].to_string(index=False))

       date stock_code  shares  close  market_value
2024-01-15       AAPL     100  182.5       18250.0
2024-01-15      GOOGL      50  142.0        7100.0
2024-01-15       TSLA     200    NaN           0.0

本章小结

📋 行动清单

• 使用Pandas实现订单表与产品表的左连接，分析未匹配订单的特征
• 在SQLite中创建包含索引的两张关联表，对比索引前后的查询性能差异
• 编写包含三个表连接的复杂查询（订单-客户-产品），输出每种产品的VIP客户消费统计

—— 小象教研组