如何编写高效SqlLite查询获取最新N个作业的所有运行记录条目

前提说明 #

SQLite 3.25及以上版本支持窗口函数，可直接使用以下语句实现需求，使用时将语句中的your_table_name替换为实际表名即可。

具体查询语句（以N=3为例） #

WITH job_first_record AS (
    -- 给每个Job的所有记录按时间升序排序，标记出每个Job的第一条记录
    SELECT 
        JobID,
        UnixTime AS first_time,
        ROW_NUMBER() OVER (PARTITION BY JobID ORDER BY UnixTime ASC) AS rn
    FROM your_table_name
),
latest_n_jobs AS (
    -- 筛选出所有Job的第一条记录，按时间倒序取最新的N个JobID
    SELECT JobID
    FROM job_first_record
    WHERE rn = 1
    ORDER BY first_time DESC
    LIMIT 3 -- 此处替换为你需要的N值
)
-- 取出这N个Job的所有关联条目，可按需求调整排序规则
SELECT t.*
FROM your_table_name t
INNER JOIN latest_n_jobs j ON t.JobID = j.JobID
ORDER BY t.UnixTime ASC;

语句说明 #

1. 第一个CTE job_first_record 使用PARTITION BY JobID对作业ID分区，按UnixTime升序排序后，每个作业的第一条记录行号rn为1
2. 第二个CTE latest_n_jobs 筛选出所有作业的首条记录，按首条记录的时间倒序排序后取前N个，即为所需的最新N个作业
3. 最终关联原表，返回这些作业的全部条目，结果默认按时间升序排列，可根据需要调整排序规则

性能优化建议 #

• 建议为表创建联合索引 CREATE INDEX idx_job_time ON your_table_name(JobID, UnixTime ASC)，可大幅加速窗口函数的分区排序操作，适配持续写入的并发场景
• 如果数据量极大，可定期归档已结束的历史作业数据，减少查询扫描范围

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