SQLite如何从主表时间区间匹配副表时间戳？（大数据量优化）

核心思路 #

要从main_test的相邻时间戳区间中匹配sec_test的时间戳，同时保证大数据量下的查询效率，核心是用窗口函数构建时间区间+建立索引避免全表扫描。

步骤1：创建优化索引 #

由于需要频繁基于时间戳做排序和范围查询，先给两张表的时间戳字段建立索引：

CREATE INDEX idx_main_test_main_ts ON main_test(main_ts);
CREATE INDEX idx_sec_test_sec_ts ON sec_test(sec_ts);

步骤2：实现查询逻辑 #

通过CTE（公共表达式）先为main_test每条记录生成对应的下一个时间戳，形成时间区间后关联sec_test筛选匹配记录：

WITH main_with_next AS (
    SELECT 
        main_id,
        main_ts,
        -- 按时间戳降序取后续记录的时间戳，形成当前区间的下边界
        LEAD(main_ts) OVER (ORDER BY main_ts DESC) AS next_main_ts
    FROM main_test
)
SELECT 
    m.main_id,
    m.main_ts,
    s.sec_ts AS sec2_ts
FROM main_with_next m
LEFT JOIN sec_test s 
    -- 匹配sec_ts落在当前main_ts和下一个main_ts之间的记录
    ON s.sec_ts BETWEEN m.next_main_ts AND m.main_ts
    -- 最后一条记录无后续时间戳，跳过关联
    AND m.next_main_ts IS NOT NULL
ORDER BY m.main_id;

逻辑解释 #

1. 生成时间区间：LEAD(main_ts) OVER (ORDER BY main_ts DESC)会为每条main_test记录获取下一个更小的时间戳，形成[next_main_ts, main_ts]的区间范围。
2. 左连接保留全量记录：用LEFT JOIN确保所有main_test记录都出现在结果中，仅当sec_ts落在对应区间时显示匹配值，否则为NULL。
3. 排序对齐期望结果：最终按main_id排序，输出格式和需求一致。

效率说明 #

• 窗口函数LEAD借助main_ts的索引，可快速完成排序，避免全表排序的性能损耗。
• sec_ts的索引让BETWEEN范围查询能快速定位目标记录，无需遍历整个sec_test表。
• 对于超大规模数据，SQLite会自动优化CTE的执行计划，性能和子查询相当。

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