PostgreSQL и последовательные данные

У меня есть набор данных, который содержит:

Table { date itemName }

Дата по большей части последовательная. Нет дубликатов даты [поскольку это первичный ключ].

Вопрос разбит на несколько частей (все в отношении использования SQL):

  1. Можно ли найти пробелы в рядах дат, перечисленных в таблице? Например: Даты 1/2/09-1/3/09 отсутствуют.
  2. Можно ли найти участки дат, отсутствующие в таблице, диапазон которых больше n (это число определяется во время выполнения)? Например: для n = 2 даты 1/2/09-1/3/09 не возвращаются, а даты 5/6/09-6/1/09 возвращаются.

postgresql sql database
person monksy    schedule 05.10.2009    source источник
comment
Мой подход заключался бы в постобработке результатов. archives/010523.html .. но если это возможно внутри запроса и не сильно забивает систему, было бы здорово :)   -  person warren    schedule 05.10.2009
comment
Это не живой запрос, который будет часто использоваться, он просто для обслуживания время от времени.   -  person monksy    schedule 05.10.2009

Ответы (3)


arrow_upward
1
arrow_downward

Просто создайте функцию в plsql или в клиенте, которая будет проверять все даты. Как этот псевдокод:

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 в год.

person Tometzky    schedule 05.10.2009
comment
Если у вас нет доступных оконных функций SQL, этот подход на самом деле является самым быстрым из возможных для большого набора данных, поскольку он делает только один проход по таблице. Одна вещь, которую вам нужно посмотреть, это то, что SELECT получает ORDER BY, поэтому строки отображаются в отсортированном порядке. И вы должны использовать SELECT min(date),max(date) из таблицы, чтобы получить пределы цикла - предполагать, что все заканчивается на «сегодня», не лучшая идея. PostgreSQL имеет множество доступных языков программирования, которые вы можете запускать внутри базы данных, PL/pgSQL является стандартным. - person Greg Smith; 06.10.2009

arrow_upward
10
arrow_downward

Если вы можете использовать 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;
person Ants Aasma    schedule 05.10.2009
comment
Это как раз те проблемы, которые оконные функции намерены эффективно решать, и нет другого решения, основанного только на SQL, которое могло бы работать так быстро, как это. - person Greg Smith; 06.10.2009
comment
К сожалению, у меня нет версии 8.4. У меня 8.1. Хотел бы я получить кредит на этот ответ. Мне это и вправду нравится. - person monksy; 23.10.2009

arrow_upward
1
arrow_downward

После некоторого тестирования я пришел к следующему оператору 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

вы можете использовать длину INTERVAL в предложении WHERE подзапроса для фильтрации пробелов по крайней мере этого размера.

Надеюсь, это поможет.

person Frank Bollack    schedule 05.10.2009
comment
Время выполнения этого запроса пропорционально квадрату размера таблицы, поскольку и внешний подзапрос SELECT, и внутренний подзапрос EXISTS выполняют действия, время выполнения которых пропорционально размеру таблицы. Сначала это может показаться разумным, но в конечном итоге это станет очень дорого. К сожалению, любое другое решение, которое вы делаете на чистом SQL, будет страдать от той же проблемы, потому что SQL не имеет памяти строк. Для таблицы с n строками вам нужно каким-то образом выполнить соединение n X n, чтобы решить проблему такого типа. Когда они доступны, оконные функции лучше всего подходят для этого. - person Greg Smith; 06.10.2009
comment
@Greg: Спасибо за анализ. Вы правы, это не самое быстрое решение, если под рукой есть функции Windows. Но PostgreSQL 8.4 — это довольно свежий выпуск, поэтому есть вероятность, что OP использует более старую версию. Также посмотрите комментарий ОП к его вопросу о его требованиях к производительности во время выполнения. - person Frank Bollack; 06.10.2009