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

概述SELECT commandid FROM results WHERE NOT EXISTS ( SELECT * FROM generate_series(0,119999) WHERE generate_series = results.commandid ); 我在类型为int的结果中有一列,但各种测试失败并且没有添加到表中.我想创建一个查询,返回在

SELECT commandID FROM results WHERE NOT EXISTS (    SELECT *     FROM generate_serIEs(0,119999)     WHERE generate_serIEs = results.commandID     );

我在类型为int的结果中有一列,但各种测试失败并且没有添加到表中.我想创建一个查询,返回在结果中找不到的commandID列表.我认为上面的查询会做我想要的.但是,如果我使用超出预期可能的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_cmdFROM generate_serIEs(0,1000) s(i)WHERE NOT EXISTS (SELECT 1 FROM results WHERE commandID = s.i);

这个替代配方也是如此：

SELECT s.i AS missing_cmdFROM 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_cmdFROM generate_serIEs(0,1000) s(i)WHERE s.i NOT IN (SELECT commandID FROM results);

所以我会避免它.拥有1,000,000行,另外两行在1.2秒内完成,NOT IN配方运行cpu限制,直到我感到无聊并取消它.

总结

以上是内存溢出为你收集整理的PostgreSQL：如何使用generate_series()找出列中缺少的数字？全部内容，希望文章能够帮你解决PostgreSQL：如何使用generate_series()找出列中缺少的数字？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。