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

陈玉杰

Postgres版本9.4.18，PostGIS版本2.2。
这是我正在使用的表(不太可能重大改变表结构):
# U, R& W7 _3 G& v表 ltg_data    (1988年至2018年的跨度):
6 f  f% Z9 b  {- T3 b1 b0 w4 z$ V    Column   |           Type           | Modifiers ---------- -------------------------- -----------intensity | integer                  | not nulltime      | timestamp with time zone | not nulllon       | numeric(9，6)            | not nulllat       | numeric(8)6)                                               | not nullltg_geom  | geometry(Point,4269)     | Indexes:"ltg_data2_ltg_geom_idx" gist (ltg_geom)"ltg_data2_time_idx" btree ("time")ltg_data（?800M行)大小:
  {: w, w& Y: i7 z' h7 v4 Zltg=# select pg_relation_size('ltg_data');pg_relation_size ------------------ 149729288192表 counties    ：
$ A4 z; K8 v; D6 o$ u9 ]        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
' `( s% h5 S- v! \2 O6 U; ~+ A* ^。以及ltg_data一年中每天的表格行数。最后，我希望在一年中的每一天每小时使用相同的内容（’MM-DD-HH’）。
! D5 [; i4 R$ D3 j  b; O一条group by句子应该能够实现这一点，但我很难说 big表与生成的日子相结合generate_series()。
* @; J# J( {1 f$ }MM-DD  | total_count   ------- ------------12-22  |     -23  |       012-24  |       012-25  |       012-26  |   -27  |       012-28  |         5122-29  |       012-30  |       012-31  |      0我尝试过很多查询:
SELECT date_trunc('day',d),  count(a.lat) AS strikesFROM generate_series('2017-01-01','2018-12-31',interval '1 day') AS dLEFT 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_yearGROUP BY dORDER BY d ASC;但这不能忽视年份。date_trunc天还在考虑我猜的年份。
2017-12-27 00:00:00-08 |   -12-28 00:00:00-08 |   -12-29 00:00:00-08 |   -12-30 00:00:00-08 |   -12-31 00:00:00-08 |   -01-01 00:00:00-08 |   -01-02 00:00:00-08 |   -01-03 00:00:00-08 |      0在这个查询中，我试图从转换数据generate_series()到text以“DD-
MM加入格式ltg_data表text格式。说数据类型不匹配。我也试过。extract因为它可以提供 doy”和“
- D- A  q# }1 R2 t* s& Y# I  dhour”，它们可以工作，但是我似乎也无法匹配该查询中的数据类型。很难使“ generate_series实现双精度。
SELECT to_char(d,'MM-DD') AS DAY,  count(a.lat) AS strikesFROM(SELECT generate_series('2017-01-01','2018-12-31',interval '1 day') AS d) AS fLEFT JOIN(SELECT to_char(TIME,'MM-DD') AS day_of_year,     ltg_data.latFROM ltg_dataJOIN counties ON ST_contains(counties.the_geom,ltg_data.ltg_geom)WHERE cwa = 'MFR' ) AS a ON f = day_of_yearGROUP BY dORDER BY d ASC;结果：
& {7 I0 N0 W- `; u1 v6 o) m$ \5 RERROR:  operator does not exist: record = textLINE 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(忽略年份)分组，查询结果显示
8 i! @/ _/ S5 i+ u% W  o即使/小时，即使是零    。
8 ~0 S6 Q- I# G. j2 F8 }后来，我会试着在几天和几个小时内找到平均值和百分位数，所以如果你有任何建议，我会不知所措。但我目前的问题集中在总结果上。
& y6 L+ H4 p* m" H0 w- b$ w  G                                                               
- O# Y4 m4 S4 `5 R    解决方案: