我們在移動應用程式上使用 SQLite 資料庫。在其中一張表中，我們有一個基于兩個變數的自動遞增計數器。我們檢測到計數器中出現間隙的問題，我們需要找到間隙索引的開始/結束。
所以表格看起來像這樣（示例）
X_VAR Z_VAR COUNTER
AA BB 1
AA BB 2
AA BB 3
AA BB 4
AA BB 8 <<<< gap 4-8
CC DD 1
CC DD 2
CC DD 3
CC DD 4
CC DD 7 <<<< gap 4-7
因此，對于表中出現的 X_VAR 和 Z_VAR 的任何組合，我們需要確定計數器是否包含間隙，如果是，則間隙的起點是哪里（以便我們可能用虛擬條目填充它）。有沒有一種有效的方法來使用 sql 檢測這個而不遍歷所有條目的陣列（表中可能有 30k -100k 個條目）？
uj5u.com熱心網友回復：
您可以使用LEAD視窗函式：
WITH gaps AS (
SELECT *, LEAD(counter) OVER (PARTITION BY x_var, z_var ORDER BY counter) as next_counter
FROM mytable
)
SELECT *
FROM gaps
WHERE counter 1 <> next_counter
通過遞回查詢插入缺失的計數器值：
INSERT INTO mytable (x_var, z_var, counter)
WITH gaps AS (
SELECT *, LEAD(counter) OVER (PARTITION BY x_var, z_var ORDER BY counter) as next_counter
FROM mytable
), cte AS (
SELECT x_var, z_var, counter 1 AS counter, next_counter - 1 AS end_counter
FROM gaps
WHERE counter 1 <> next_counter
UNION ALL
SELECT x_var, z_var, counter 1, end_counter
FROM cte
WHERE counter < end_counter
)
SELECT x_var, z_var, counter
FROM cte
資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/337751.html