PostgreSQL：如何使用generate_series()找出列中缺少的数字？

SELECT commandID 
FROM results 
WHERE NOT EXISTS (
    SELECT * 
    FROM generate_serIEs(0,119999) 
    WHERE generate_serIEs = 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服务器更容易优化这些,并避免了NOT IN中出现NulL的混乱问题.

我最初偏爱OUTER JOIN公式,但至少在9.1中我的测试数据NOT EXISTS表格优化到同一计划.

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

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

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限制,直到我感到无聊并取消它.