识别SQLite表中缺少的序列
我有一个包含1000条记录的表。每条记录表示子文件夹中的一个文件以及与该文件相关的一些属性。感兴趣的字段/列如下:
目录路径=包含感兴趣文件的每个子文件夹的名称 Track=文件的序列号(它们应该是连续的,范围从1到任何数字我正在查找与每个__目录路径相关的文件所表示的序列中缺少的数字。
列出序列中每个缺失数字的开始和结束的通用查询如下(归功于:https://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/):
select start, stop from (
select m.id + 1 as start,
(select min(id) - 1 from sequence as x where x.id > m.id) as stop
from sequence as m
left outer join sequence as r on m.id = r.id - 1
where r.id is null
) as x
where stop is not null order by start, stop;
但是,在本例中,我需要对与具有相同__dirPath值的记录相关的每个序列执行相同的操作。假设顺序表除了通用示例中的id字段之外还有一个__dirpath列,人们将如何做到这一点?
下面是一个包含伪数据的表,前述查询适用于此表,而不考虑__dirpath:
drop table if exists sequence;
create table sequence (__dirpath blob, id int not null);
insert into sequence(__dirpath, id) values
("A", 1), ("A",2), ("A", 3), ("A", 4), ("A", 6), ("A", 7), ("A", 8), ("A", 9),
("A", 10), ("A", 15), ("A", 16), ("A", 17), ("A", 18), ("A", 19), ("A", 20);
如果然后运行以下查询,则会得到正确的答案集:
select dir, start, stop from (
select m.id + 1 as start,
(select min(id) - 1 from sequence as x where x.id > m.id) as stop, m.__dirpath as dir
from sequence as m
left outer join sequence as r on m.id = r.id - 1
where r.id is null
) as x
where stop is not null order by dir, start, stop;
结果正确,如下所示:
如果随后将以下记录添加到表中:
insert into sequence(__dirpath, id) values
("B", 1), ("B",4), ("B", 5), ("B", 6), ("B", 7), ("B", 117), ("B", 14), ("B", 9),
("B", 10), ("B", 15), ("B", 16), ("B", 17), ("B", 18), ("B", 19), ("B", 20);
并重新运行上面的左外连接,则结果是没有意义的,因为与__dirPath=";A";和__dirPath=";B";相关的值都在查询中被引用,从而产生:
因此,问题实质上是如何修改查询以仅引用与每个相应的__目录路径条目相关的记录。
解决方案
必须在相关子查询和联接中添加__dirpath
列:
SELECT dir, start, stop
FROM (
SELECT m.id + 1 start,
(SELECT MIN(id) - 1 FROM sequence x WHERE x.__dirpath = m.__dirpath AND x.id > m.id) stop,
m.__dirpath dir
FROM sequence m LEFT JOIN sequence r
ON m.__dirpath = r.__dirpath AND m.id = r.id - 1
WHERE r.id IS NULL
)
WHERE stop IS NOT NULL
ORDER BY dir, start, stop;
另一个具有CTE和窗口函数的解决方案:
WITH cte AS (
SELECT __dirpath, grp, MIN(id) min_id, MAX(id) max_id
FROM (
SELECT *, SUM(flag) OVER (PARTITION BY __dirpath ORDER BY id) grp
FROM (
SELECT *, id - 1 <> LAG(id, 1, id - 1) OVER (PARTITION BY __dirpath ORDER BY id) flag
FROM sequence
)
)
GROUP BY __dirpath, grp
)
SELECT c1.__dirpath,
MAX(c1.max_id) + 1 start,
MIN(c2.min_id) - 1 stop
FROM cte c1 INNER JOIN cte c2
ON c2.__dirpath = c1.__dirpath AND c2.grp = c1.grp + 1
GROUP BY c1.__dirpath, c1.grp
请参阅demo。
相关文章