Find gap in SQLite table with index based on two variables-CodePudding

We are using an SQLite database on a mobile app. In one of the tables we have an auto increasing counter based on two variables. We have detected a problem where a gap appears in the counter and we need to find the start/end of the gapped indexes.

So the table looks like this (example)

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

So for any combination of X_VAR and Z_VAR that occurs in the table we would need to determine if the counter contains a gap and if so where is the start of the gap (so that we could potentially fill it with dummy entries). Is there an efficient way to detect this using sql without looping through an array of all entries (there may be 30k -100k entries in the table) ?

CodePudding user response：

You can use the LEAD window function:

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

Inserting missing counter values through a recursive query:

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<>fiddle