如何在MM-DD上汇总来自多年的数据，而忽略年份

admin

如何在MM-DD上汇总来自多年的数据，而忽略年份

sql

Postgres版本9.4.18，PostGIS版本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语句应该可以实现这一点，但是我很难将“ big”表与产生的日子相结合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以“DD-
MM”的格式加入到ltg_data表text格式。说数据类型不匹配。我也尝试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”（忽略年份）分组，查询结果显示
所有天/小时，即使它们为零 。

稍后，我还将尝试查找几天和几小时内的平均值和百分位数，因此，如果您对此有任何建议，我将不知所措。但是我目前的问题集中在仅仅获得总计的完整结果上。

阅读 129

2021-07-01

共1个答案

admin

基本上， to_char(time, 'MMDD') 像您已经尝试过的那样，完成年度工作就可以了。您只是忘记了也
将它应用于加入generate_series() 之前生成的时间戳。以及其他一些小细节。

为了简化并提高性能和方便性，我建议使用此简单函数根据integer给定的模式“ MMDD”来计算timestamp。

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)-通过内部IMMUTABLE函数实现date_part(text, date)。（请注意，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) ...

格式化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，但是函数包装器仍然很方便。

2021-07-01