量化数据库：SQL常用查询语法

量化数据库：SQL常用查询语法

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

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

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

💡 核心导读

本节将介绍数据库基础概念和SQL核心语法，帮助读者掌握数据采集场景下的数据库操作技能。重点包括：数据库连接方法、CRUD操作、聚合查询以及Python与数据库的交互实践。通过本节学习，读者将能够使用SQL语句高效处理本地数据库中的多源数据，并实现与Python工作流的无缝衔接。

数据库基础操作

连接数据库

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')

data = {'id': [1, 2, 3], 'name': ['小象咖啡', '腾讯控股', '贵州茅台']}
df = pd.DataFrame(data)
df.to_sql('users', conn, index=False, if_exists='replace')

pd.read_sql('SELECT * FROM users', conn)

执行结果：

   id name
0   1   小象咖啡
1   2   腾讯控股
2   3   贵州茅台

表结构操作

-- 创建新表
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK(price > 0)
);

-- 查看表结构
.schema products

💡 概念小测：SQL数据过滤

在量化选股中，如果要查询股票代码（ticker）为 ‘AAPL’ 且收盘价（close）大于 150 美元的所有成交数据，正确的 SQL 查询语句是？

• SELECT * FROM stocks WHERE ticker = 'AAPL' OR close > 150
• SELECT * FROM stocks WHERE ticker = 'AAPL' AND close > 150
• SELECT ticker FROM stocks HAVING close > 150
• SELECT stocks WHERE ticker = 'AAPL' LIMIT 150

📝 选择题

💡 概念小测：SQL数据过滤 在量化选股中，如果要查询股票代码（ticker）为 ‘AAPL’ 且收盘价（close）大于 150 美元的所有成交数据，正确的 SQL 查询语句是？

• A. SELECT * FROM stocks WHERE ticker = 'AAPL' OR close > 150
• B. SELECT * FROM stocks WHERE ticker = 'AAPL' AND close > 150
• C. SELECT ticker FROM stocks HAVING close > 150
• D. SELECT stocks WHERE ticker = 'AAPL' LIMIT 150

答案：B（SELECT * FROM stocks WHERE ticker = 'AAPL' AND close > 150）

SQL核心语法实战

数据查询基础

query = '''
SELECT 
    u.name as user_name,
    p.name as product_name,
    o.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.product_id
WHERE o.quantity > 1
ORDER BY o.quantity DESC
'''

orders_data = [
    (1, 1, 3), (2, 3, 1), (3, 2, 2)
]
pd.DataFrame(orders_data, columns=['order_id', 'user_id', 'product_id']).to_sql('orders', conn, index=False, if_exists='replace')

数据修改操作

try:
    cursor = conn.cursor()
    cursor.execute("BEGIN TRANSACTION")
    cursor.execute("UPDATE products SET price = price * 0.9 WHERE product_id = 1")
    cursor.execute("INSERT INTO orders VALUES (4, 1, 2, 1)")
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"事务失败: {e}")

Python与数据库交互

使用Pandas批量操作

new_products = pd.DataFrame({
    'product_id': [4, 5],
    'name': ['键盘', '鼠标'],
    'price': [299.0, 159.0]
})
new_products.to_sql('products', conn, if_exists='append', index=False)

user_id = 2
orders = pd.read_sql('SELECT * FROM orders WHERE user_id = ?', conn, params=(user_id,))

数据库性能优化

conn.execute("CREATE INDEX idx_user_id ON orders(user_id)")

plan = conn.execute("EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 1").fetchall()
for line in plan:
    print(line)

💡 代码填空：过滤低成交股票

补全以下 SQL 查询语句，筛选出股价（price）低于 10 元且交易量（volume）大于 50000 股的股票：

SELECT ticker, price, volume 
FROM stocks 
WHERE price < 10 {blank} volume > 50000;

• OR
• AND
• ON

✏️ 填空题

💡 代码填空：过滤低成交股票 补全以下 SQL 查询语句，筛选出股价（price）低于 10 元且交易量（volume）大于 50000 股的股票：

SELECT ticker, price, volume 
FROM stocks 
WHERE price < 10 ___ volume > 50000;

答案：AND

本章小结

行动清单

• 使用SQLite创建包含3个关联表的数据库，并实现跨表联合查询
• 编写Python脚本实现数据库事务操作，包含成功提交和异常回滚两种情况
• 对10万条测试数据创建适当索引，比较索引前后查询性能差异

小象量化教研组