PostgreSQL和序列数据

Question

我有一个包含以下内容的数据集：

Table { date itemName }

这个日期大部分是顺序排列的。由于它是主键，因此没有重复的日期。

这个问题分成多个部分（都涉及使用SQL）：

1. 是否可以找到表中列出的日期系列中的间隙？ 例如：日期 1/2/09-1/3/09 缺失。
2. 是否可以找到在表中缺失的日期段，其范围大于n（n是运行时确定的数字）？例如：对于 n=2，日期 1/2/09-1/3/09 不会被返回，但日期 5/6/09-6/1/09 会被返回。

- monksy

---

我的方法是对结果进行后处理.. http://jeremy.zawodny.com/blog/archives/010523.html ..但如果可以在查询中实现，并且不会对系统造成太多压力，那就太好了 :) - warren

这不是一个经常使用的实时查询，只是偶尔用于维护。 - monksy

Answer 1

如果您可以使用PostgreSQL 8.4，那么窗口函数会有所帮助：

SELECT *
    FROM (SELECT itemName, date, date - lag(date) OVER w AS gap
              FROM someTable WINDOW w AS (ORDER BY date)
         ) AS pairs
    WHERE pairs.gap > '1 day'::interval;

- Ants Aasma

---

这正是窗口函数旨在高效解决的问题类型，没有其他仅限于SQL的解决方案可以像这样快速运行。 - Greg Smith

很遗憾，我没有8.4版本，只有8.1版本。我希望我能得到这个答案的认可。我真的很喜欢它。 - monksy

Answer 2

只需在PL/SQL或客户端中创建一个函数，该函数将检查所有日期。就像这样的伪代码：

date checked_date = 2000-01-01;
int unchecked_section = 0;
while ( checked_date <= today() ) {
  if (! sql(select itemName from Table where itemName=checked_date)) {
    unchecked_section++;
  } else {
    if ( unchecked_section>=n ) {
      print checked_date-unchecked_section, checked_date
    }
    unchecked_section = 0;
  }
  checked_date++;
}
if ( unchecked_section ) {
  print checked_date-unchecked_section, checked_date
}

由于这只是维护工作，所以速度不必太快。每年需要检查的日期也不多，仅有365天。

- Tometzky

---

如果您没有可用的SQL窗口函数，则此方法实际上是在大型数据集上可能最快的方法，因为它只对表进行一次遍历。您需要注意的一件事是SELECT获取ORDER BY，以便行按排序顺序显示。并且您应该使用“SELECT min（date），max（date）from table”来获取循环限制-假设事情在“今天”结束并不是最好的想法。PostgreSQL有许多可用的编程语言，您可以在数据库内运行，PL / pgSQL是标准语言。 - Greg Smith

Answer 3

经过一些测试，我得出了以下的SQL语句：

SELECT date, itemName
  FROM "Table" as t1
  WHERE NOT EXISTS (
     SELECT date 
     FROM "Table" as t2 
     WHERE t2.date = (t1.date - INTERVAL '1 day')
  )
  ORDER BY date
  OFFSET 1  -- this will skip the first element

这将获取所有没有直接后继的行。

如果您修改语句为：

SELECT date, itemName
  FROM "Table" as t1
  WHERE NOT EXISTS (
    SELECT date 
    FROM "Table" as t2 
    WHERE (t2.date >= (t1.date - INTERVAL '2 day'))
    AND (t2.date < t1.date)
  )
  ORDER BY date
  OFFSET 1

你可以在子查询的WHERE子句中使用INTERVAL长度来过滤至少具有该大小间隔的数据。

希望这能帮到你。

- Frank Bollack

---

这个的运行时间与表大小的平方成正比，因为外部SELECT和内部EXISTS子查询都在执行其运行时间与表大小成正比的操作。 虽然一开始看起来似乎合理，但最终会变得非常昂贵。不幸的是，在纯SQL中进行的任何其他解决方案都将遭受相同的问题，因为SQL没有行内存。 对于一个n行的表，您必须以某种方式执行n X n join来解决此类问题。 在可用时，窗口函数是解决此类问题的最佳方法。 - Greg Smith

@Greg：感谢你的分析。你说得对，如果有Windows函数在手，这并不是最快的解决方案。但PostgreSQL 8.4是一个相当新的版本，所以有可能OP正在使用旧版本。此外，请查看OP在他的问题中对运行时性能要求的评论。 - Frank Bollack