在 SQLite 中獲取每個組的最後 10 行的有效方法，CTE 可以提供幫助嗎？

考慮有一個具有以下模式的作業執行歷史記錄表：

job_runs
(
   run_id integer not null, -- identifier of the run
   job_id integer not null, -- identifier of the job
   run_number integer not null, -- job run number, run numbers increment for each job
   status text not null, -- status of the run (running, completed, killed, ...)
   primary key (run_id)
   -- ...
)

並且需要status != 'running'為每個作業獲取最後 10 次執行（作業不同job_id）。為此，我編寫了以下查詢：

SELECT
   *
FROM
   job_runs AS JR1
WHERE
   JR1.run_number IN
   (
       SELECT
           JR2.run_number
       FROM
           job_runs AS JR2
       WHERE
           JR2.job_id = JR1.job_id
           AND
           JR2.status != 'running'
       ORDER BY
           JR2.run_number
       DESC
       LIMIT
           10
   )

它可以滿足我的需要，但是即使表的job_id和run_num欄位上有一個多欄位索引，job_runs查詢也很慢，因為它會掃描 job_runs 表，並且它的每一行都執行子查詢。索引每次都有助於子查詢快速執行，但嵌套查詢掃描整個表的事實會降低性能。那麼如何調整查詢的性能呢？

一些想法：

• 作業的數量（不同job_id的 s）很小，如果 SQLite 中有一個 FOR 循環，則很容易循環所有不同job_id的 s 並執行傳遞作業 id 的子查詢，而不是JR1.job_idUNION 所有結果。
• 我還嘗試使用遞歸 CTE 構造查詢，但沒有成功。

重要的：

請不要建議在我的應用程序的原始碼中執行循環。我需要純 SQL 解決方案。

由於 distinct 的數量job_id很少，您可以嘗試這種方式來編寫此類查詢。我經常在沒有視窗函式的 MySQL 中使用 - 你甚至不能使用a IN (SELECT ... LIMIT x). 嘗試使用以下索引(job_id, run_number, status)：

SELECT
   jr.*
FROM
   ( SELECT DISTINCT job_id
     FROM job_runs
   ) AS jrd
 JOIN
   job_runs AS jr
   ON  jr.job_id = jrd.job_id
   AND jr.status <> 'running'
   AND jr.run_number >= COALESCE(
       (
           SELECT jri.run_number
           FROM job_runs AS jri
           WHERE jri.job_id = jrd.job_id
             AND jri.status <> 'running'
           ORDER BY jri.run_number DESC
           LIMIT 1 OFFSET 9
       ), -2147483647) 
   ;

這（具有相同的索引）：

SELECT
   jr.*
FROM
   ( SELECT DISTINCT job_id
     FROM job_runs
   ) AS jrd
 JOIN
   job_runs AS jr
   ON  jr.run_id IN
       (
           SELECT jri.run_id
           FROM job_runs AS jri
           WHERE jri.job_id = jrd.job_id
             AND jri.status <> 'running'
           ORDER BY jri.run_number DESC
           LIMIT 10
       )
   ;

引用自：https://dba.stackexchange.com/questions/119900