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
для каждого дня в году. Я также в конечном итоге хочу одно и то же для каждого часа каждого дня в году («ММ-ДД-ЧЧ»).
Это должно выполняться с помощью оператора 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
в формате «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.
Заключение. Я стремлюсь получать ежедневные и ежечасные общие подсчеты, охватывающие много лет, но сгруппированные по «ММ-ДД» и «ММ-ДД-ЧЧ» (без учета года), с отображением результатов запроса все дни / часы, даже если они равны нулю.
Позже я также попытаюсь найти средние значения и процентили по дням и часам, так что, если у вас есть какие-либо советы по этому поводу, я все слышу. Но моя текущая проблема сосредоточена на том, чтобы просто получить полный результат для итогов.
My desired result is to left join ...
. Нет. Сначала объясните желаемый результат простым английским языком. Не начинайте с техник, которые у вас есть в голове, чтобы этого добиться. Определите ваши временные рамки (какие строки из таблицы будут учитываться) и какие дни должны быть в результате. - person Erwin Brandstetter   schedule 16.05.2018