SELECT commandid
FROM results
WHERE NOT EXISTS (
SELECT *
FROM generate_series(0,119999)
WHERE generate_series = results.commandid
);
我有一列results
类型的int
但各种测试失败,并没有添加到表。 我想创建一个返回列表的查询commandid
未在发现results
。 我想上面的查询会做什么,我想要的。 然而,它甚至不工作,如果我用一个范围是预期可能的范围之外commandid
(如负数)。
给定的样本数据:
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 IN
我用NOT EXISTS
在一个配方中的子查询,和普通OUTER JOIN
中的其他。 这对DB服务器更容易优化这些,它避免了能与出现的混乱问题NULL
S IN NOT IN
。
我最初的青睐OUTER JOIN
制定,但至少在9.1与我的测试数据NOT EXISTS
形式优化,以同样的计划。
双方将进行好过NOT IN
制定以下时,该系列产品是大的,因为你的情况。 NOT IN
用于需要PG做的线性搜索IN
列表中每个元组被测试,但查询计划的检查表明,PG可足够聪明,现在凑了。 该NOT EXISTS
(转化为JOIN
由查询规划)和JOIN
更好的工作。
在NOT IN
制剂是既令人困惑在NULL的存在commandid
S和可能是低效的:
SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
WHERE s.i NOT IN (SELECT commandid FROM results);
所以我会避开它。 随着1,000,000行其他两个1.2秒内完成和NOT IN
配方跑了CPU绑定的,直到我觉得无聊,并取消它。
As I mentioned in the comment, you need to do the reverse of the above query.
SELECT
generate_series
FROM
generate_series(0, 119999)
WHERE
NOT generate_series IN (SELECT commandid FROM results);
At that point, you should find values that do not exist within the commandid
column within the selected range.
我不这么有经验的SQL大师,但我喜欢其他的方式来解决问题。 就在今天,我有类似的问题 - 在一个字符列找到未使用的号码。 我已经使用PL / pgSQL的解决我的问题,是在什么将是我的程序的速度非常感兴趣。 我用@Craig林格的方式来产生串行列的表格,添加一百万条记录,然后删除每第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();
也许这将是对别人有用。
如果你在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;
文章来源: PostgreSQL: How to figure out missing numbers in a column using generate_series()?