背景(输入)
全球历史气候网络(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],n
ame [varchar],d
ate [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拥有窗口函数。
谢谢!