SQLite多子查询与窗口操作：批量计算标的10日涨跌幅总和

• 涉及两张表结构：
  • api_results：存储标的记录，核心字段为ticker、entry、date、change
  • historical_data：存储全量标的历史行情数据，核心字段为Ticker、Date、Change，单表数据量超100万行
• 核心计算逻辑：针对api_results中每一组(ticker, date)配对，计算historical_data表中对应ticker下、从目标日期开始按日期升序排列的连续10条记录的Change字段总和
• 现存问题：指定固定参数的单条SQL可以返回正确结果，但无法批量适配所有配对；现有Python全表加载+逐行循环的方案运行效率极低，执行2小时仍未完成。

该需求完全可以通过SQL实现，借助窗口函数给每个标的下的行情记录按日期打行号，再关联配对表做范围聚合即可，SQLite 3.25及以上版本均支持窗口函数语法。

注意：你之前写的单条查询缺少ORDER BY Date子句，实际运行时可能因为数据存储顺序不确定返回错误的10条记录，以下写法显式指定排序规则，可避免该问题。

-- 给历史表每个标的下的记录按日期排序打行号
WITH hd_ranked AS (
    SELECT
        Ticker,
        Date,
        Change,
        ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY Date) AS rn
    FROM historical_data
),
-- 关联拿到每个api配对对应的起始行号
api_start AS (
    SELECT
        a.ticker,
        a.date AS target_date,
        h.rn AS start_rn
    FROM api_results a
    JOIN hd_ranked h
      ON a.ticker = h.Ticker AND a.date = h.Date
)
-- 关联取起始行号往后共10条记录求和
SELECT
    s.ticker,
    s.target_date,
    SUM(h.Change) AS sum_10_change
FROM api_start s
JOIN hd_ranked h
  ON s.ticker = h.Ticker
  AND h.rn BETWEEN s.start_rn AND s.start_rn + 9
GROUP BY s.ticker, s.target_date;

执行前给historical_data表建立(Ticker, Date)联合索引，百万行数据的查询耗时通常可控制在秒级。

原有Python代码效率低的核心原因是逐行全表扫描查找日期索引，时间复杂度极高，可通过分组+索引对齐+减少全表遍历的方式优化，优化后代码如下：

import sqlite3
import pandas as pd
from config import db_path

conn = sqlite3.connect(db_path)
# 读取时直接按标的、日期排序，减少后续处理
hd_df = pd.read_sql(
    "SELECT Ticker, Date, Change FROM historical_data ORDER BY Ticker, Date",
    conn,
    parse_dates=["Date"]
)
api_df = pd.read_sql(
    "SELECT ticker, date as target_date FROM api_results",
    conn,
    parse_dates=["target_date"]
)
conn.close()

# 设置多层索引提升定位效率
hd_df = hd_df.set_index(["Ticker", "Date"])
res = []

# 按标的分组处理，避免跨标的全表扫描
for ticker, ticker_hd in hd_df.groupby(level="Ticker"):
    # 提取当前标的下所有需要计算的目标日期
    target_dates = api_df[api_df["ticker"] == ticker]["target_date"]
    date_seq = ticker_hd.index.get_level_values("Date")
    # 二分法批量查找所有起始位置，比逐行list.index快数百倍
    start_positions = date_seq.searchsorted(target_dates)
    for pos in start_positions:
        sum_10 = ticker_hd.iloc[pos:pos+10]["Change"].sum()
        res.append((ticker, date_seq[pos], sum_10))

result_df = pd.DataFrame(res, columns=["ticker", "target_date", "sum_10_change"])

优化后的Python方案在百万行数据量级下，耗时通常可控制在分钟级，相比原有循环方案性能提升100倍以上。

内容的提问来源于stack exchange，提问作者rickturner2001