Объединить серию генерации и подсчет в один запрос

Постгрес версии 9.4.18, ПостГИС версии 2.2.

Я удалил некоторые подробности о таблицах из этого вопроса, потому что сомневаюсь, что это необходимо для ответа на вопрос. Я могу добавить эти детали обратно, если это необходимо.

Желаемый результат: мне нужно общее количество для каждой недели года и часа дня (от 0100 до 5223). Я могу успешно сгенерировать серию от 0100 до 5223 (на самом деле до 5300), и я могу получить общее количество для каждой недели года и часа дня по отдельности, но я не могу объединить запросы чтобы по-прежнему отображались недели года/часы дня с нулевым округом. Я хочу объединить результат подсчета с generate_series (и в идеале разделить этот результат на 30), чтобы получить что-то вроде ниже.

MM-DD  | count_not_zero | count_not_zero_divided_by_30
-------+----------------+----------------------------
0100  |       10        |  33.3
0101  |       0         |  0
0102  |       0         |  0
...
0123  |       0         |  0
0200  |       3         |  10
0201  |       10        |  33.3
...
5223  |       20        |  66.6

Вот мои отдельные запросы, которые работают... которые я хочу объединить:

SELECT DISTINCT f_woyhh(d::timestamp) as woyhh 
FROM generate_series(timestamp '2018-01-01', timestamp '2018-12-31', interval '1 hour') d 
GROUP BY woyhh
ORDER by woyhh asc;

SELECT dt, count(*) FROM 
(SELECT f_woyhh((time)::timestamp at time zone 'utc' at time zone 'america/chicago') 
AS dt, 
EXTRACT(YEAR FROM time) AS ctYear, count(*) 
AS ct 
FROM counties c 
INNER JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom) 
WHERE countyname = 'Milwaukee' AND state = 'WI' AND EXTRACT(YEAR from time) > '1987' GROUP BY dt, EXTRACT(YEAR from time)) 
AS count group by dt;

Результат второго запроса выше (и пропускает нулевой счетчик dt, чего я не хочу):

dt  | count
-------+-------
0100  |    10      
0104  |    5       
0108  |    4

...

Вывод. Я пытаюсь объединить приведенные выше рабочие отдельные запросы в один запрос, который выдает результат в виде трех столбцов: woyhh, count и count, разделенный на 30. И я хочу включить woyhh что в округе ноль, так что у меня полный комплект woyhh.

Спасибо за любую помощь!!


postgresql subquery generate-series
person user1610717    schedule 14.03.2019    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Я нашел ответ. Я опубликую это завтра, но я хотел поставить это сегодня, чтобы никто без необходимости не работал над этим вопросом. Прошу прощения за форматирование.

WITH CTE_Dates AS (SELECT DISTINCT f_woyhh(d::timestamp) as dt 
FROM generate_series(timestamp '2018-01-01', timestamp '2018-12-31', interval '1 hour') d), 
CTE_WeeklyHourlyCounts AS (SELECT dt, count(*) as ct 
FROM (SELECT f_woyhh((time)::timestamp at time zone 'utc' at time zone 'america/chicago') as dt, 
EXTRACT(YEAR FROM time) as ctYear, count(*) as ct 
FROM counties c 
INNER JOIN ltg_data d on ST_contains(c.the_geom, d.ltg_geom) 
WHERE countyname = 'Milwaukee' AND state = 'WI' AND EXTRACT(YEAR from time) > '1987' 
GROUP BY dt,
EXTRACT(YEAR from time)) as count group by dt), 
CTE_FullSTats AS (SELECT CTE_Dates.dt as dt, CAST(CTE_WeeklyHourlyCounts.ct as decimal) as ct 
FROM CTE_Dates LEFT JOIN CTE_WeeklyHourlyCounts ON CTE_WeeklyHourlyCounts.dt = CTE_Dates.dt 
GROUP BY CTE_Dates.dt, CTE_WeeklyHourlyCounts.ct, CTE_WeeklyHourlyCounts.dt) SELECT dt, COALESCE(ct, 0) 
AS count, round(((COALESCE(ct,0) * 100) / 30),0) as percent FROM CTE_FullStats 
GROUP BY dt, ct ORDER BY dt;
person user1610717    schedule 14.03.2019