使用基於兩個變量的索引查找 SQLite 表中的差距

用戶426132

我們在移動應用程序上使用 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 個條目)?

編號'7238

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

數據庫<>小提琴

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

查找MySQL中顺序值之间的差距

使用oracle查询用平滑平均值填充表中缺失数据的差距

查找日期范围中的差距-TSQL

如何在Oracle表中查找月份差距?

使用SQLite中的子查询更新表

在SQLite中动态使用IN()

通过索引明智的问题从sqlite表中检索数据

如何在Sqlite表中查找子字符串?

如何使用SQLite在Qt中创建表?

查找表中的列数 SQLite3

使用 COUNT 在 sqlite3 中查找表中的行数

SQLITE3:在多个表中查找 ID

sqlite,在大型数据库中重新索引表的方法

sqlite中的复合索引

使用pandas计算sqlite表中的值

如何使用 foreach 在 C# 中使用條件在數據表中查找一個變量?

基於 R 中的多個變量創建人口普查表

基於Python中的索引合併兩個嵌套列表

使用 purrr、broom 和 dplyr1.0.0 中的幾個不同預測變量將 glm 應用於相同的結果變量

Sass中兩個變量的比例

將結果存儲在基於另一個變量 (Pandas) 命名的新列中

基於兩個因素的列中的平均值

R - 如何使用來自兩個數據幀的數據來計算 R 中的新變量?

在python中,我們可以使用Sqlite3將表名設置為變量嗎?

使用 awk 解析文本文件並存儲在兩個變量中

比較java中枚舉的兩個變量

使用兩個數組的 Pandas 查找表(第一個數組的索引,第二個數組的列)

SQLite - 基於部分字符串匹配加入 2 個表,排除某些行

從 ID 等於設置變量的表打印 Python SQLite