PostgreSQL:如何使用generate_series()计算列中缺失的数字

Question

SELECT commandid 
FROM results 
WHERE NOT EXISTS (
SELECT * 
FROM generate_series(0,119999) 
WHERE generate_series = results.commandid 
);

我在results中有一列类型为int，但各种测试都失败了，没有添加到表中。我想创建一个查询，返回在results中找不到的commandid的列表。我认为上面的查询可以满足我的要求。然而，如果我使用的范围超出了commandid的预期可能范围(比如负数)，它甚至都不起作用。

Answer 1

给定样本数据：

create table results ( commandid integer primary key);
insert into results (commandid) select * from generate_series(1,1000);
delete from results where random() < 0.20;

这项工作：

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
WHERE NOT EXISTS (SELECT 1 FROM results WHERE commandid = s.i);

这种替代配方也是如此：

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
LEFT OUTER JOIN results ON (results.commandid = s.i) 
WHERE results.commandid IS NULL;

在我的测试中，以上两种情况似乎都会导致相同的查询计划，但您应该使用EXPLAIN ANALYZE与数据库中的数据进行比较，看看哪种是最好的。

解释

请注意，我在一个公式中使用了带有子查询的NOT EXISTS，而在另一个中使用了普通的OUTER JOIN，而不是NOT IN。DB服务器更容易优化这些，并且避免了NOT IN中NULL可能出现的混淆问题。

我最初喜欢OUTER JOIN配方，但至少在9.1中，根据我的测试数据，NOT EXISTS形式优化为相同的计划。

当系列很大时，两者都会比下面的NOT IN配方表现得更好，就像您的情况一样。NOT IN过去要求Pg对正在测试的每个元组的IN列表进行线性搜索，但对查询计划的检查表明，Pg现在可能足够聪明，可以对其进行散列。NOT EXISTS(由查询规划器转换为JOIN)和JOIN工作得更好。

NOT IN公式在存在NULLcommandid的情况下既令人困惑，又可能效率低下：

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
WHERE s.i NOT IN (SELECT commandid FROM results);

所以我会避免它。在1000000行的情况下，另外两行在1.2秒内完成，NOT IN配方运行CPU，直到我感到无聊并取消它。

Answer 2

正如我在评论中提到的，您需要执行与上述查询相反的操作。

SELECT
generate_series
FROM
generate_series(0, 119999)
WHERE
NOT generate_series IN (SELECT commandid FROM results);

此时，您应该会在所选范围内的commandid列中找到不存在的值。

Answer 3

我不是经验丰富的SQL大师，但我喜欢其他解决问题的方法。就在今天，我遇到了类似的问题——在一个字符列中查找未使用的数字。我已经通过使用pl/pgsql解决了我的问题，并且对我的过程的速度非常感兴趣。我用@Craig Ringer的方法生成了一个带有序列列的表，添加了一百万条记录，然后删除了每99条记录。这个程序在搜索丢失的号码时工作大约3秒：

-- creating table
create table results (commandid character(7) primary key);
-- populating table with serial numbers formatted as characters
insert into results (commandid) select cast(num_id as character(7)) from generate_series(1,1000000) as num_id;
-- delete some records
delete from results where cast(commandid as integer) % 99 = 0;
create or replace function unused_numbers()
returns setof integer as
$body$
declare
i integer;
r record;
begin
-- looping trough table with sychronized counter:
i := 1;
for r in
(select distinct cast(commandid as integer) as num_value
from results
order by num_value asc)
loop
if not (i = r.num_value) then
while true loop
return next i;
i = i + 1;
if (i = r.num_value) then
i = i + 1;
exit;
else
continue;
end if;
end loop;
else
i := i + 1;
end if;
end loop;
return;
end;
$body$
language plpgsql volatile
cost 100
rows 1000;
select * from unused_numbers();

也许它对某人有用。

Answer 4

如果您使用AWS红移，您可能最终需要回答这个问题，因为它不支持generate_series。你最终会得到这样的东西：

select 
startpoints.id    gapstart, 
min(endpoints.id) resume 
from (
select id+1 id 
from   yourtable outer_series 
where not exists 
(select null 
from   yourtable inner_series 
where  inner_series.id = outer_series.id + 1
)
order by id
) startpoints,   
yourtable endpoints 
where 
endpoints.id > startpoints.id 
group by 
startpoints.id;