在基于两个变量的索引的SQLite表中查找间隙

Question

我们在移动应用程序上使用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项)?

Answer 1

您可以使用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

,db&lt的在小提琴