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

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

小象量化极客小册 · 华尔街量化分析先锋系列

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

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

💡 核心导读

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

连接类型与原理

基础连接类型

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

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

   A表：       B表：       结果：
   ┌───┐      ┌───┐      ┌───────┐
   │id │      │id │      │A.id│B.id│
   ├───┤      ├───┤      ├───────┤
   │ 1 │      │ 2 │      │ 3 │ 3 │
   │ 2 │      │ 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

💡 概念小测：SQL连接类型

在合并“股票基础信息表 (info)”和“每日行情价格表 (prices)”时，如果有一只新发行的股票在 prices 表中还没有任何报价记录，我们希望在关联后依然保留该股票的基本信息（价格显示为 NULL），应该使用哪种连接方式？

• INNER JOIN，因为这会自动剔除两边不完全匹配的记录。
• LEFT JOIN，以 info 表为主表进行左连接，右表缺失字段自动填充为 NULL。
• RIGHT JOIN，以 prices 表为主表进行右连接。
• FULL OUTER JOIN，因为这会将两表无条件进行笛卡尔积乘积。

📝 选择题

💡 概念小测：SQL连接类型 在合并“股票基础信息表 (info)”和“每日行情价格表 (prices)”时，如果有一只新发行的股票在 prices 表中还没有任何报价记录，我们希望在关联后依然保留该股票的基本信息（价格显示为 NULL），应该使用哪种连接方式？

• A. INNER JOIN，因为这会自动剔除两边不完全匹配的记录。
• B. LEFT JOIN，以 info 表为主表进行左连接，右表缺失字段自动填充为 NULL。
• C. RIGHT JOIN，以 prices 表为主表进行右连接。
• D. FULL OUTER JOIN，因为这会将两表无条件进行笛卡尔积乘积。

答案：B（LEFT JOIN，以 info 表为主表进行左连接，右表缺失字段自动填充为 NULL。）

Pandas 实现方案

import pandas as pd

df_orders = pd.DataFrame({
    'order_id': [1001, 1002, 1003],
    'customer_id': [201, 202, 203],
    'amount': [150, 200, 180]
})

df_customers = pd.DataFrame({
    'customer_id': [201, 202, 204],
    'name': ['小象咖啡', '腾讯控股', '贵州茅台'],
    'vip_level': [1, 3, 2]
})

result = pd.merge(
    left=df_orders,
    right=df_customers,
    how='inner',  # 连接类型
    on='customer_id',  # 连接键
    suffixes=('_order', '_cust')  # 列名后缀
)

print("连接结果：\n", result.to_markdown(index=False))

执行结果：

|   order_id |   customer_id |   amount | name   |   vip_level |
|-----------:|--------------:|---------:|:-------|------------:|
|       1001 |           201 |      150 | 小象咖啡   |           1 |
|       1002 |           202 |      200 | 腾讯控股   |           3 |

SQLAlchemy 高级连接

from sqlalchemy import create_engine, MetaData, Table, select

engine = create_engine('sqlite:///:memory:')

df_orders.to_sql('orders', engine, index=False)
df_customers.to_sql('customers', engine, index=False)

metadata = MetaData()
orders = Table('orders', metadata, autoload_with=engine)
customers = Table('customers', metadata, autoload_with=engine)

join_query = select(
    orders.c.order_id,
    customers.c.name,
    orders.c.amount
).select_from(
    orders.join(
        customers,
        orders.c.customer_id == customers.c.customer_id,
        isouter=True  # 左连接
    )
)

with engine.connect() as conn:
    result = conn.execute(join_query)
    print("\nSQL执行结果：")
    for row in result:
        print(f"订单ID:{row.order_id} 客户:{row.name} 金额:{row.amount}")

输出示例：

订单ID:1001 客户:小象咖啡 金额:150
订单ID:1002 客户:腾讯控股 金额:200 
订单ID:1003 客户:None 金额:180

性能优化策略

1. 索引优化

   CREATE INDEX idx_customer_id ON orders(customer_id);

2. 连接字段选择原则

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

3. 大表连接技巧

   # 分块连接大表
   chunksize = 100000
   for chunk in pd.read_sql('large_table', engine, chunksize=chunksize):
       result = pd.merge(chunk, small_table, on='key')
       # 处理结果...

💡 代码填空：关联两张表

补全以下 SQL 查询，将 prices 表与 info 表通过相同的 ticker 字段进行内连接：

SELECT p.date, p.ticker, p.close, i.sector 
FROM prices p 
INNER JOIN info i 
{blank} p.ticker = i.ticker;

• WHERE
• USING
• ON

✏️ 填空题

💡 代码填空：关联两张表 补全以下 SQL 查询，将 prices 表与 info 表通过相同的 ticker 字段进行内连接：

SELECT p.date, p.ticker, p.close, i.sector 
FROM prices p 
INNER JOIN info i 
___ p.ticker = i.ticker;

答案：ON

本章小结

行动清单

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

「小象量化教研组」