如何高效查找SQLite中基于双变量索引的表计数器间隙

前置要求 #

• SQLite版本 >= 3.25.0（目前主流移动端系统自带的SQLite均满足该版本要求）
• 提前为表创建联合索引 (X_VAR, Z_VAR, COUNTER)，这是查询高效的核心前提，创建索引的语句如下：

CREATE INDEX idx_xz_counter ON your_table_name(X_VAR, Z_VAR, COUNTER);

核心查询SQL #

该方案基于SQLite原生窗口函数实现，所有计算在数据库层完成，无需应用层遍历全量数据，10万级数据查询耗时为毫秒级：

SELECT 
  X_VAR,
  Z_VAR,
  COUNTER + 1 AS gap_start,
  next_counter - 1 AS gap_end,
  (next_counter - COUNTER - 1) AS missing_count
FROM (
  SELECT 
    X_VAR,
    Z_VAR,
    COUNTER,
    LEAD(COUNTER, 1) OVER (PARTITION BY X_VAR, Z_VAR ORDER BY COUNTER) AS next_counter
  FROM your_table_name
) t
WHERE next_counter IS NOT NULL 
  AND next_counter - COUNTER > 1;

查询效果说明 #

针对你给出的示例数据，上述SQL的输出结果如下：

完全匹配你标注的间隙起止位置。

可选扩展：统计分组起始间隙 #

如果需要额外统计分组最小COUNTER大于1的起始间隙（比如某分组第一条COUNTER为3，缺失1、2），可以使用扩展版本的SQL：

-- 统计COUNTER中间的间隙
SELECT 
  X_VAR,
  Z_VAR,
  COUNTER + 1 AS gap_start,
  next_counter - 1 AS gap_end,
  (next_counter - COUNTER - 1) AS missing_count
FROM (
  SELECT 
    X_VAR,
    Z_VAR,
    COUNTER,
    LEAD(COUNTER, 1) OVER (PARTITION BY X_VAR, Z_VAR ORDER BY COUNTER) AS next_counter
  FROM your_table_name
) t
WHERE next_counter IS NOT NULL 
  AND next_counter - COUNTER > 1

UNION ALL

-- 统计分组开头缺失的间隙
SELECT 
  X_VAR,
  Z_VAR,
  1 AS gap_start,
  MIN(COUNTER) - 1 AS gap_end,
  MIN(COUNTER) - 1 AS missing_count
FROM your_table_name
GROUP BY X_VAR, Z_VAR
HAVING MIN(COUNTER) > 1;

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