Отметить несмежные диапазоны дат

Фон (ввод)

Глобальная сеть исторической климатологии отметила неверные или ошибочные данные в своем сборе данных о погоде. После удаления этих элементов остаются ряды данных, в которых больше нет смежных датированных разделов. Данные выглядят следующим образом:

"2007-12-01";14 -- Start of December
"2007-12-29";8
"2007-12-30";11
"2007-12-31";7
"2008-01-01";8 -- Start of January
"2008-01-02";12
"2008-01-29";0
"2008-01-31";7
"2008-02-01";4 -- Start of February
... entire month is complete ...
"2008-02-29";12
"2008-03-01";14  -- Start of March
"2008-03-02";17
"2008-03-05";17

Проблема (выход)

Хотя можно экстраполировать отсутствующие данные (например, путем усреднения за другие годы) для получения непрерывных диапазонов, чтобы упростить систему, я хочу пометить несмежные сегменты в зависимости от того, существует ли непрерывный диапазон дат для заполнения месяца:

D;"2007-12-01";14 -- Start of December
D;"2007-12-29";8
D;"2007-12-30";11
D;"2007-12-31";7
D;"2008-01-01";8 -- Start of January
D;"2008-01-02";12
D;"2008-01-29";0
D;"2008-01-31";7
"2008-02-01";4 -- Start of February
... entire month is complete ...
"2008-02-29";12
D;"2008-03-01";14  -- Start of March
D;"2008-03-02";17
D;"2008-03-05";17

Некоторые измерения были сделаны в 1843 году.

Вопрос

Для всех метеостанций, как бы вы отметили все дни в месяцах, в которых отсутствует один или несколько дней?

Исходный код

Код для выбора данных выглядит примерно так:

select
  m.id,
  m.taken,
  m.station_id,
  m.amount
from
  climate.measurement

Связанные идеи

Создайте таблицу, заполненную смежными датами, и сравните их с датами измеренных данных.

• Что такое самый простой способ дополнить пустые даты в результатах sql (на конце mysql или perl)?
• Как группировать по непрерывным диапазонам
• http://msdn.microsoft.com/en-us/library/aa175780%28v=sql.80%29.aspx

Обновлять

Проблема может быть воссоздана с помощью SQL в этом разделе.

Таблица

Таблица создается следующим образом:

CREATE TABLE climate.calendar
(
  id serial NOT NULL,
  n character varying(2) NOT NULL,
  d date NOT NULL,
  "valid" boolean NOT NULL DEFAULT true,
  CONSTRAINT calendar_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

Генерировать данные

Следующий SQL вставляет данные в таблицу (id [int], name [varchar], date [date], valid [boolean]):

insert into climate.calendar (n, d) 
    select 'A', (date('1982-01-1') + (n || ' days')::interval)::date cal_date
    from generate_series(0, date('2011-04-9') - date('1982-01-1') ) n
insert into climate.calendar (n, d) 
    select 'B', (date('1982-01-1') + (n || ' days')::interval)::date cal_date
    from generate_series(0, date('2011-04-9') - date('1982-01-1') ) n
insert into climate.calendar (n, d) 
    select 'C', (date('1982-01-1') + (n || ' days')::interval)::date cal_date
    from generate_series(0, date('2011-04-9') - date('1982-01-1') ) n
insert into climate.calendar (n, d) 
    select 'D', (date('1982-01-1') + (n || ' days')::interval)::date cal_date
    from generate_series(0, date('2011-04-9') - date('1982-01-1') ) n
insert into climate.calendar (n, d) 
    select 'E', (date('1982-01-1') + (n || ' days')::interval)::date cal_date
    from generate_series(0, date('2011-04-9') - date('1982-01-1') ) n
insert into climate.calendar (n, d) 
    select 'F', (date('1982-01-1') + (n || ' days')::interval)::date cal_date
    from generate_series(0, date('2011-04-9') - date('1982-01-1') ) n

Значения с 'A' по 'F' представляют названия метеостанций, которые проводили измерения в определенный день.

Удалить случайные строки

Удалите несколько строк следующим образом:

delete from climate.calendar where id in (select id from climate.calendar order by random() limit 5000);

Попытка №1

Следующее не переключает флаг valid на false для всех дней месяца, когда в месяце отсутствует один или несколько дней:

UPDATE climate.calendar
SET valid = false
WHERE date_trunc('month', d) IN (
    SELECT DISTINCT date_trunc('month', d)
    FROM climate.calendar A
    WHERE NOT EXISTS (
        SELECT 1
        FROM climate.calendar B
        WHERE A.d - 1 = B.d
   )
);

Попытка №2

Следующий SQL возвращает пустой набор результатов:

with gen_calendar as (
    select (date('1982-01-1') + (n || ' days')::interval)::date cal_date
    from generate_series(0, date('2011-04-9') - date('1982-01-1') ) n
)
select gc.cal_date
from gen_calendar gc
left join climate.calendar c on c.d = gc.cal_date
where c.d is null;

Попытка №3

Следующий SQL генерирует все возможные комбинации названий станций и дат:

select
  distinct( cc.n ), t.d
from
  climate.calendar cc,
  (
    select (date('1982-01-1') + (n || ' days')::interval)::date d
    from generate_series(0, date('2011-04-9') - date('1982-01-1') ) n
  ) t
order by
  cc.n

Однако в реальных данных есть несколько сотен станций, а даты восходят к середине 1800-х годов, поэтому декартова датировка всех станций слишком велика. Такой подход мог бы сработать, если бы достаточно времени ... Должен быть более быстрый способ.

Попытка №4

PostgreSQL имеет оконные функции.

Как выбрать определенные изменения с помощью оконных функций в postgres