如何在MM-DD上聚合多年数据，忽略年份

Postgres version 9.4.18， PostGIS Version 2.2.

以下是我正在使用的表格(并且不太可能对表格结构进行重大更改)：

表ltg_data(跨度为1988年至2018年)：

Column   |           Type           | Modifiers 
----------+--------------------------+-----------
intensity | integer                  | not null
time      | timestamp with time zone | not null
lon       | numeric(9,6)             | not null
lat       | numeric(8,6)             | not null
ltg_geom  | geometry(Point,4269)     | 
Indexes:
"ltg_data2_ltg_geom_idx" gist (ltg_geom)
"ltg_data2_time_idx" btree ("time")

ltg_data大小(~800M 行)：

ltg=# select pg_relation_size('ltg_data');
pg_relation_size 
------------------
149729288192

表counties：

Column   |            Type             |                       Modifiers                      
-----------+-----------------------------+--------------------------------- -----------------------
gid        | integer                     | not null default nextval('counties_gid_seq'::regclass)
objectid_1 | integer                     | 
objectid   | integer                     | 
state      | character varying(2)        | 
cwa        | character varying(9)        | 
countyname | character varying(24)       | 
fips       | character varying(5)        | 
time_zone  | character varying(2)        | 
fe_area    | character varying(2)        | 
lon        | double precision            | 
lat        | double precision            | 
the_geom   | geometry(MultiPolygon,4269) | 
Indexes:
"counties_pkey" PRIMARY KEY, btree (gid)
"counties_gix" gist (the_geom)
"county_cwa_idx" btree (cwa)
"countyname_cwa_idx" btree (countyname)

期望的结果：我想要一个时间序列，其中一年中的每一天都有一行，格式为"MM-DD"，忽略年份：01-01、01-02、01-03、...、12-31。以及表中一年中每天ltg_data的行数。我最终也希望一年中的每一天的每一小时都做同样的事情("MM-DD-HH")。

group by语句应该可以做到这一点，但我很难将"大"表与generate_series()生成的日子连接起来。

MM-DD  | total_count   
-------+------------
12-22  |       9
12-23  |       0
12-24  |       0
12-25  |       0
12-26  |      23
12-27  |       0
12-28  |       5
12-29  |       0
12-30  |       0
12-31  |       0

我尝试的一些查询：

SELECT date_trunc('day', d),
count(a.lat) AS strikes
FROM generate_series('2017-01-01', '2018-12-31', interval '1 day') AS d
LEFT JOIN
(SELECT date_trunc('day', TIME) AS day_of_year,
ltg_data.lat
FROM ltg_data
JOIN counties ON ST_contains(counties.the_geom, ltg_data.ltg_geom)
WHERE cwa = 'MFR' ) AS a ON d = day_of_year
GROUP BY d
ORDER BY d ASC;

但这并不能忽视年份。我不应该感到惊讶，因为date_trunc的"日子"仍在考虑我猜的年份。

2017-12-27 00:00:00-08 |       0
2017-12-28 00:00:00-08 |       0
2017-12-29 00:00:00-08 |       0
2017-12-30 00:00:00-08 |       0
2017-12-31 00:00:00-08 |       0
2018-01-01 00:00:00-08 |       0
2018-01-02 00:00:00-08 |       12
2018-01-03 00:00:00-08 |       0

在这个查询中，我尝试将数据从generate_series()转换为text，以text格式连接到ltg_data表。表示数据类型不匹配。我也尝试过extract，因为这可以提供"doy"和"hour"，这将起作用，但我似乎也无法匹配该查询中的数据类型。很难使"generate_series"成为双重精度。

SELECT to_char(d, 'MM-DD') AS DAY,
count(a.lat) AS strikes
FROM
(SELECT generate_series('2017-01-01', '2018-12-31', interval '1 day') AS d) 
AS f
LEFT JOIN
(SELECT to_char(TIME, 'MM-DD') AS day_of_year,
ltg_data.lat
FROM ltg_data
JOIN counties ON ST_contains(counties.the_geom, ltg_data.ltg_geom)
WHERE cwa = 'MFR' ) AS a ON f = day_of_year
GROUP BY d
ORDER BY d ASC;

结果：

ERROR:  operator does not exist: record = text
LINE 4: ON f = day_of_year group by d order by d asc;
^
HINT:  No operator matches the given name and argument type(s). You might 
need to add explicit type casts.

结论：我的目标是获得跨越多年的每日和每小时总数，但按"MM-DD"和"MM-DD-HH"(忽略年份)分组，查询结果显示所有天数/小时，即使它们为零。

稍后我还会尝试找到天数和小时的平均值和百分位数，所以如果你对此有任何建议，我都会听到。但我目前的问题集中在获得总数的完整结果上。

基本上，为了切断年份，to_char(time, 'MMDD')就像你已经尝试过一样。您只是忘记在加入之前将其应用于使用generate_series()生成的时间戳。以及其他一些小细节。

为了简化和方便性能和方便，我建议使用这个简单的函数来计算给定timestamp的模式"MMDD"的integer。

CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS
'SELECT (EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int';

我一开始使用to_char(time, 'MMDD')，但切换到上面的表达式，结果证明它在各种测试中是最快的。

db<>小提琴在这里

它可以在表达式索引中使用，因为它是在IMMUTABLE定义的。它仍然允许函数内联，因为它只使用EXTRACT (xyz FROM date)- 这是在内部date_part(text, date)IMMUTABLE函数实现的。(请注意，datepart(text, timestamptz)仅STABLE)。

然后这种查询完成这项工作：

SELECT d.mmdd, COALESCE(ct.ct, 0) AS total_count
FROM  (
SELECT f_mmdd(d::date) AS mmdd  -- ignoring the year
FROM   generate_series(timestamp '2018-01-01'  -- any dummy year
, timestamp '2018-12-31'
, interval '1 day') d
) d
LEFT  JOIN (
SELECT f_mmdd(time::date) AS mmdd, count(*) AS ct
FROM   counties c
JOIN   ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
WHERE  cwa = 'MFR'
GROUP  BY 1
) ct USING (mmdd)
ORDER  BY 1;

由于time(我会使用不同的列名)是数据类型timestamptz因此转换time::date取决于当前会话的时区设置。("天"由您所在的时区定义。要获得不可变(但速度较慢)的结果，请使用时区名称如下的AT TIME ZONE构造：

SELECT f_mmdd((time AT TIME ZONE 'Europe/Vienna')::date) ...

详：

在 Rails 和 PostgreSQL 中完全忽略时区

mmdd您喜欢的任何显示方式设置格式。

对于此特定查询，转换为integer是可选的。但是，由于您计划执行各种查询，因此最终需要表达式的索引：

CREATE INDEX ltg_data_mmdd_idx ON event(f_mmdd(time));

(此查询不需要。
为此，integer速度要快一些。~~为此你需要(否则可选的)函数包装器，因为to_char()只是STABLE定义的，但我们需要索引IMMUTABLE。~~更新后的表达式(EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int是IMMUTABLE，但函数包装器仍然很方便。

相关内容

最新更新

热门标签：