标记非连续日期范围

Question

背景（输入）

全球历史气候网络(Global Historical Climatology Network)已标记其收集的天气测量数据中的无效或错误数据。在删除这些元素后，许多数据不再具有连续日期部分。数据类似于：

"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

相关想法

生成一个填充连续日期的表格，并将其与测量数据日期进行比较。

• 在mysql或perl端最简单的方式是什么来填充sql结果中的空日期？
• 如何在连续范围上进行分组
• 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

然而，在真实数据中，有数百个站点，日期可以追溯到19世纪中期，因此所有站点的所有日期的笛卡尔积过于庞大。这种方法可能有效，但需要足够的时间... 必须有更快的方法。

尝试 #4

PostgreSQL拥有窗口函数。

如何使用Postgres中的窗口函数选择特定的更改

谢谢！

- Dave Jarvis

---

这是在说您想要识别有间隔的月份吗？ - Mike Sherrill 'Cat Recall'

@Catcall：是的。这只是一种复杂的方式来表达这个意思。考虑到每个月的天数、闰年等因素。 - Dave Jarvis

+1 对于这些编辑，让问题更加清晰明了。 - Mike Sherrill 'Cat Recall'

Answer 1

generate_series()

PostgreSQL的generate_series()函数可以创建一个包含连续日期列表的视图：

with calendar as (
    select ((select min(date) from test)::date + (n || ' days')::interval)::date cal_date
    from generate_series(0, (select max(date) - min(date) from test)) n
)
select cal_date
from calendar c
left join test t on t.date = c.cal_date
where t.date is null;

表达式select max(date) - min(date) from test可能会多算一天。

按月计算天数

识别无效月份的一种方法是创建两个视图。第一个视图计算每个站点每个月应该产生的日读数的数量。(注意，climate.calendar被翻译成climate_calendar。) 第二个返回每个站点实际每个月产生的日读数。

每个站点每月最多天数

此视图将返回每个站点每月的实际天数。(例如，二月份总是有28或29天。)

create view count_max_station_calendar_days as 
with calendar as (
    select ((select min(d) from climate_calendar)::date + (n || ' days')::interval)::date cal_date
    from generate_series(0, (select max(d) - min(d) from climate_calendar)) n
)
select n, extract(year from cal_date) yr, extract(month from cal_date) mo, count(*) num_days
from stations cross join calendar
group by n, yr, mo
order by n, yr, mo

每个站点每月实际天数

返回的总天数将少于计数。 （例如，一月份总天数始终为31天或更少。）

create view count_actual_station_calendar_days as
select n, extract(year from d) yr, extract(month from d) mo, count(*) num_days
from climate_calendar
group by n, yr, mo
order by n, yr, mo;

在生产环境中删除ORDER BY子句（在开发中很有帮助）。

比较视图

将这两个视图连接起来，创建一个新的视图来识别需要标记的站点和月份：

create view invalid_station_months as 
select m.n, m.yr, m.mo, m.num_days - a.num_days num_days_missing
from count_max_station_calendar_days m
inner join count_actual_station_calendar_days a
       on (m.n = a.n and m.yr = a.yr and m.mo = a.mo and m.num_days <> a.num_days)

n   yr    mo  num_days_missing
--
A   1982  1   1
E   2007  3   1

num_days_missing 列并非必需，但它是有用的。

需要更新的行如下：

select cc.* 
from climate_calendar cc
inner join invalid_station_months im 
        on (cc.n = im.n and 
            extract(year from cc.d) = im.yr and
            extract(month from cc.d) = im.mo)
where valid = true

更新数据库

要更新它们，id键很方便。

update climate_calendar
set valid = false
where id in (
    select id
    from climate_calendar cc
    inner join invalid_station_months im 
        on (cc.n = im.n and 
            extract(year from cc.d) = im.yr and
            extract(month from cc.d) = im.mo)
    where valid = true
);

- Mike Sherrill 'Cat Recall'

---

@Dave Jarvis：我根据你的问题编辑添加了很多内容。它包括三个有用的视图和一个可工作的UPDATE语句。 - Mike Sherrill 'Cat Recall'

计算天数是很有见地的。再次感谢您。 - Dave Jarvis

查询成功返回：共影响498215579行，执行时间为173600096毫秒。;-) 需要96 GB的临时空间。 - Dave Jarvis

@Dave Jarvis：你需要休息一下，对吧？你是在一个事务中完成的吗？ - Mike Sherrill 'Cat Recall'

Answer 2

这里是一种可能的方法，假设你有一个名为is_contiguous的BOOLEAN字段。根据需要进行修改：

UPDATE measurement
SET is_contiguous = FALSE
WHERE NOT EXISTS (
  SELECT 1
    FROM measurement B
   WHERE measurement.taken - 1 = B.taken
);

编辑：

我想我误解了您的要求。我以为您想标记不连续的单个日期。但显然，如果缺少任何天数，您想将整个月份的日期标记为不连续。

编辑2：

这是我原来（不正确）查询的修改版本，它选择缺少任何天数的不同月份：

UPDATE measurement
SET is_contiguous = FALSE
WHERE date_trunc('month', taken) IN (
    SELECT DISTINCT date_trunc('month', taken)
    FROM measurement A
    WHERE NOT EXISTS (
        SELECT 1
        FROM measurement B
        WHERE A.taken - 1 = B.taken
   )
);

- Sam Choukri

Answer 3

假设每天只有一行数据，这将返回所有行数不等于该月天数的月份。

SELECT station_id, DATE_TRUNC('month', d)
FROM climate.calendar
GROUP BY station_id, DATE_TRUNC('month', d)
HAVING COUNT(*) <> 
  DATE_PART('month',
            DATE_TRUNC('month', d) + INTERVAL '1 month' - INTERVAL '1 day')

- Andriy M

---

谢谢。不幸的是，这会每个月返回每年的数据，因为它没有考虑每个站点的日期分布。 - Dave Jarvis