PostgreSQL和顺序数据 [英] PostgreSQL and Sequential Data

问题描述

我有一个数据集包含：

Table { date itemName }

大部分日期是连续的。没有重复的日期[因为它是主键]。

The date for the most part is sequential. There are no duplicates of the date [as it is the primary key].

问题分为多个部分（所有关于使用SQL）：

The question is split up into multiple parts (all with respect to using SQL):

1. 是否可以在表中列出的日期系列中找到缺口？
   例如：日期 1/2 / 09-1 / 3/09 缺失


2. 表中缺少的日期部分，其范围大于n（这是在运行时确定的数字）？例如： n = 2 不返回日期 1/2 / 09-1 / 3/09 5/6 / 09-6 / 1/09 是。

1. Is it possible to find gaps in the date series listed in the table? For example: Dates 1/2/09-1/3/09 are missing
2. Is it possible to find sections of dates that are missing from the table, that has a range greater than n (this is a number determined at run time)? For example: For n = 2 Dates 1/2/09-1/3/09 are not returned but Dates 5/6/09-6/1/09 are.

推荐答案

只需在plsql中或在将检查所有日期的客户端中创建一个函数。像这个伪代码：

Just create a function in plsql or in a client which will be checking all dates. Like this pseudocode:

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个。

It does not have to be very fast as it is maintenance only. There aren't many dates to check - only 365 a year.

这篇关于PostgreSQL和顺序数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！