如何高效从SQLite数据库筛选大量数据并转换为Numpy数组

数据老陈

2026-10-4

解决方案

方案1：全量读取后Pandas聚合（内存足够优先选）

你原来的方案慢的核心原因是每次单用户查询都要重复执行表关联操作，相当于每次查询都要全量扫描三张表再过滤，单次查询耗时自然和全量查询接近，循环N次就会产生N倍的冗余计算开销。

如果你的数据集总大小可以被内存容纳，直接全量读取关联后的必要字段到Pandas聚合是最高效的方案，全程仅需要执行1次表关联操作：

# 1. 一次性读取所有需要的字段
df = pd.read_sql_query('''
    SELECT FirstName, LastName, State, StartTime, StopTime
    FROM Input
    INNER JOIN Input1 ON ... 
    INNER JOIN Input2 ON ...
''', conn)

# 2. 按用户维度分组聚合时间序列
res_df = df.groupby(['FirstName', 'LastName', 'State']).apply(
    lambda x: np.concatenate([x['StartTime'].values, x['StopTime'].values])
).reset_index(name='时间序列')

# 如需对时间点去重的话替换上面的lambda逻辑为：
# lambda x: np.unique(np.concatenate([x['StartTime'].values, x['StopTime'].values]))

# 3. 写入新表
res_df.to_sql('目标表名', conn, if_exists='replace', index=False)

方案2：SQL层面预聚合（数据量过大内存不足时选）

如果你使用的SQLite版本 >= 3.39.0及以上，可以直接用内置的聚合函数在查询阶段直接完成数据拼接，无需全量加载原始数据到内存：

SELECT 
    FirstName,
    LastName,
    State,
    json_group_array(StartTime || ',' || StopTime) AS 时间序列
FROM (
    SELECT FirstName, LastName, State, StartTime, StopTime
    FROM Input
    INNER JOIN Input1 ON ...
    INNER JOIN Input2 ON ...
) t
GROUP BY FirstName, LastName, State

读取结果后，将拼接好的字符串拆分转换为numpy数组即可。